Review, Test, Document, and Present the Database

import sqlite3from pathlib import PathROOT = Path(__file__).resolve().parents[1]SCHEMA = (ROOT / 'db/001_schema.sql').read_text()SEED = (ROOT / 'db/002_seed.sql').read_text()def build_memory_database():    db = sqlite3.connect(':memory:')    db.execute('PRAGMA foreign_keys = ON')    db.executescript(SCHEMA)    db.executescript(SEED)    return dbdef test_structure_and_integrity():    with build_memory_database() as db:        assert db.execute('PRAGMA integrity_check').fetchone()[0] == 'ok'        assert db.execute('PRAGMA foreign_key_check').fetchall() == []        tables = {row[0] for row in db.execute(            "SELECT name FROM sqlite_schema WHERE type='table'"        )}        assert {'customer','product','inventory','sales_order','order_item',                'payment','shipment','audit_event'} <= tablesdef test_business_reconciliation():    with build_memory_database() as db:        order_1001 = db.execute(            'SELECT SUM(line_total_cents) FROM order_item WHERE order_id=1001'        ).fetchone()[0]        captured_1001 = db.execute(            "SELECT SUM(amount_cents) FROM payment WHERE order_id=1001 AND status='captured'"        ).fetchone()[0]        assert order_1001 == captured_1001 == 7800        non_cancelled = db.execute(            '''SELECT SUM(oi.line_total_cents)               FROM order_item oi JOIN sales_order so USING(order_id)               WHERE so.status <> 'cancelled' '''        ).fetchone()[0]        by_product = db.execute(            '''SELECT SUM(gross_cents) FROM (                 SELECT p.product_id,                        SUM(CASE WHEN so.status <> 'cancelled'                                 THEN oi.line_total_cents ELSE 0 END) gross_cents                 FROM product p                 LEFT JOIN order_item oi ON oi.product_id=p.product_id                 LEFT JOIN sales_order so ON so.order_id=oi.order_id                 GROUP BY p.product_id               )'''        ).fetchone()[0]        assert non_cancelled == by_productdef test_constraints_reject_invalid_state():    with build_memory_database() as db:        try:            db.execute(                'UPDATE inventory SET reserved=on_hand+1 WHERE warehouse_id=1 AND product_id=10'            )        except sqlite3.IntegrityError:            pass        else:            raise AssertionError('inventory invariant was not enforced')def test_expected_plan_after_indexes():    with build_memory_database() as db:        db.executescript((ROOT / 'db/003_indexes.sql').read_text())        detail = ' '.join(row[3] for row in db.execute(            '''EXPLAIN QUERY PLAN               SELECT order_id, order_number, ordered_at               FROM sales_order               WHERE customer_id=1               ORDER BY ordered_at DESC, order_id DESC LIMIT 20'''        ))        assert 'idx_order_customer_history' in detail

TABLE sales_orderPurpose:  Durable header for one customer checkout request.Grain:  One row per order.Primary key:  order_id INTEGER.Alternate keys:  order_number, request_key.Foreign keys:  customer_id -> customer; shipping_address_id -> address.Lifecycle:  draft | submitted | paid | packed | shipped | cancelled.Retention:  Retain for the financial/operational policy; do not hard-delete casually.Sensitive fields:  customer_id and shipping_address_id are indirect personal data.Main writers:  checkout and order-management services.Main readers:  customer service, fulfillment, finance, reporting views.Known invariants:  request_key unique; status domain; currency length 3.Related tests:  test_idempotent_request, test_address_ownership, test_order_reconciliation.

# ADR-002: Reserve inventory in the order transactionStatus: AcceptedContextNorthstar must prevent two checkouts from reserving the same available units.The database stores on_hand and reserved per warehouse-product balance.DecisionThe checkout service owns reservation. It begins a write transaction and runs aconditional UPDATE whose predicate requires on_hand - reserved >= requested quantity.Every order line and audit event commits in the same transaction.Consequences+ No partial order can outlive a failed reservation.+ The predicate rechecks availability at write time.+ request_key makes retries idempotent.- SQLite permits one writer at a time; production scale may require PostgreSQL row locks.- Reservation expiry/release needs a future workflow.EvidenceTransaction tests cover success, retry, insufficient stock, and rollback.

README.md  Purpose, prerequisites, quick start, supported engines, current limitations.docs/conceptual-model.md  Scope, actors, entity grains, cardinalities, business rules.docs/data-dictionary.md  Tables, columns, keys, domains, sensitivity, ownership, retention.db/queries/README.md  Query names, grains, inputs, expected outputs, indexes, security.runbooks/backup_restore.md  RPO/RTO, commands, storage, encryption, restore, verification, escalation.runbooks/release_checklist.md  Migration order, compatibility, backup prerequisite, checks, rollback/forward fix.docs/decisions/*.md  Price snapshot, inventory reservation, revenue definition, access model.CHANGELOG.md  User-visible and operational database changes by version.

[ ] Fresh clone builds northstar.db without manual edits.[ ] All 90 SQL and Database Fundamentals lessons are linked and published.[ ] Schema, seed, indexes, queries, and tests are version controlled.[ ] PRAGMA integrity_check returns ok.[ ] PRAGMA foreign_key_check returns no rows.[ ] Negative constraint tests pass.[ ] Operational and analytical reconciliation totals agree.[ ] Order placement is atomic and idempotent.[ ] Important query plans use intended access paths.[ ] Roles and parameterization boundaries are documented.[ ] Backup restores into a separate database and passes verification.[ ] Data dictionary, ADRs, runbooks, and limitations are complete.[ ] Presentation connects every major decision to evidence.