The database must answer who produced an event, from which evidence, under which code and configuration, and what changed afterward. Use append-only domain events plus rebuildable projections. SQLite is sufficient for this single-worker course project when transactions, constraints, backups, and integrity checks are handled deliberately.
Create tables for runs, artifacts, cases, reviews, paper_events, and schema_migrations. Every row has a stable ID, UTC creation time, schema version, and relevant hashes. paper_events includes portfolio ID, event type, idempotency key, payload JSON, previous-event hash, and event hash. Unique constraints protect run keys and idempotency keys.
Store payload JSON only after schema validation. Add relational columns for fields queried often, such as event type and case ID, while retaining the signed payload. Foreign keys should be enabled on every connection. A review references exact evidence-packet and policy hashes; a fill references the approved review and snapshot.
Do not expose update/delete operations in the application repository. Corrections append events. Database administrators can technically alter files, so protect detection with hashes, file permissions, backups, and audit reconciliation; “append-only” is an application guarantee, not magic immutability.
Use migrations with forward-only numbered scripts and a recorded checksum. Test a fresh database and upgrade from the prior schema. Back up before migration, run foreign_key_check and integrity_check, then replay projections.
Plan indexes from queries: portfolio plus sequence, case ID, run ID, event type/time, and unique idempotency key. Measure with realistic synthetic volume instead of adding indexes blindly.
Define canonical serialization for event hashing: fixed UTF-8, sorted object keys, normalized timestamps, explicit decimal strings, and no insignificant whitespace. Hash verification should work in a separate audit command, not only inside the writer. Export schema documentation showing which fields are identifiers, provenance links, controlled enums, public text, and fictional quantities.
Set database pragmas deliberately and verify them per connection. Busy timeouts, journal mode, synchronous level, and foreign-key enforcement have tradeoffs; record the chosen values and failure tests rather than copying a tuning snippet. One writer plus read-only report connections keeps this course architecture understandable.
🇵🇰 Pakistan Angle
A local SQLite artifact keeps the lab inexpensive and offline-capable. It stores public evidence and fictional events only—no CNIC, phone, bank, wallet, or learner account data. Encrypt and minimize any separate operational contact system.
Hands-On Exercise
Write schema v1, insert a complete synthetic case, and replay its portfolio. Attempt duplicate idempotency, missing foreign key, invalid event type, and direct application update. Create migration v2 adding an indexed error category, back up, migrate, and run integrity checks.
Completion Rubric
- Events link evidence, reviews, policies, runs, and prior hashes.
- Constraints reject duplicates and broken references.
- Corrections append; projections are rebuildable.
- Migrations, backups, integrity, and replay are tested.
- Schema contains no financial or identity secrets.
Sources
Key takeaway: An audit schema preserves the complete reasoning chain and treats every projection as disposable, rebuildable state.