# Database

MySQL 8.0.16+ / MariaDB 10.6+ · InnoDB · `utf8mb4_unicode_ci` · 30 tables
(29 migrations + `schema_migrations`).

## Conventions

- **Time:** every `DATETIME` column holds **UTC**. Every connection runs
  `SET time_zone = '+00:00'`, so `CURRENT_TIMESTAMP` defaults are UTC too.
  Display in Africa/Nairobi happens only in `App\Core\Clock` and the
  `format_date()` helper. Never compare against server-local time.
- **Money:** `DECIMAL(12,2)`, currency `CHAR(3)` restricted to `KES`.
- **Strict SQL mode** on every connection (`STRICT_ALL_TABLES`, no zero dates).
- **No hard deletes** of business records. Awards, categories, nominees and
  panelists are archived; payments and votes are never deleted (refunds
  set `REFUNDED` / `REVERSED`).
- **Composite foreign keys** pin children to the right parent chain:
  `categories` has `UNIQUE (id, award_id)` and `nominees` has
  `UNIQUE (id, category_id, award_id)`, so a nomination, nominee, payment,
  vote or adjustment physically cannot reference a category from another
  award, or a nominee from another category.

## Rules enforced by the database itself

These hold even if application code has a bug:

| Rule | Constraint |
|---|---|
| A category needs ≥ 20 shortlisted nominees to open voting; `min_shortlist` can never be below 20 | `chk_categories_min_shortlist` |
| Only shortlisted nominees can be published | `chk_nominees_publish_shortlisted` |
| A nominee needing consent (third-party nomination) can't be published until consent is confirmed | `chk_nominees_publish_consent` |
| An admin-added nominee must have a recorded reason | `chk_nominees_admin_reason` |
| An accepted nomination must be linked to a nominee in the same category | `chk_nominations_accepted_linked` + `fk_nominations_nominee` |
| Nominations/payments can't be double-submitted | `UNIQUE (nominations.submit_token)`, `UNIQUE (payments.submit_token)` |
| Payment amount = vote quantity × unit price, always | `chk_payments_amount` |
| A payment is SUCCESS/REFUNDED only with an IntaSend invoice and paid + verified timestamps | `chk_payments_success_verified` |
| One payment funds at most one vote row | `UNIQUE (votes.payment_id)` |
| A vote's nominee, voter, quantity and amount equal its payment's | `fk_votes_payment_match` → `payments (id, nominee_id, voter_id, vote_quantity, amount)` |
| Reversed votes carry a reversal time; confirmed ones don't | `chk_votes_reversal` |
| Adjustments add up (`after = before + delta`, `delta ≠ 0`) | `chk_vote_adjustments_*` |
| Voter phones are normalised Kenyan numbers | `chk_voters_phone_format` (`^254[17][0-9]{8}$`) |
| A webhook event is stored once | `UNIQUE (webhook_events.event_key)` |

Not expressible as a constraint, and therefore enforced in services
(Phases 5–8) and checked by reconciliation: a vote only exists for a
`SUCCESS` payment; a category's shortlist count stays ≥ its minimum while
voting is open.

## Tables

| Group | Tables |
|---|---|
| Access | `roles`, `permissions`, `role_permissions`, `admins`, `login_attempts`, `rate_limits` |
| Content & branding | `settings`, `media` |
| Awards | `awards`, `award_vote_packages`, `categories`, `nominees` |
| Nominations | `nominations`, `nomination_reviews`, `shortlist_decisions` |
| Panel | `panelists`, `award_panelists` |
| Voting & money | `voters`, `payments`, `payment_status_history`, `votes`, `payment_reversals`, `vote_adjustments`, `nominee_vote_totals` |
| System | `webhook_events`, `audit_logs`, `contact_messages`, `notification_outbox`, `cron_runs`, `schema_migrations` |

Each table's purpose and constraints are documented in a comment at the top
of its migration in `database/migrations/`.

### Changes from the Phase 1 plan

- `nominees.consent_required` added so the consent rule can be a CHECK
  constraint (approved decision 5).
- `payments.phone_masked` / `voters.phone_masked` added so personal data can
  be anonymised later while keeping financial records.
- `votes.reversal_id` dropped: `payment_reversals.vote_id` (unique) holds
  the link, which avoids a circular foreign key.
- `media.purpose` gained `sponsor` for the optional Sponsors & Partners strip.
- `awards.is_featured` and `awards.event_venue` added for the home hero and
  schema.org Event data.
- `webhook_events.next_attempt_at` and `notification_outbox.available_at` /
  `last_error` added for cron retry scheduling.

## Migrations

Forward-only numbered SQL files: `database/migrations/NNN_description.sql`.

```bash
php bin/migrate.php            # apply pending migrations in order
php bin/migrate.php --status   # APPLIED / PENDING / MODIFIED / MISSING-FILE
php bin/migrate.php --dump     # regenerate database/install.sql (phpMyAdmin import)
```

- Each migration's SHA-256 checksum is stored in `schema_migrations`. If an
  applied file is edited, the runner stops with an error. Never edit an
  applied migration; add a new one.
- Runs stop at the first failing statement. The failing version is not
  recorded, and the error names the migration and the statement number.
- MySQL commits DDL implicitly, so `CREATE TABLE` can't be rolled back. Each
  migration therefore creates exactly one table. Data-only migrations run
  inside a transaction.
- A database lock (`GET_LOCK`) prevents two runs at the same time.
- After adding a migration, run `--dump` and commit the new
  `database/install.sql`. A test fails if the file is stale.

## Seeds

```bash
php bin/seed.php               # roles, permissions, grants, default settings (production-safe)
php bin/seed.php --demo        # DEV ONLY demo award/categories/nominees/panelists
php bin/seed.php --remove-demo # remove the demo data
```

- The base seed is idempotent. Roles and permissions come from
  `config/permissions.php`, and grants are synchronised to match it. Settings
  are inserted only when missing, so administrator changes survive.
- No administrator account is ever seeded. The first admin is created in
  Phase 3 (`bin/create-admin.php`).
- Demo data (`database/seeders/DemoSeeder.php`) is refused when
  `APP_ENV=production`. It uses `demo-` slugs and "Demo" names, and creates no
  payments, votes, nominations or shortlist decisions. Its demo nominees are
  inserted directly as shortlisted/published only so the templates can be
  previewed.

## Personal data & retention

Voter name, phone and email, nominator details and IP addresses are personal
data (Kenya Data Protection Act, 2019). v1 does not delete anything
automatically. The retention period is a setting
(`retention.voter_pii_months`), and a manual anonymisation command arrives
with the admin tooling. It will clear `voters.full_name`, `email` and
`phone_e164` (keeping `phone_masked`) and the equivalent payment fields,
while keeping amounts, references and vote records for accounting and audit.