# Database schema

## Conventions

- **InnoDB** for every table
- **`utf8mb4` / `utf8mb4_unicode_ci`** everywhere; anything else corrupts Arabic silently
- **Foreign keys** with `ON DELETE CASCADE` for owned rows and `ON DELETE SET NULL` for references
- **Composite primary keys** on join tables such as follows, so a duplicate is impossible by construction
- **Every parameter prepared.** No string-built SQL, ever

## Table groups

**Identity** — `users`, `email_tokens`, `api_tokens`, `user_preferences`, `rate_limits`

**Content** — `questions`, `answers`, `comments`, `topics`, `votes`

**Taxonomy** — `categories`, `tags`, `question_tags`, `tag_synonyms`

**Following** — `question_follows`, `topic_follows`, `category_follows`, `tag_follows`

**Moderation** — `reports`, `moderation_actions`, `audit_log`

**Messaging** — `notifications`, `notification_preferences`, `contact_messages`

**Presentation** — `layouts`, `layout_sections`, `widgets`, `widget_translations`, `menu_items`, `theme_profiles`, `theme_profile_versions`, `custom_code_snippets`, `ad_creatives`

**Platform** — `settings`, `languages`, `translation_overrides`, `schema_migrations`

The current list is in [Database tables](../reference/DATABASE-TABLES.md).

## Denormalised counters

`questions.answers_count`, `questions.comments_count`, `topics.comments_count` and `topics.last_activity_at` are maintained in the same transaction as the row that changes them.

A comment on an **answer** increments the counter on that answer's **parent question**. Getting this wrong corrupts an unrelated question whose id happens to match the answer id; it now has a regression test.

## Migrations

Forward-only SQL files in `database/migrations`, applied in lexicographic order, tracked in `schema_migrations`.

Rules:

1. Never edit a migration that has run anywhere. Add a new one.
2. Prefer `IF NOT EXISTS`; running the set twice must not fail.
3. No subquery on the table being modified — MySQL rejects it.
4. A migration must work on a fresh database **and** on an upgraded one.

Run them with the admin **Upgrade database** action or `php bin/migrate.php`.

## Account classification columns

`users.account_type` (`member` / `editorial` / `system`), `users.trust_level`, `users.login_disabled`, `users.created_by_admin_id`.

These make house content transparent at the schema level rather than by convention. `login_disabled` is enforced during authentication.

## Indexing

Indexes exist for the listing and lookup paths actually used: status plus publication date for feeds, slugs for lookups, foreign keys for joins, and activity timestamps for ordering.

Add an index when a measurement shows it is needed, not when a query looks slow to the naked eye.
