# Database and data handling

## Engine and units

Target MySQL 8+/compatible MariaDB using Laravel PDO. PHPUnit config points to SQLite `:memory:` for fast tests; SQLite execution must be confirmed with the actual PHP SQLite extension. Numeric quantity is fixed decimal (up to two places). Fields named `*_minor` currently hold whole Tanzanian shillings (TZS, zero-decimal currency convention in this application) as signed-range-checked integers. `MinorUnitMath` multiplies by hundredths of quantity and rounds half-up without floating-point arithmetic. Verify business/accounting convention with Tanzanian finance/legal reviewers before deployment.

## Migrations

Migration set defines users/roles, consent/assisted registration, farmer/buyer profiles, crops/listings/offers/revisions/orders/history, notifications/outbox/delivery events, audit trail, price observations, reports, payment records, devices, private business documents, verification/trust assessments, listing media, conversations/messages, agreements/deliveries/inspections, disputes/evidence, payment events, preferences/templates, searches/reviews/transport/subscriptions/invoices, password resets/Sanctum tokens/queues, account deletion, listing quality details, and idempotency/retry columns. `php artisan migrate --seed` seeds only roles, ginger crop, and language templates; no real persons, sales, payments, endorsements, market prices or verification decisions are seeded. Device push tokens are encrypted at rest and indexed by a SHA-256 digest rather than a MySQL-incompatible unique index on a `TEXT` column. Migration `000019` upgrades databases from the earlier draft schema; set a stable `APP_KEY` before migrating existing device rows, because changing that key later prevents token decryption until devices re-register and invalidates decryption of stored TOTP secrets and encrypted OTP delivery payloads. Establish protected key backup and a tested key-rotation/re-enrollment procedure before launch.

## Sensitive data and deletion

Private evidence paths are under Laravel local private disk and never serialized by listing endpoints. Downloads require participant/officer permissions except approved images attached to active public listings. Store secure backups as sensitive data. Raw service-account JSON stays outside public roots. Never log token, secret, OTP, private evidence content or full provider payload.

Deletion requests are recorded for human review; approval suspends the account but does not erase it. `users:erase {request_id} --dry-run` checks unresolved orders, negotiations, disputes, payment reviews, reports and verification requests. The interactive execution removes private business documents and listing photos, deletes tokens/devices/challenges, redacts all conversation text and free-form applicant/report text, anonymizes the profile, and preserves transaction/audit references. Payment/dispute evidence, market observations, audit data and backups may remain; this is not a complete legal-hold or backup-purge system. Qualified Tanzanian counsel and the data controller must define the lawful basis, holds, precise retention periods, evidence treatment, export process, backup expiry, and proof of erasure before live data is accepted. No retention durations are invented here.

## Migration/restore check

After provisioning database and private storage, run migrations on a clean staging database, seed, execute tests, create a backup, restore it to a separate database/storage location, compare counts and validate private-file access. Never test restore over production. Record operator, timestamp, application commit and checksum.