# Database Operations Guide

## Supported server

Phase 4 targets Oracle MySQL 8.0.34 or newer, including MySQL 8.4 LTS, with InnoDB and enforced check constraints. MariaDB is intentionally rejected because syntax and behavioral compatibility are not treated as equivalent.

Every acquired application connection is initialized with UTC (`+00:00`) and strict SQL mode. Remote production databases require verified TLS. Local socket/loopback deployments may use the configured local policy.

## First installation

1. Create a dedicated empty database and least-privilege application/migration account.
2. Configure `.env` and run `npm run verify:environment`.
3. Confirm connectivity and capability with `npm run database:check`.
4. Review SQL offline with `npm run migrate:dry-run`.
5. Apply migrations with `npm run migrate`.
6. Confirm every version using `npm run migrate:status`.
7. Review defaults using `npm run seed:dry-run`.
8. Apply idempotent defaults with `npm run seed`.

Dry-run commands do not read `.env`, open a database connection, or print credentials. Add `--json` directly to the underlying `bin/*.js` command when machine-readable output is needed.

## Migration safety model

The runner:

- Acquires a MySQL advisory lock scoped to the database.
- Creates four internal history tables.
- Loads only contiguous `NNN_name.sql` files.
- Calculates SHA-256 checksums and rejects modified applied files.
- Splits and executes one statement at a time with multi-statements disabled at the driver.
- Records every successful statement for same-file resume support.
- Marks a migration applied only after all up statements succeed.
- Rejects embedded transaction/session-control SQL and foreign-key-check disabling.

MySQL DDL can commit implicitly, so a file-level transaction cannot promise ordinary DML rollback semantics. If a connection is lost exactly after the server accepts DDL but before statement history is recorded, stop and inspect `SHOW CREATE TABLE` plus backups before retrying. Do not edit the migration file or manually insert a success record.

## Rollback

`npm run rollback` rolls back only the latest applied migration. Before any production rollback:

1. Enable maintenance mode at the infrastructure/application layer.
2. Stop workers and schedulers.
3. Take and verify an encrypted database backup.
4. Run `npm run rollback:dry-run` and review the exact down migration.
5. Execute the rollback and rerun `npm run migrate:status`.

A successful rollback invalidates seed history because affected seed dependencies may have changed. Seed data is not silently deleted; after re-migration, `npm run seed` safely re-evaluates defaults without overwriting existing operator values.

## Phase 14 withdrawal migration

Migration `019_harden_withdrawal_workflow.sql` adds constraints, indexes, permissions, immutable-evidence triggers, method/field versions, request snapshots, and the `withdrawal_fees` system account. Before applying it to any non-empty installation:

1. Back up and test-restore the database.
2. Inspect existing terminal withdrawal rows for consistent `completed_at`, and approved/paid rows for `approved_at`.
3. Confirm no withdrawal has multiple payout rows; the new unique index intentionally rejects that state.
4. Apply the migration first in a production-shaped staging copy.
5. Verify the new triggers, constraints, permissions, and `withdrawal_fees` account before enabling traffic.

Do not weaken the constraints to accommodate inconsistent data. Preserve the original database, investigate each row, and use an explicit reviewed repair migration when needed. Rollback `019` deletes the new system account only when it has no ledger entries; once fee settlement exists, ordinary rollback is intentionally not a substitute for accounting reconciliation.

## Phase 15 content and configuration migration

Migration `020_harden_content_configuration.sql` adds optimistic versions and lifecycle constraints to themes, currency metadata, managed links, feature flags, banners, announcements, tutorials, and maintenance windows. It also enforces a single active theme, immutable setting/read evidence, archive-only content deletion policy, and permission-separated publishing.

Before applying it to a non-empty installation:

1. Back up and test-restore the database.
2. Confirm no more than one theme currently has `status = 'active'`.
3. Verify active/published content has a non-null `published_at` where required.
4. Validate existing feature-flag and announcement-rule JSON values are objects or null.
5. Apply the migration in a production-shaped staging copy and run the full API smoke tests.

Do not bypass rejected legacy rows. Repair invalid data with a reviewed preflight migration, then apply `020`. Rollback removes Phase 15 guards and is not appropriate after operators have relied on immutable setting or announcement-read evidence.

## Phase 16 notification delivery migration

Migration `021_harden_notification_delivery.sql` adds immutable bot-template revisions and delivery-attempt evidence, outbox foreign keys and payload hashes for reminder/broadcast deliveries, optimistic reminder and broadcast versions, audience/counter timestamps, lifecycle checks, and messaging permissions. It also backfills the current template rows into version history and adds the default draft `daily_reminder` template.

Before applying `021`, confirm that existing active reminders have `next_run_at`, broadcast approval fields match their lifecycle states, and JSON placeholder/target columns have the required array/object shape. Keep `reminders` and `broadcasts` flags disabled during migration. Do not roll back after relying on immutable template/delivery evidence without a reviewed retention and reconciliation plan.

## Phase 17 analytics and leaderboard migration

Migration `022_harden_analytics_and_leaderboards.sql` adds reversal-aware analytics projections, immutable rollup evidence, generation-keyed leaderboard snapshots, privacy-safe display constraints, supporting indexes, the public period setting, and the sensitive financial-report permission.

Before applying `022`, confirm legacy user statistics can satisfy the earning-breakdown equality check and that every legacy leaderboard score/rank is positive with an object-shaped display snapshot. Apply on a restored staging copy first. Keep the public leaderboard feature disabled until the previous closed UTC date has exactly one successful run and all configured metric/period snapshots have been inspected. Do not roll back after published snapshot/run evidence is used for reporting without a reviewed retention and reconciliation plan.

## Immutable file policy

After any migration or seed reaches a shared/staging/production database:

- Never modify, rename, reorder, or delete that file.
- Correct it with a new higher-numbered migration.
- Never bypass a checksum mismatch.
- Store database backups and the exact deployed source revision together.

## Privilege guidance

The runtime application account should receive only required DML permissions after deployment. Schema migration credentials may require `CREATE`, `ALTER`, `DROP`, `INDEX`, `REFERENCES`, and `CREATE VIEW`; keep them separate when the host permits. Neither account should have global privileges, user-management privileges, or access to unrelated databases.

## Backup and recovery boundary

Phase 4 provides schema and migration tooling, not a backup engine. Production needs automated encrypted backups, off-host retention, restore drills, and monitoring. A migration is not considered safe merely because the up command completed; verify tables, constraints, migration status, and application smoke tests before returning traffic.