Module 08 · Database and Monitoring — Audit Logging and Model Evaluation
Database Design — Append-Only Paper Audit Schema
Open lesson + course map
On this lesson
Course outline
Module 1 · Market Systems and Safety — Pehle Boundaries Samjho
Module 2 · Python Bot Architecture — Ek Professional Bot Ka Skeleton
Module 3 · Market Data Pipeline — Read-Only Evidence Safely Fetch Karo
Module 4 · AI Research Engine — Extraction Se Human Review Tak
Module 5 · Strategy Research — Hypothesis Se Paper Test Tak
Module 6 · Paper Execution Engine — Synthetic Fills Only
Module 7 · Risk Controls — Estimation Error and Paper Limits
Module 8 · Database and Monitoring — Audit Logging and Model Evaluation
Module 9 · Deploying the Research Service — Read-Only and Measured
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
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
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
Completion Rubric
5 checks — tick as you verify
// sources
Sources
3 official sources — check every claim yourself