---
name: forum-migration
description: Write a schema change for the GCC Forum — the USE-your-database rule, additive-only constraint on legacy tables, idempotency, index choices and the FULLTEXT lock. Use when adding a forum_* table, an index, or a section seed row.
---

# Writing a forum migration

Forum schema lives in **`app/Admin/sql/`** with every other migration.

## The rule that comes first

**Start every `.sql` file with a `USE`** — after the header comment, before the
first statement — or fully qualify every object it touches. `USE` is preferred:
one line at the top instead of a prefix on every statement, and it cannot be
half-applied.

```sql
-- ---------------------------------------------------------------------------
-- gcc.forum_* — what this does, and why.
-- Target: gcc (the GAME database), NOT gcc_admin.
--
-- Safe to re-run: every statement is IF NOT EXISTS / ON DUPLICATE KEY UPDATE.
--
-- Apply with:
--   mysql -u WolfrenInd -p < app/Admin/sql/<file>.sql
-- ---------------------------------------------------------------------------

USE `gcc`;

CREATE TABLE IF NOT EXISTS `forum_thing` ( … );
```

That folder holds migrations for `gcc_admin`, `gcc` **and** `gcc_log`, and its
name biases you toward `gcc_admin`. **All forum tables are in `gcc`.** A
migration run against the wrong database is a silent no-op, or creates a table
somewhere nothing will ever read from.

With the `USE` present the file no longer depends on the invoker remembering:

```bash
mysql -u WolfrenInd -p < app/Admin/sql/forum_schema.sql   # no DB argument
```

The one exception to placement is `gcc_admin.sql`, where
`CREATE DATABASE IF NOT EXISTS` necessarily precedes its `USE`.

## Checklist

1. Header comment: what, why, target database, apply command.
2. `USE \`gcc\`;`
3. Statements — all idempotent.
4. Apply locally, **twice**, and confirm with `information_schema`.
5. Update the file table in
   [`../../../Admin/docs/DEVELOPMENT.md`](../../../Admin/docs/DEVELOPMENT.md).
6. Note anything that needs an app restart or a quiet window.

## Additive only

`he`, `hef`, `he2`, `hef2`, their `_old` twins, `he_s`, `hef_s` and `he_type`
hold twenty years of posts.

- **Never** `DROP`, and never `ALTER` a legacy column's type or width.
- **Never** `DELETE` a legacy row.
- New indexes on legacy tables are fine — they are additive.
- If a change seems to need a column on `hef`, it belongs in a side table keyed
  by `(src, thread_id)`. That is what every `forum_*` table is.

The one legacy column the board writes is `hef.type` (moving a thread) and the
last-post columns on reply — both of which the legacy board writes too.

## Idempotency

```sql
CREATE TABLE IF NOT EXISTS …
ALTER TABLE `x` ADD INDEX IF NOT EXISTS `idx_y` (…);   -- MariaDB supports this
ALTER TABLE `x` MODIFY `col` varchar(16) DEFAULT NULL; -- MODIFY is naturally idempotent
INSERT INTO … ON DUPLICATE KEY UPDATE …
```

Verify rather than trusting a success return:

```sql
SELECT COLUMN_TYPE FROM information_schema.columns
WHERE table_schema='gcc' AND table_name='forum_section' AND column_name='glyph';

SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
FROM information_schema.statistics
WHERE table_schema='gcc' AND table_name='hef' GROUP BY INDEX_NAME;
```

## Index choices that mattered

| Index | Why it exists |
|---|---|
| `hef(type, lastpost)`, `hef(type, id)` | section listings; without them every page is a filesort over the whole section |
| `hef2(belongto, id)` | the correlated reply-count subquery — 28ms → 1ms vs a derived table |
| `hef(userid, datetime, usernic)` | **covering** for the member aggregate — 216ms → 58ms |
| `he(userid, id)` | My Tickets, which scans a player's own threads across all sections |
| `ft_hef_body`, `ft_hef2_nlong` | FULLTEXT search — 274ms → 39ms |

A covering index earns its size when the query selects only indexed columns.
The member aggregate needs `usernic` and `datetime` alongside `userid`; without
them in the index it is a table scan.

## FULLTEXT locks the table

Roughly **5s on `hef2`** (122k rows), **1s on `hef`**. One-off, but it blocks
writes while it runs — apply during a quiet window, and say so in the header
comment.

Search uses FULLTEXT only for terms of 3+ characters, because InnoDB will not
index a token shorter than `innodb_ft_min_token_size` (default 3). Below that
`Board.search()` falls back to a title-only `LIKE`.

## Standalone vs full-schema

`forum_schema.sql` is the whole thing and is idempotent, so re-running it is
usually fine. But when an existing install needs **one** change, ship it
separately as well — someone with a populated test server should not have to run
the whole schema to widen a column.

`forum_glyph_widen.sql` is the pattern: a header explaining the failure it
fixes, the `USE`, and the single statement. `forum_schema.sql` carries the same
statement so a fresh install gets it automatically.

## Section seed rows

Adding a section is a migration — see
[`forum-section`](../forum-section/SKILL.md) for the two rows and the
access implications. Remember `he_type` is cached in application scope by
`s_loadsystem.cfm`: **restart the app** or the new section will not appear.