STD-D — The Data Standard

Subordinate to Codex 1 and the Codices named in §1. See 00-index.md for authority, normative vocabulary, citation format, and waiver rules, which this Standard does not restate.

1. Purpose

1.1 This Standard makes the data-model doctrine of Codex 0 chapters 06 and 07, and Codex 5, checkable. It states what a reviewer who did not do the work must be able to verify before a schema, migration, or dataset ships.

2. Scope and non-scope

2.1 In scope: canonical-record rule, identifier policy, naming conventions, time and money representation, nullability and defaults, enum-versus-lookup-table choice, the migration standard, derived and anonymized datasets, retention and severance, data quality, schema documentation.

2.2 Out of scope: which role may read or write a table (STD-S), the engine that produces or consumes a record (STD-A), and the graph's node/edge model beyond how it references canonical entities (Codex 0 ch. 06, cited not restated).

2.3 Binds new tables, new columns, and any migration touching an existing table's shape. Existing non-conformant shape found in unrelated review is logged as named debt, not silently fixed as a side effect.

3. Normative clauses

3.1 Canonical record and no duplicate Person

3.1.1 Every real-world thing must have exactly one authoritative row; other tables reference it by ID and must not hold a second copy of its fields for convenience. A reviewer verifies this by checking a new table does not duplicate columns already owned by an existing canonical entity's table. (Codex 0 §07.2.1, Codex 5)

3.1.2 A human being must have exactly one Person row for life. No onboarding, intake, or workflow may create a second Person row for someone already known to the institution. A reviewer verifies this by confirming any new intake path performs identity resolution against existing Person rows before insert, and blocks rather than writes speculatively when resolution is ambiguous. (Codex 0 §07.2.2)

3.1.3 A role grant must never be a column on Person, Membership, or any profile-shaped table. A reviewer verifies this by confirming no migration adds a role-like column to a profile table; role data lives only in the Role table. (Codex 0 §07.2.3)

3.1.4 A value obtainable by joining or computing from canonical tables must not be stored redundantly as if canonical, unless it is explicitly marked as a materialized cache with a documented rebuild path. A reviewer verifies any denormalized column carries a comment or migration note naming its source query and rebuild trigger. (Codex 0 §07.2.5)

3.2 Identifier policy

3.2.1 Every table's primary key must be a UUID surrogate key generated at creation, never a natural key (email, government ID, external system ID) used as primary key. A reviewer verifies the migration's primary key column type and generation default.

3.2.2 A natural or external identifier may be stored as a unique, indexed column but must not be the primary key and must not be assumed stable if the identifier's issuing authority is outside the institution's control.

3.2.3 Every entity's identifier must be non-reused and institution-scoped, assigned at creation and never altered. A reviewer verifies no migration or application code ever updates a primary key value in place. (Codex 0 §07.3.4)

3.2.4 A stable, public-facing identifier (a display code, a verification token, a slug) must be a separate field from the primary key and may change; the primary key never does. A reviewer verifies a public-facing route resolves by the public field, not by exposing the internal UUID as the only lookup path where a stable public form is required (for example, credential verification tokens).

3.2.5 A reference from one table to another must be by ID, and an entity must not embed another entity's mutable fields as a snapshot unless the snapshot is explicitly a historical record (for example, a credential recording the exact capability version it certifies). A reviewer verifies foreign-key columns exist for references and any embedded snapshot field is documented as intentional history. (Codex 0 §07.3.5)

3.3 Naming conventions

3.3.1 Table names must be plural, lower-case, snakecase, and match the canonical entity name in the Registry (e.g., `capabilityassessments for CapabilityAssessment`). A reviewer verifies the migration's table name against the Registry entity list.

3.3.2 Column names must be lower-case snakecase, must not abbreviate beyond common institutional convention (`id`, `createdat, updatedat`), and a foreign-key column must be named `<referencedsingular>id` (e.g., `personid`). A reviewer verifies naming against this pattern for every new column.

3.3.3 An enum type or lookup table's values must be named as stable, lowersnakecase tokens, never as display-facing prose; display text is a presentation-layer concern. A reviewer verifies enum values are not sentence-cased strings intended for direct display.

3.3.4 A boundary name (an API- entry, a public route path segment) must use the Registry's assigned name and must not introduce an alternate name for the same boundary. A reviewer verifies the route or contract name matches the Registry entry.

3.4 Time representation

3.4.1 Every timestamp column must be stored as UTC timestamptz, with timezone information preserved at the point of capture. A reviewer verifies the column type in the migration and that application code does not strip timezone before writing. (Codex 0 §07.3.1)

3.4.2 Display localization of a timestamp happens at the surface only; a migration or backend transformation must never alter a stored timestamp to represent a local time. A reviewer verifies no server-side code converts and re-stores a timestamp in a non-UTC form.

3.4.3 A value that is inherently a date without a time component (a birth date, a target date) must be stored as a date type, not coerced into a timestamp with an arbitrary time component. A reviewer verifies the column type matches the semantic meaning of the value. (Codex 0 §07.3.2)

3.5 Money representation

3.5.1 Every monetary amount must be stored as an integer minor-unit value (cents or the smallest unit of the recorded currency), paired with an explicit currency code column. A reviewer verifies the column type is an integer type, not numeric, float, or double, and that a currency column exists alongside it. (Codex 0 §07.3.3)

3.5.2 No monetary value may ever be stored, transmitted, or computed as a floating-point number at any layer. A reviewer verifies application-layer arithmetic on money uses integer or fixed-precision decimal operations, not native floating-point math.

3.6 Nullability and default discipline

3.6.1 A column must be declared NOT NULL unless the absence of a value is a meaningful, named state, not merely an oversight. A reviewer verifies the migration states, in a comment or the review description, why a nullable column is nullable.

3.6.2 A column must not carry a default value that silently substitutes for a decision the application should make explicitly (for example, defaulting a status to active when the correct initial state depends on workflow context). A reviewer verifies defaults are either genuinely universal or absent, forcing the application to state the value.

3.6.3 A boolean column represents exactly one binary fact and must not be repurposed to mean a third state later; a third state requires an enum or lookup table, not a nullable boolean. A reviewer verifies a nullable boolean is not being used to encode tri-state logic.

3.7 Enum versus lookup table

3.7.1 A closed, small, rarely-changing set of values known at schema-design time may be a database enum type. A reviewer verifies the set is genuinely closed (unlikely to require an application deploy-independent addition) before approving an enum.

3.7.2 A set of values that grows, requires metadata beyond a label (ordering, description, active/inactive state), or is edited by non-engineering staff must be a lookup table, not an enum. A reviewer verifies a proposed enum is rejected in favor of a lookup table where any of these conditions hold.

3.7.3 Changing an enum's members requires a migration; a reviewer verifies the migration is additive (new value appended) rather than renaming or removing a value already referenced by existing rows, unless a data-migration plan accompanies the removal.

3.8 The migration standard

3.8.1 Every migration must be forward-only in the deployed sequence; a reviewer verifies no migration is edited after it has shipped to any environment beyond local development. (Codex 7, "Testing and verification"; Codex 0 §17)

3.8.2 A migration that could be destructive (dropping a column, dropping a table, narrowing a type) must state a reversible plan — how the change could be undone or compensated — in the migration's accompanying documentation before it is approved. A reviewer verifies this plan is present and specific, not "restore from backup."

3.8.3 Every migration that creates a public table (any table holding member data) must ship explicit GRANTs and row-level security policies in the same migration as the CREATE TABLE statement. A reviewer verifies both are present in the identical migration file; a table shipped without them, planned for a follow-up migration, is rejected outright with no waiver available. (Codex 0 §07.2.7, §17.6.1, Codex 7)

3.8.4 A new table's row-level security default must deny all access until a policy explicitly grants it; a reviewer verifies no table is created with RLS disabled or with a permissive default policy. (Codex 0 §17.6.3)

3.8.5 No destructive change (drop, irreversible type narrowing, data-erasing column removal) may ship without a named repayment: what is lost, why the tradeoff was accepted, and what restores equivalent capability if needed. A reviewer verifies this is recorded in the migration's documentation or a linked decision record before approval. (Codex 7, "Technical debt")

3.8.6 A migration changing an entity's meaning (not merely its storage detail) must follow the contract-change procedure: a version, a migration window where consumers exist, and a decision record. A reviewer verifies this procedure was followed by checking for the decision record before approving a meaning-changing schema change. (Codex 0 §07.1.3)

3.9 Derived and anonymized datasets

3.9.1 A dataset with identifying fields removed or aggregated for research, reporting, or model training must be recorded as a derived dataset with its own transformation record (what produced it, from which canonical source, when) and must never be treated as, or promoted to, a primary record. A reviewer verifies a transformation record exists and that no downstream code writes back into the canonical tables from the derived dataset. (Codex 0 §07.2.6)

3.9.2 An aggregation used in a derived or reporting dataset must enforce a minimum group size (an aggregation threshold) before returning a value computed over fewer identifiable people than that threshold, to prevent re-identification by inference. A reviewer verifies the query or view enforcing this threshold is present and that no code path allows a caller to bypass it by narrowing filters. (Codex 0 §07.2.6, §17.6, Codex 1 Article VI.2, restated)

3.9.3 Reporting or analytics boundaries must never return individual-level data beyond the scope an Organization or a research consent explicitly agreed to; scope must be enforced at the data layer, not trusted to the calling surface. A reviewer verifies the enforcement point is a database-layer policy or an equivalently server-side check, not a client-side filter. (Codex 0 §10.12.10)

3.10 Retention, soft delete, and severance

3.10.1 Soft delete and severance are distinct operations and must not be conflated in code or in a migration's design. A soft-deleted record must remain fully present and reversible by an authorized role; a severed record must have identifying and personal fields removed, irreversibly. A reviewer verifies the implementation of a "delete" feature states which of the two it performs and matches the correct mechanism (a status flag versus field nulling plus anonymization). (Codex 0 §07.4.1)

3.10.2 A severance operation must not be implemented as reversible; a reviewer verifies no "undo severance" path exists in code, since reversibility would defeat the deletion right it implements. (Codex 0 §07.4.2)

3.10.3 What must survive severance and be resolvable indefinitely: issued credentials and their certified capability versions, audit records of consequential actions, and anonymized aggregates. A reviewer verifies a severance implementation retains these specific record types (with identifying fields stripped where the record type requires it) rather than cascading full deletion across them. (Codex 0 §07.4.2, §07.12, §18.2.2)

3.10.4 A retention period shorter than a stated legal, contractual, or credential-verification obligation must never be applied; the longer obligation always governs. A reviewer verifies a proposed retention or purge job checks for such obligations before deleting or anonymizing a record class. (Codex 0 §07.4.3)

3.10.5 History-bearing tables (assessments, credentials, index values, decisions, progress records, role grants) must be additive: a correction or re-attempt produces a new row, never an in-place overwrite of a finalized record. A reviewer verifies the write path for these tables uses INSERT, not UPDATE, for any change that would alter a previously asserted fact. (Codex 0 §07.2.4)

3.10.6 A Credential row must never be hard-deleted and must remain publicly resolvable for at least twenty years from issuance regardless of the issuing person's own account status. A reviewer verifies no cascade rule attached to Person severance or deletion removes or hides a Credential row beyond stripping the issuer-side personal fields it does not itself require. (Codex 0 §07.12, §18.2.2, §18.6.5)

3.11 Data quality obligations

3.11.1 A foreign-key column must carry an actual foreign-key constraint at the database level, not merely an application-level convention, unless a specific, documented reason (cross-database reference, extreme write-volume hot path) exempts it. A reviewer verifies the constraint exists in the migration or that the exemption is documented.

3.11.2 A field with a known, closed validation rule (a currency code, a country code, an email shape) must be validated at the boundary before it reaches the data layer, in addition to any database-level check. A reviewer verifies both the boundary validation and, where practical, a database check constraint exist. (Codex 0 §10.5.1, §10.5.3)

3.11.3 An orphaned reference (a foreign key pointing to a row that no longer satisfies its own invariants, such as a deprecated node still required to resolve) must remain resolvable, never silently nulled or cascaded away, where another canonical record still depends on it. A reviewer verifies cascade rules on deletion do not remove a referenced row while a dependent record exists. (Codex 0 §06.6.4, §07.10)

3.11.4 Data-quality metrics for a new dataset (orphaned rows, null rates on non-nullable-in-spirit fields, referential gaps) must be named and monitorable at the time the dataset is introduced, not left undiscovered until an incident. A reviewer verifies a monitoring or query artifact for at least the primary integrity risk is part of the change.

3.12 Schema documentation obligations

3.12.1 Every new table must have its purpose, owning division, key fields, lifecycle, relationships, and retention rule documented in the Registry's entity section (mirroring Codex 0 chapter 07's per-entity structure) before or in the same change that creates it. A reviewer verifies this documentation exists and is not deferred to "add later."

3.12.2 A migration's commit or pull-request description must cite the specific Codex or Standard clause governing any non-obvious choice (a nullable field, a chosen retention period, an enum-versus-lookup decision). A reviewer verifies the citation resolves to an actual clause supporting the specific choice made.

3.12.3 A schema change that alters an entity's meaning must be reflected in the Registry entity entry's version history, not only in the migration file, so a future engineer reading the Registry sees the same history the database shows. A reviewer verifies both are updated together.

4. Governing Codex clauses

Codex 0 chapter 06 (Knowledge Graph, referenced for how it references canonical entities and never duplicates them); chapter 07 (the data model, ENT-01 through ENT-19, normalization, time, money, identifiers, retention); Codex 5 (shared data model, normalization rules, contract rules); Codex 7 (engineering doctrine, migrations, GRANTs and RLS in the same change); Codex 1 Article VI (server-side enforcement, data use, deletion right), cited where a data clause implements it.

5. Conformance checklist

1. 1Does this change create a second Person row or a role column on a profile table? — yes / no / not-applicable-because
2. 2Does every new table use a UUID surrogate primary key with a separate stable public identifier where one is needed? — yes / no / not-applicable-because
3. 3Do table, column, and enum names follow the stated naming convention? — yes / no / not-applicable-because
4. 4Are all timestamps UTC timestamptz and all dates stored as date, with no local-time coercion? — yes / no / not-applicable-because
5. 5Is every monetary amount an integer minor-unit value with an explicit currency code, with no floating-point use? — yes / no / not-applicable-because
6. 6Is every non-nullable-in-spirit column declared NOT NULL, with nullability elsewhere justified? — yes / no / not-applicable-because
7. 7Is the enum-versus-lookup-table choice correct for the set's volatility and metadata needs? — yes / no / not-applicable-because
8. 8Does the migration ship GRANTs and RLS in the same migration as CREATE TABLE, defaulting to deny? — yes / no / not-applicable-because
9. 9Does any destructive change carry a reversible plan and a named repayment? — yes / no / not-applicable-because
10. 10Is a derived or anonymized dataset recorded with its transformation and an enforced aggregation threshold? — yes / no / not-applicable-because
11. 11Does the change correctly distinguish soft delete from severance, and does severance preserve credentials and audit records? — yes / no / not-applicable-because
12. 12Are history-bearing tables written additively rather than overwritten in place? — yes / no / not-applicable-because
13. 13Do foreign keys carry real constraints, and are known-shape fields validated at the boundary? — yes / no / not-applicable-because
14. 14Is the new table documented in the Registry entity section with clause citations for non-obvious choices? — yes / no / not-applicable-because

6. Interfaces to other Standards

6.1 STD-A governs the layering that restricts which layer may issue a query against tables this Standard defines; a schema-level fix does not excuse a layering violation found elsewhere.

6.2 STD-S governs the depth of row-level policy content (which role sees which row); this Standard requires only that RLS and GRANTs exist in the same migration, not their specific rule content.

6.3 STD-I governs what data an AI agent may read or write against these tables; this Standard governs only the table's own shape and integrity regardless of caller.

6.4 STD-Q is the final gate; conformance to this Standard is necessary but not sufficient for a change to ship.

7. Open questions

7.1 The exact minimum aggregation threshold (§3.9.2) for derived and reporting datasets is not yet fixed institution-wide and is deferred to a decision record before the first large-scale reporting boundary is promoted to stable.

7.2 Whether a deceased standing on Person triggers severance automatically or requires a separate lawful-basis review remains open, per Codex 0 chapter 07's own open question, and this Standard defers to that resolution.

7.3 The tooling that will enforce naming-convention and RLS-presence checks automatically (rather than by human review alone) is not yet chosen; until it is, these clauses are verified manually at review.

8. Status

8.1 This Standard is an internal engineering instrument, not a compliance certification, and must not be quoted as one (Standards index §8).

8.2 A must not clause here — a duplicate Person row, a floating-point monetary value, a public table shipped without GRANTs and RLS in the same migration, an overwritten history-bearing record, a reversible severance — carries no waiver path. A must clause may be waived only per the Standards index §4 procedure, with a named owner and expiry.

9. Amendment

9.1 A clause in this Standard is amended only by a decision record naming the clause, the reasoning, and what it supersedes, per Standards index §7. A struck clause number is never reused.