# Data model

What the forum reads, what it adds, and why the split falls where it does.

Schema: **`app/Admin/sql/forum_schema.sql`** (full, idempotent),
**`app/Admin/sql/forum_avatars.sql`** (avatar columns on `forum_profile`), and
**`app/Admin/sql/forum_glyph_widen.sql`** (a standalone `MODIFY` for installs
predating the `glyph varchar(16)` widen).

---

## The legacy tables — read in place, never migrated

There is no import and no mirror. Every query hits these directly, so what the
forum shows is what the database holds and `hef.cfm` keeps working against the
same rows.

| Table | Rows (live board) | What it is |
|---|---|---|
| `hef` / `hef_old` | 9,341 / 24 | Forum threads |
| `hef2` / `hef2_old` | 122,038 / 37,208 | Forum replies |
| `he` / `he_old` | 935 / 14,717 | Help Center tickets |
| `he2` / `he2_old` | 2,272 / 20,056 | Help Center replies |
| `hef_s` / `he_s` | 20 / 3 | Sticky — a row's **presence** means pinned |
| `he_type` | 21 | Sections, for both boards. **Source of truth for access.** |

A thread is a row in `hef`; a reply is a row in `hef2` pointing back through
`belongto`. Both denormalise the author's name into `usernic` — which turns out
to matter enormously (see *Identity* below).

### Constraints that come with them

- **`nshort` is `varchar(30)`.** Thread titles are capped at 30 characters
  because the column has been that width since 2006. Widening it would rewrite
  a column every legacy page still reads.
- **Bodies are HTML in the 2006 storage form** — entity-escaped quotes, a
  backtick for the apostrophe, `<br>` for newlines. New posts keep that form so
  `hef.cfm` renders them correctly and nothing is stranded on a rollback.
- **`_old` is a move, not a delete.** Closing a thread copies it to `_old` and
  removes the live row.
- **`type` is the section.** Moving a thread is a single `UPDATE hef.type`.

### The dedup that has to stay

"Tag as answered" (`f_he_detail.cfm`, `url.co=101`) copies a thread into `_old`
**without** deleting the live row. So **2,400 `hef2` rows and 73 `he` rows exist
in both tables.** Every live+archive union filters the archive branch with
`NOT EXISTS`; remove it and threads list twice.

---

## The `forum_*` tables — additive only

Everything the new board needs that the legacy schema had nowhere to put. All in
`gcc`. Keyed on `(src, thread_id)` where `src` is `'hef'` or `'he'`, so one set
of tables serves both boards.

| Table | Holds | Losing it costs |
|---|---|---|
| `forum_category` | Board-index groupings (`he_type` has no notion of one) | the index layout |
| `forum_section` | Placement + presentation per `he_type`, per board. **Not permissions.** | sections vanish from the index |
| `forum_prefix` | Thread prefixes | prefixes |
| `forum_thread_meta` | Views, prefix, pin, lock, staff-only replies, moved-from | view counts, new-style pins, the "no player replies" flag |
| `forum_read` | Per-user read markers (`last_seen_reply`) | unread highlighting |
| `forum_subscribe` | Thread following | notifications |
| `forum_react` | Reactions (schema only, no UI yet) | nothing yet |
| `forum_report` | Player reports → moderation queue | the queue |
| `forum_modlog` | Append-only staff action record | the audit trail |
| `forum_profile` | Forum title, signature, **avatar** | signatures and avatars |
| `forum_notify` | On-site notification feed | the feed |
| `forum_acl` | Clearance levels **moved from their default** (`acl_key`, `min_level`). Empty = every gate where the code puts it. | levels revert to their built-in defaults |

**None of it holds a post.** Drop every `forum_*` table and the board still has
every thread and reply it ever had — it just forgets who read what.

### `forum_section` is presentation, not permission

The PK is `(src, type_id)`, not `type_id` alone, because the two boards share
the `he_type` id space but not its meaning: **type 1 is "Question" in the Help
Center and the pre-category catch-all in the Forum.** Different sections,
different rows.

`access` and `publicflag` are **not** stored here. They stay on `he_type` so
there is exactly one source of truth, shared with the legacy board. `listed = 0`
hides a section from the index while leaving its threads readable by direct
link; `postable = 0` makes it read-only.

### Pinning writes both places

`forum_thread_meta.pinned_at` **and** the legacy `hef_s`. A thread counts as
pinned if either says so, so a pin made in the old board is honoured here and
vice versa.

---

## Identity: posts outlive accounts

**1,897 of the 2,234 distinct Forum authors have no `user` row.** Accounts were
purged over twenty years; `hef.usernic` is the only surviving record of who
wrote a post.

A member directory built from `user` would show a twenty-year board with 337
members and attribute 85% of its history to nobody. So the directory is built
from **authorship** — group `hef`/`hef2` by `userid`, take the name from the
post, and `LEFT JOIN user` only for the extras a live account can supply.

This is why `forum_profile` is keyed on `userid` and is **optional**, rather
than being columns on `user`. A departed author keeps a profile, a post count
and a working name link; the page says the account is closed instead of
inventing a rank and a fed for somebody who left in 2011.

---

## Sections

`he_type` is flat and ordered by `orderby`. `forum_section` groups it.

**Forum (`hef`)** — 8 sections in 5 categories:

| Category | Sections |
|---|---|
| The Galaxy | 100 General, 101 Strategy, 103 Federation, **106 Events** — staff start the threads (`post.events`, default 3); everyone reads and, unless the thread says otherwise, replies |
| Trade & Craft | 102 Wanted |
| Development | 105 GC Development (`access 1`) |
| The Vault | **1 The Archive** — `postable = 0` |
| Staff Room | 104 Guide & Admin (`access 1`) |

**Help Center (`he`)** — 15 sections in 6 categories:

| Category | Sections |
|---|---|
| Get Help | 1 Questions, 12 Bugs, 2 Suggestions |
| Account & Billing | 3 Email Validation, 10 Payment, 11 Business |
| Report & Appeal | 90 Cheating, **91 Re-Activate/Silencing**, 92 Complain about Staff |
| Announcements | 200 Updates, 201 News — `access 5` is **who may post** |
| Join the Team | 20 Volunteer, 21 Staff, 22 Bounty |
| From the Admins | 95 System Messages |

### The Vault

`hef` type 1 holds **1,341 threads** posted 2006–2023, **1,236 still open**.
`f_he.cfm:139` lists the Forum with `type>=100 and type<=199`, so nothing in the
old UI could reach them — they were unlisted, not deleted, and direct links
still resolved. Giving them a `forum_section` row is what put them back on the
board.

**If you add a section, add its `forum_section` row** or its threads become
invisible exactly the same way.

---

## Indexes

Added by the migration, all additive:

| Index | Why |
|---|---|
| `hef(type, lastpost)`, `hef(type, id)` | section listings; without them every page is a filesort over the section |
| `hef2(belongto, id)` | the correlated reply-count subquery |
| `hef(userid, datetime, usernic)`, `hef2(…)` | **covering** — the member aggregate, 216ms → 58ms |
| `he(userid, id)`, `he(type, id)` | My Tickets and the Help Center listings |
| `hef(lastpost)`, `he(lastpost)` | the Latest Activity rail — `ORDER BY lastpost` across an IN list of sections, which `(type, lastpost)` cannot serve; without it every forum page read and sorted the whole thread table |
| `ft_hef_body(nshort, nlong)`, `ft_hef2_nlong(nlong)` | FULLTEXT search, 274ms → 39ms |
| `ft_he_body`, `ft_he2_nlong` | the same for the Help Center |

**The FULLTEXT build locks the table** — roughly 5s on `hef2` (122k rows), 1s on
`hef`. One-off, but apply during a quiet window.

Search uses FULLTEXT for terms of 3+ characters and falls back to a title-only
`LIKE` below that, because InnoDB will not index a token shorter than
`innodb_ft_min_token_size` (default 3). On this data the two find within 0.15%
of the same posts (10,587 vs 10,603 for "fleet"); the gap is mid-word
substrings, which is not how people search a forum.

---

## Avatars

Uploaded avatars follow the **federation flag** system exactly
(`s_fed_flag_util.cfm` / `s_fed_flag_save.cfm`), because that code already
solved this problem on this server.

**Storage:** `app/i/avatar/a<userid>_<ver>.png`, alongside `app/i/fed/`. The
directory carries the same `.htaccess` hardening, and `app/i/avatar/*.png` is
gitignored — uploads are user content, not source.

**Columns**, added to `forum_profile` by `forum_avatars.sql`:

| Column | Means |
|---|---|
| `avatar_ver` | `0` = none. Otherwise both the "has one" flag **and** the cache-buster in the filename. |
| `avatar_by` / `avatar_at` | who uploaded it, and when. Kept after a takedown so the trail still names the uploader. |
| `avatar_removed_by` / `avatar_removed_at` | the takedown record. "Never had one" and "had one removed" are different situations. |
| `avatar_locked` | blocks re-upload after a takedown. Without it, removing an offensive avatar is a speed bump. |

`avatar_seed` (the deterministic colour slot) stays as the fallback for anyone
without an upload.

### The three properties worth preserving

1. **Nothing the user uploaded is ever served.** The file is decoded, drawn onto
   a fresh `BufferedImage`, and re-encoded as a new PNG; the original is
   deleted. A polyglot file, EXIF payload or script tag glued to a GIF header
   does not survive being redrawn as pixels. *Verified:* a PNG with
   `<cfoutput>PWNED</cfoutput><?php … ?>` appended comes back out with the
   payload gone.
2. **Versioned filenames.** `avatar_ver` increments on every upload, so a
   replaced avatar cannot serve from a stale cache and deleting the old file is
   precise rather than a guess. The version is read **inside** the write path,
   not from the request cache, so two tabs cannot both write version 3.
3. **Header-only dimension check before the decode.** A 200-byte PNG claiming
   60000×60000 is rejected on its header, before anything tries to allocate
   14 GB of pixels for it.

Plus a 6-uploads-per-hour cap per user in application scope, and the board's
CSRF token on both upload and removal.

### Where it differs from a fed flag

A flag is 160×80 and **letterboxed** — its aspect ratio carries meaning. An
avatar is a square that must **fill** its box; letterboxing would leave
transparent bars inside a 22px square. So avatars are **centre-cropped to
square, then scaled to 192×192** — twice the largest place they render
(`.av.xl` is 92px) so they stay sharp on a retina display, and no larger.

### Moderation

Held at **help level 4**, not the board's usual mod threshold of 2. An avatar
appears on every post its owner has ever made, so a takedown reaches further
than locking a thread and is deliberately one rung higher.

`?p=avatars` lists what is currently published, newest first, with a **Locked**
tab. Removal deletes the file permanently — there is no undo — optionally sets
`avatar_locked`, and writes `avatar_remove` to `forum_modlog` with the
moderator's name. A member clearing their own picture is *not* logged: it is not
a moderation event.