LabDataStorageEvaluation/docs/rules/db-sql-schema.md

# DB / SQL / Schema - conventions for the SQLite and PostgreSQL benchmark targets

**Purpose.** Naming, key, and loading conventions for the two relational
storage formats under evaluation: the SQLite file (`./out/sqlite/tribo.db`,
spec [05](../specs/05_convert_sqlite.md)) and the PostgreSQL 16 database
`tribo` (spec [06](../specs/06_convert_postgresql.md)).
**Binding scope.** Every table, index, and load routine in either engine.
The two engines carry the SAME logical schema; they differ only in types,
storage options, and load mechanics. The full table list and DDL signatures
live in the specs - this file codifies the conventions the DDL must follow,
and it wins where a spec detail is ambiguous.

---

## 1. Two kinds of tables

- **Dimension tables** (`batches`, `wafers`, `coupons`, `runs`,
  `instruments`, `tracks`): small, joined constantly.
  - PK: integer surrogate, named `<singular>_id` (`coupon_id`, `track_id`).
    Never a bare `id`, never TEXT.
  - The natural key from the CSV corpus (`B721`, `B721-W1-C07`,
    `B721-W1-C07-T2` - see [data-naming-units.md](data-naming-units.md))
    is stored as a TEXT column named `<singular>_code`, declared `UNIQUE`.
    It is for lookups and human-readable output, never the join key.
- **Bulk tables** (`friction_cycles`, `friction_loop_points`, `xrf_points`,
  `profilometry_points`, `nanoindentation`, `afm`, `wear`): millions of
  rows, scanned and filtered.
  - Reference dimensions by integer FK columns only. **TEXT foreign keys in
    bulk tables are forbidden** - they bloat the file and slow every scan.
  - PK is the natural composite where one exists: `(track_id, cycle)` for
    cycles, `(track_id, cycle, pt)` for loop points; single-column
    `track_id` / `coupon_id` where the grain is one row per entity. In
    SQLite, composite-PK bulk tables are `WITHOUT ROWID`.
  - No surrogate `bigserial` on bulk tables - it adds bytes and nothing
    else.

## 2. Table and column naming

- Tables: lowercase, snake_case, plural (`friction_cycles`, not
  `FrictionCycle`).
- Columns: lowercase snake_case; measurement columns carry the SI unit
  suffix in **lowercase** (`hardness_gpa`, `max_load_mn`,
  `wear_volume_um3`). The CSV corpus keeps SI capitalization
  (`hardness_GPa`); the SQL mapping is a plain lowercase of the CSV name -
  see the canonical mapping in
  [data-naming-units.md](data-naming-units.md).
- Denormalization is deliberate and limited: `environment` and `load_mn`
  are copied onto `tracks` (from `runs` / test conditions) because
  Q4 and Q7 filter on them constantly. Do not denormalize further without
  updating this rule.

## 3. Indexes - only what Q1-Q7 use

- Create indexes AFTER the bulk load, never before.
- The index set is driven by the benchmark queries: `coupons(au_wtpct_mean)`,
  `tracks(run_id)`, `tracks(coupon_id)`, `runs(environment)`, composite
  `tracks(environment, load_mn)`; PostgreSQL adds BRIN on
  `friction_cycles(cycle)` per partition.
- Naming: `ux_<table>_<cols>` for unique, `ix_<table>_<cols>` for
  non-unique.
- Do not over-index: an index no query uses is disk footprint that skews
  the storage comparison AND load-time cost. Every index must name the
  query it serves (a one-line comment in the DDL).

## 4. Loading

- **SQLite:** during load `PRAGMA journal_mode=OFF, synchronous=OFF,
  cache_size=-262144`; insert via `executemany` in transactions of 50k
  rows; after load `PRAGMA journal_mode=WAL`, `ANALYZE`, `VACUUM`.
- **PostgreSQL:** load via `COPY FROM STDIN` (psycopg3 `copy`), never
  row-by-row INSERT; `friction_cycles` and `friction_loop_points` are
  declaratively RANGE-partitioned by `track_id` (12 partitions of 120
  tracks); `VACUUM ANALYZE` after load.
- Loads are all-or-nothing per table and validated against `MANIFEST.csv`
  row counts - see [code-error-handling.md](code-error-handling.md)
  section 3 and [test-pipeline-validation.md](test-pipeline-validation.md).
- **Foreign keys are ENFORCED in both engines on every table, including
  bulk tables** (owner decision 2026-07-11, after measurement on the real
  corpus: +0.5 s load on 4.3M rows in SQLite, zero disk and read cost;
  full study with ER diagrams and measurements:
  [docs/research/Tribology_FK_Architecture.html](../research/Tribology_FK_Architecture.html)).
  Every SQLite connection sets `PRAGMA foreign_keys = ON` (SQLite silently
  ignores FK clauses without it). Row-count validation against
  `MANIFEST.csv` remains a second, independent integrity gate.

## 5. `track_summary` - precomputed, and off-limits to Q5

Both engines carry a `track_summary` (plain table computed during load in
SQLite; materialized view in PostgreSQL) with per-track derived values
(`cof_ss_mean`, `run_in_cycles`, ...). It exists for Q2/Q3/Q4/Q6. **Q5 must
never read it** - Q5 is the forced full-scan benchmark over raw
`friction_cycles` in every format (spec
[08](../specs/08_benchmark_queries.md)).

Binding computation (identical semantics in both engines; float results
agree within the 1e-9 checksum tolerance):

1. Steady-state proxy window = cycles 501-1000 (the second half);
   `m` = avg(cof), `s` = stddev_pop(cof) over that window.
2. `run_in_cycles` = the first cycle `c` with `|cof(c) - m| < 2*s`;
   fallback 500 if no cycle qualifies.
3. `cof_ss_mean` / `cof_ss_std` = avg / stddev_pop of cof over cycles
   `> run_in_cycles`.

SQLite computes this in Python during load
([common/track_summary.py](../../common/track_summary.py)); PostgreSQL as
a materialized view implementing the same three steps.

## 6. Engine parity

- Same logical schema, same table names, same column names, same row
  counts in both engines.
- Q1-Q7 executed against SQLite and PostgreSQL MUST return identical
  result sets (checksum parity with 1e-9 float tolerance - the canonical
  expected checksums are computed from PostgreSQL and cross-validated
  against SQLite; see
  [test-pipeline-validation.md](test-pipeline-validation.md)).
- A schema change in one engine is a schema change in both, in the same
  commit.

## 7. Anti-patterns

- **Bare `id` PK, TEXT PK, or a GUID column** - this is a load-once
  analytical schema; surrogate integer `<singular>_id` on dimensions,
  natural composites on bulk tables.
- **TEXT FK in a bulk table** (`friction_cycles.track_code`) - integer FKs
  only.
- **A `bigserial` surrogate on `friction_cycles` / `friction_loop_points`**
  - dead weight at 1.4M / 2.9M rows.
- **Creating indexes before the bulk load** - doubles load time for
  nothing.
- **An index without a named query** - footprint that skews the format
  comparison.
- **Rewriting Q5 to use `track_summary`** - breaks the benchmark's
  full-scan contract.
- **`updated_at` / audit columns / triggers** - this is a read-only
  benchmark target rebuilt from CSV; it has no mutation lifecycle.
- **Divergent schemas between the engines** - parity is what makes the
  benchmark comparison valid.