# ABC Core Database Guidelines (Phase 1.1)

## Database Choice

- MySQL 5.7+ or MariaDB 10.3+.
- InnoDB engine for all tables (required for transactions and foreign keys).
- UTF-8 character set: `utf8mb4` with `utf8mb4_unicode_ci` collation.

## Connection

- PDO with `ATTR_ERRMODE => ERRMODE_EXCEPTION`.
- `ATTR_EMULATE_PREPARES => false` (native prepared statements).
- `ATTR_DEFAULT_FETCH_MODE => FETCH_ASSOC`.
- Persistent connections enabled (`ATTR_PERSISTENT => true`).

## Table Design

- Every table must have a primary key (auto-increment `id` OR UUID).
- Use `created_at` and `updated_at` timestamps on every table.
- Use `deleted_at` for soft deletes where required.
- Foreign keys must be defined with `ON DELETE RESTRICT` or `ON DELETE CASCADE` explicitly.
- No table names may be reserved words.
- Table names: lowercase, plural, snake_case (e.g., `ledger_entries`, `audit_logs`).
- Column names: lowercase, snake_case.

## Ledger-First Schema

Financial tables must follow the ledger-first principle:

1. **ledger_entries** — Immutable record of every financial movement.
2. **transactions** — Business-level transaction metadata (UUID public, id internal).
3. **accounts** — Current balance (derived from ledger, not directly updated).
4. **audit_logs** — Who did what, when, and from where.
5. **receipts** — Immutable receipt records for every transaction.
6. **notifications** — Notification queue and history.

### Account Balances

- Never run `UPDATE accounts SET balance = balance - 100`.
- Always insert a `ledger_entry` first.
- The account balance is calculated from the ledger or updated by the Ledger Core after ledger insertion.
- Balances are stored for performance but are always reconcilable against the ledger.

## UUID Fields

- Every table that exposes data via API must have a `uuid` column (CHAR 36, unique, indexed).
- The `uuid` is the public identifier. The `id` is internal and never exposed.
- Foreign keys between tables may use `id` for performance, but API responses always use `uuid`.

## Indexing

- Index all foreign key columns.
- Index columns used in `WHERE`, `JOIN`, and `ORDER BY`.
- Index `uuid` columns (unique).
- Index `status`, `type`, `currency`, and `created_at` on high-volume tables.
- Add composite indexes for common query patterns.
- Avoid over-indexing (costs write performance).

## Migrations

- Stored in `database/migrations/`.
- Named with timestamp prefix: `YYYY_MM_DD_HHMMSS_description.php`.
- Must be reversible (`up` and `down` methods).
- Must run inside a transaction where possible.
- Must never drop data without explicit warning.
- Must use Constants for enum values where applicable.

## Seeders

- Stored in `database/seeders/`.
- Must be idempotent (safe to run multiple times).
- Must use transactions.
- Must not contain real customer data or production credentials.

## Schema Reference

- `database/schema/schema.sql` — Generated canonical schema (do not edit manually).
- `database/schema/migrations.md` — Migration history and notes.

## Query Rules

- All SQL only in `Repository` classes.
- Prepared statements for all dynamic values.
- No `SELECT *` in production queries; list columns explicitly.
- Limit result sets by default; never return unbounded lists.
- Use `EXPLAIN` to verify query plans for new indexes.

## Security

- Separate database user per environment (dev, staging, production).
- Production DB user has no `DROP` or `GRANT` privileges.
- Encrypt sensitive columns at application level if needed (PIN, account numbers).
- No plain-text passwords or secrets in DB.
- Hash all tokens and credentials.
- Salt all hashes.

## Audit

Every table that tracks state changes should have a companion audit mechanism:
- `created_by` and `updated_by` columns where applicable.
- Or a separate `audit_logs` table with JSON diff snapshots.
- IP address and user agent captured on all state changes.
- UUIDs used for audit references, never internal IDs in public audit trails.