Data model stewardship runbook

Status: procedure reference. The rules — schema audit before large features, Organization as canonical identity, use the shared primitives — live in AGENTS.md §11 and stay always-on. This file holds the primitive inventory, helper boundaries and audit indicators. Relocated by BI-0020D511 Phase 1; no rule was dropped.

Before adding any large feature, audit the existing schema for refactoring opportunities. Indicators that refactoring is needed: a domain model being reused as a shared concept; the same logical data appearing in two+ existing models; a new feature needing meta-data with no canonical home. → kernel principle

Organization is the canonical platform identity model. Any feature needing org name, slug, logo, address, or contact info reads from Organization — not from BrandingConfig, env vars, or bespoke fields elsewhere. → kernel principle

The Organization.address JSON has one canonical shape + helpers in apps/web/lib/shared/org-address.ts (OrgAddress, parseOrgAddress / serializeOrgAddress / formatOrgAddressLines, resolveTimezoneFromAddress). Read and write the address through those — do not hand-roll a parallel address field or shape. It is captured at setup via the business-context step (/storefront/settings/business) and is the precise source for state-accurate timezone derivation (BI-AAAA0691). The same JSON carries the business’s own map point (latitude, longitude, validationSource, validatedAt; ORG_LOCATION_KEYS), written by geocode-on-save and read by nearby discovery. serializeOrgAddress drops a provider-derived point when the address text changes and keeps a hand-entered or person-confirmed one (BI-C318C227).

Shared micro-primitives (BET-6, BI-6A505BFF). Cross-cutting helpers that were hand-inlined at hundreds of sites now have one home each — import them, do not re-copy:

• Server-action result: apps/web/lib/shared/action-result.ts — ActionResult<T> ({ ok: true; data: T } | { ok: false; error: string }) with ok(data?) / err(message) constructors. The canonical shape for a server action’s return.
• JSON coercion: apps/web/lib/shared/coerce.ts — isRecord(v) (object guard), asString(v, fallback?), asNumber(v, fallback?) for narrowing Prisma.JsonValue / unknown. Packages cannot import from apps/web, so they use the same guard from @dpf/validators (packages/validators/src/guards.ts), and plain-Node scripts use scripts/lib/is-record.mjs. The CI ratchet scripts/check-no-local-isrecord.mjs fails on any other local isRecord or isPlainObject in apps/web/lib, apps/web/components, packages/ or scripts/lib. Two @dpf/storefront-templates modules keep a local copy (allowlisted with the reason): the docs:business-types generator imports them, and the guard CI job installs only @dpf/repo-guard-runtime, so a workspace import would not resolve there.
• Display formatters (plan 2026-09-08 §10.5 S6): dates and times in apps/web/lib/datetime.ts (formatInstant, formatDateTime, formatDate, formatTimestamp), money in apps/web/lib/org-locale/org-locale.ts (formatMoney). The ratchet scripts/check-no-local-formatters.mjs lists every remaining local copy with the reason it has not moved (most hard-code en-GB or en-US, so moving them changes what users see); new code imports the shared helper.
• Route paths: apps/web/lib/routes.ts — ROUTES.* named constants for the high-frequency section roots passed to revalidatePath / redirect / <Link>, so a rename is a single compiler-checked edit. Emailed links to public token pages (bill approval, expense approval, invoice payment) go through tokenLinkUrl() over TOKEN_LINK_ROUTES in the same file; routes.test.ts fails if an entry has no [token] page or a sender hand-builds a ${baseUrl}/ link.
• Kebab-case slugs: apps/web/lib/shared/slugify.ts — slugify(s); compose a cap or fallback around it (slugify(x).slice(0, 64), slugify(x) || "fallback") instead of defining a local slugify. The ratchet scripts/check-no-local-slugify.mjs fails on a new local copy. Its allowlist keeps each copy whose output differs (a _ separator, uppercase, heading-anchor rules) with the reason, because slugs are persisted and changing the transform would change stored keys.
• Markdown rendering: apps/web/lib/shared/markdown.ts — renderMarkdown(source, options) and parseMarkdown(source) share one markdown-it parser with raw HTML off. Document validation uses the latter for source tokens and line maps so its block boundaries agree with rendered Markdown; components show the result through MarkdownHtml, the one dangerouslySetInnerHTML for markdown. Per-surface behaviour is an option, not a second parser: element classes, tableWrapperClass, headingId, resolveLink, resolveImage (null renders [alt]; without it only same-origin and embedded data:image images render, so a model-written off-origin image cannot exfiltrate on display — BI-94E08D68), renderFence and wikilinkHref. HTML written in the markdown shows as text, and a javascript: link stays literal text. The ratchet scripts/check-no-local-markdown-renderer.mjs fails on an import of any markdown library elsewhere. A caller whose options are a pure module constant can pass memoize to MarkdownHtml. The HTML is then reused per options object and source, bounded at 500 entries, so a finished coworker message renders once rather than on every panel render (BI-EDE2AF6F). Plan 2026-09-08 M5.
• Untrusted text reaching a model: packages/validators/src/untrusted-text.ts — sanitizeUntrustedText(s) / sanitizeUntrustedValue(v) remove hidden Unicode (Tags-block “ASCII smuggling”, zero-width splitting, invisible operators, chained variation selectors, bidi controls) while keeping emoji ZWJ sequences, subdivision flags, Persian/Indic ZWNJ, CJK variants, soft hyphens, and the direction marks and isolates that Arabic, Hebrew and other right-to-left text needs (overrides and runs of three or more are still removed; localization epic EP-6B33A840); looksLikeSmuggling and invisibleRemovalNotice flag a payload. It cleans what a model reads, never what is stored, so a direction mark is not lost when an agent reads, edits and writes a record back. Already applied at every tool result (clampToolResultForModel, /api/mcp/v1), the chat-attachment prompt context, onboarding enrichment and mailroom prompts; a new path that puts outside text into a prompt calls it rather than writing its own filter. Its regression set is packages/validators/src/hostile-content-fixtures.ts (BI-5BC34E0A): one shared set of hostile payloads and benign look-alikes, exercised against the sanitizer, the tool-result boundary, the markdown image policy, the attachment fence, the voice detector and the repository guards; a new control adds its case there. BI-7AD0DA3D, EP-E76E81D1.
• Canonical JSON for hashes and signatures: packages/integration-shared/src/canonical-json.ts — canonicalJson(value), imported as @dpf/integration-shared/canonical-json from apps/web and packages. Plain-Node scripts use the byte-identical scripts/lib/canonical-json.mjs; a randomized test holds the two together. Keys sort by UTF-16 code unit, never localeCompare. undefined is dropped inside objects and becomes null elsewhere. Numbers serialize as JSON.stringify does, and toJSON is not called. The ratchet scripts/check-no-local-canonical-json.mjs fails on a new local sorted-key serializer. Its allowlist keeps the older copies, each with the exact way its output differs. None is byte-identical to the home, and most feed a persisted hash, signature or idempotency key. Migrating one changes those values, so it needs a per-call-site decision and a migration note.

Route-segment helpers vs shared domain modules (BI-IMP-BC5AA87E). Page-local helpers under a route segment (e.g. apps/web/app/(shell)/…/_helpers.ts or a colocated *.ts next to page.tsx) may format, adapt, or present canonical domain data for that surface only. They must not invent a second home for reusable agent/delegation policy, tool metadata, grant maps, persisted outcome contracts, or identifiers that other routes need — those live in shared modules under apps/web/lib/ (or packages). If a helper is imported from a second route or encodes policy that would change coworker behavior platform-wide, promote it to a shared canonical module in the same PR.

Metadata governance (BI-IMP-FA900452, BI-IMP-52761525). JSON metadata columns (e.g., unstructured payload fields) are reserved for optional, unstructured, or rapidly-evolving context. They must not be used as primary query or reporting sources. Any property that becomes frequently filtered, queried, or joined must be promoted to a typed schema field (via a database migration). When reading or writing JSON metadata in code, use typed accessor helpers and centralized key constants rather than raw string indexing. A provider publication/thread/conversation id is normalized through the owning domain’s canonical identity or relationship mapping before a report joins on it; an incidental metadata key is never an implicit foreign key. → kernel principle · reporting boundary

Principal convergence (2026-05-09). Per the addendum on docs/superpowers/specs/2026-04-22-enterprise-auth-directory-federation-design.md, any new identity-bearing entity introduced after 2026-05-09 must be modeled as a PrincipalAlias linked to a single Principal, not as a parallel identity table. The convergence target covers User, CustomerContact, Agent, EdgeNode, MobileDevice, and ServiceAccount. Authorization decisions resolve on the Principal; alias kind tells the platform which surface authenticated the request. → kernel principle

Record lifecycle convention

The ONE “not active” convention (BI-C357FA5A, Simplify & Strengthen W20, architecture pass 2026-08-16 §3.2-d). The pass measured “not active” said six ways across the schema — archivedAt, supersededById, mergedIntoId, quarantinedAt/overlapQuarantinedAt/conflictQuarantinedAt, retiredAt, and status="quarantined" — so every reader had to know which convention applies per table. The unified convention is:

• lifecycle RecordLifecycle @default(active) — the enum (declared in packages/db/prisma/schema/resource-scheduling.prisma) closes the state set: active | archived | retired | superseded | merged | quarantined. A record is active or it carries exactly one not-active state.
• lifecycleAt DateTime? — when the record left active. NULL while active.
• lifecycleReason String? (optional) — free-text operator/system context, including legacy-*: markers minted by convention migrations.
• Successor pointers (supersede/merge chains) are declared self-relations with a leading index in the owning family — never bare *Id columns (the FK-index ratchet enforces the declared+indexed half).

Semantics per state: archived = kept for reference, excluded from operational reads; retired = permanently withdrawn from service; superseded = replaced by a successor record (pointer required); merged = collapsed into a survivor record (pointer required); quarantined = excluded pending integrity review (the record is suspect, not the data subject).

Enforcement. scripts/check-no-new-notactive-conventions.mjs blocks NEW legacy-convention columns schema-wide; the 29 existing carriers live in the shrink-only owned baseline scripts/notactive-conventions-baseline.json (owner platform-architecture). New String status columns carrying quarantine vocabulary are already blocked by scripts/check-no-new-closed-set-strings.mjs.

Pilot family. The W19 unified resource-scheduling models (Resource, ResourceAvailability, ResourceCapacityPool, ResourceCapacityAllocation) are born on the convention — zero-data adoption. Existing carriers migrate per the operator-reviewed plan: docs/superpowers/plans/2026-08-18-w20-lifecycle-convention-migration-plan.md. Business-state machines (e.g. an allocation’s reserved→confirmed→released flow) remain domain enums — the record lifecycle says whether the ROW is live, not where the BUSINESS process stands.

Model metadata lives in the schema and the catalog

One declaration, one carrier, many readers (BI-D9F158AF, EP-A33A5C61 slice 4, founder direction 2026-09-08). Governance metadata for a model is declared ONCE as a /// @dpf documentation line directly above the model block and converged into the Postgres catalog as COMMENT ON TABLE "X" IS 'dpf:{...}' at every portal boot (packages/db/scripts/apply-model-metadata-comments.ts, called from scripts/portal-migrate-boot.sh right after prisma migrate deploy). The parser, vocabularies and carrier format are in packages/db/src/model-metadata.ts.

/// @dpf lifecycle=telemetry-bounded retention=365d sensitivity=internal categories=telemetry,security-audit owner=platform-architecture steward=data-steward timeAxis=createdAt
model ToolExecution { ... }

• Keys (closed set). lifecycle (operational · telemetry-bounded · business-record · regulated-record · security-audit · legal-evidence · ephemeral) and retention are required. retention is exactly one disposition: <N>d (auto-purge past N days on timeAxis), retained (statutory minimum, needs basis=), domain (rows follow their own lifecycle), reference, config, or projection (derived copy, reconciled not aged). Optional: sensitivity, categories, scope (pci-cardholder / phi-health), owner, steward.
• The gate. scripts/check-model-metadata-tags.mjs (source policy guards) fails the build on an invalid tag, a purge window on a non-purgeable class, or a NEW persistent model with no tag. Models that predate the convention sit in scripts/model-metadata-baseline.txt, which can only shrink — tag a model and run --update.
• Reading it. SELECT relname, obj_description(oid, 'pg_class') FROM pg_class answers lifecycle and sensitivity for every table; external catalog tools ingest the same comment. In code, parseCatalogComment turns the string back into the typed declaration.
• What it replaced. The tags were seeded once from table-classification.ts, the govern/data asset registry, the hand-typed lists in operate/retention/policies.ts and scripts/stewardship-exemptions.txt. The retention sweep now builds its policies from the catalog (apps/web/lib/operate/retention/declarations.ts); the hand lists, the name-suffix enrollment guard with its allowlist, and the exemptions file are gone. What remains in code is behaviour a table tag cannot express: partitions, extra predicates and cascade handlers in RETENTION_OVERRIDES, each of which must name a model the schema declares purgeable. Sensitivity is answered from the tag first (getTableSensitivity in table-classification.ts, whose registry now holds only untagged models and shrinks as tags land), and the ERD mirror projects the declaration as governance.declared on every tagged model element. What still lives beside the schema is the field-level asset registry, which the coverage gate depends on.
• Adding a model? Write the tag before the migration. A model whose disposition you cannot name is a design question, not a default.

The steward watches growth, not only structure

Nightly growth pass (BI-592F1E7E, EP-A33A5C61 slice 5). After the structural drift detectors, the Data Architect steward samples every table from pg_class / pg_stat_user_tables into TableGrowthSample (heap, index, TOAST, live and dead tuples, rows in the last 24 h on the model’s declared timeAxis) and runs two detectors (apps/web/lib/ea/table-growth.ts):

• growth-without-disposition (error) — a table adding ≥ 1,000 rows/day or ≥ 5 MB/day whose declaration is missing, or whose disposition (reference, config, domain, projection) never removes rows. Carries bytes/day and a 12-month projection.
• payload-anatomy (warn) — TOAST is ≥ 50% of a relation of ≥ 50 MB: a JSON/text column is carrying blobs that belong in the content-addressed store.

Findings reconcile into EaConformanceIssue beside the structural ones (visible on /ea/data-model). A finding that stays open for three consecutive nightly samples is filed once as a fingerprinted backlog item with source data-growth (captureCorrectiveFailureBI), owned by AGT-BUILD-DA, and re-observed nightly until it clears. The sample table is itself tagged telemetry-bounded retention=90d, so the steward’s telemetry is governed by the mechanism it enforces.

Retention floors come from the obligations that bind this install

Derived, not hardcoded (BI-69C29492, EP-A33A5C61 slice 6). A retention window may only ever LENGTHEN: effective = max(base, industry floor, obligation floor, processing-activity floor). The obligation floor is the one derived from the compliance plane:

1. Obligation.retentionMinimumDays and Obligation.retentionFloorBuckets carry the duration a regulator states. Before this, durations existed only as prose in description (“wage records … for at least three years”) and no engine could read them.
2. apps/web/lib/operate/retention/obligation-floors.ts reads the minimums of the obligations whose regulation applies to this install, using the existing classifier (compliance-library.ts), which already scopes by archetype and jurisdiction. There is one answer to “what binds us”, not two.
3. The sweep max()es that against the base window. Each floor carries its citation, so a sweep can name the regulator behind a lengthened window.

• Failure is always the safe direction. An unreadable compliance plane, an unresolvable applicability, a null or zero minimum, an unknown bucket: each leaves the base window in place or considers more obligations, never fewer. A sweep never aborts because compliance is unreadable.
• An obligation that names no bucket binds every bucket. A records rule that does not distinguish audit trails from chat is making a claim about all of them; reading it narrowly would understate a regulator.
• The legacy table is still in the max(). INDUSTRY_RETENTION_FLOORS (four hardcoded industry rows) stays until parity is proven. obligation-floors.test.ts is that proof: it fails if any hardcoded row lacks an obligation at least as long. When it passes with no gaps the table can be deleted without any install’s window shortening — deleting it first would be the one thing floors must never do.
• Adding a floor? State it as an obligation on the regulation that already binds the archetype (packages/db/src/seed-retention-floor-obligations.ts); the seed invents no regulation and reports a floor it could not attach rather than pretending a regulator said something. The window then shows on /compliance/obligations/<id> as a Retention minimum tile — the duration and the categories it binds, worded by apps/web/lib/compliance/format-retention-minimum.ts (BI-4DD2F087). An obligation stating no minimum shows no tile, rather than an empty one. The obligations grid carries retentionMinimumDays as a sortable column so the obligations that actually lengthen a window can be found without opening each one.
• Name the regulations the floor is about, best first, in preferredRegulationIds. Archetype coverage alone is not a citation. Five active regulations name a public-sector archetype on a real install, and the original deterministic pick took the alphabetically first, so the public-records floor cited the Clean Water Act (BI-0C723E18). The duration was right and the citation was a compliance claim the platform must not make: a wrong regulator is worse than none. The alphabetical pick survives only as the fallback for an install that seeds none of the named regulations, because losing the floor to protect the citation would trade a weak claim for a short retention window. The seed logs every fallback attachment, so the weaker claim is visible rather than silent.

The screen must not describe a narrower rule than the sweep enforces. formatRetentionBuckets renders an unnamed bucket set as “all categories” for the same reason foldObligationFloors treats it as binding every bucket. If one of those two readings changes, both change.

Evidence payloads never live inline

The ceiling (BI-39AAE9B8, EP-A33A5C61 slice 2). A ledger row records that something happened and what it carried, by digest. Any string leaf above EVIDENCE_INLINE_CEILING_BYTES (64 KB, apps/web/lib/evidence/bounded-evidence-output.ts) leaves the JSON column:

• Evidence writers (recordLocalIntegrationResult and anything else that persists a console log or report body) call offloadEvidenceOutput: the bytes are written once to the content-addressed blob store (lib/documents/blob-storage.ts, tracked by a DocumentBlob row keyed on sha256) and the record keeps a head+tail excerpt — still a string, so readers that want the failing tail keep working — plus outputBlob: {sha256, storageKey, sizeBytes}.
• NUL in captured local-CI tails (BI-37D6E04E). Before offloading, the local integration recorder projects NUL-bearing content.execution.vitest.observations[].outputTail strings into bounded, visibly escaped previews. outputTailProjection records the encoding, NUL count and original blob reference; the blob retains the original bytes even when the tail is below the inline ceiling. This happens after fingerprinting the original request, so replay still binds to the unchanged request and gate verdict. The projection does not rewrite arbitrary signed or bound evidence. A provenance collision or failed blob write prevents recording rather than losing the original text.
• The tool-execution ledger (lib/governed-tool-audit.ts) applies boundLargeStrings to every parameter tree: an oversized leaf becomes {__dpfBounded, sha256, byteLength, head}. Two ledgers carrying the same log converge on one file and one row.
• Why it matters. Before the ceiling, one 16-day-old install held ~560 MB (32% of the database) of local-CI console text twice, inside ExternalEvidenceRecord.details and ToolExecution.parameters, growing ~6.5 GB/year per copy. Backfill for rows written before the ceiling: apps/web/scripts/offload-evidence-output.ts (dry-run by default, --apply to rewrite).
• Adding a new evidence-bearing writer? Route the body through offloadEvidenceOutput (or boundLargeStrings if the row is a pure ledger) before the insert. A JSON column is a place for structure and references, never for a log.

Retiring a model nothing reads

How a model leaves the schema (BI-911840CB). The prismaModelCount ratchet makes every new model owned debt, and a retirement is how that debt is paid back. Show that no live code path reads or writes the model (the Prisma accessor, its back-relation fields, and raw SQL against the table), then land in one PR:

• The migration handles any data state. It is a no-op when the table is absent, drops the table only when it is empty, and otherwise renames it to <Model>_retired_<item> with every row, its indexes renamed off the original names, and a table comment naming the item. It never deletes a row. 20261006200000_retire_voice_training_job_and_exam_voucher is the reference.
• The schema-regression guard gets one INTENTIONAL_MODEL_REMOVALS entry per model, plus an INTENTIONAL_FIELD_REMOVALS entry for each back-relation field on a surviving model (schema-regression-guard.mjs). Retiring a model does not excuse those fields.
• The shrink-only baselines drop the model’s entries: the legacy coverage baseline, model metadata, stewardship scope, closed-set strings and FK-index coverage. scripts/platform-substrate-baseline.json records the lower count.
• A data-impact manifest records the live row counts and what the migration does to existing rows.

Task-specific operating rules

Read these when this domain is touched. These statements are relocated from AGENTS.md; its invariant core still applies.

• Closed-set string fields are typed enums, never free-form strings. A new closed axis gets a Prisma enum + generated TypeScript union; widening one is a migration, not a string literal. → kernel principle
• Organization is the canonical platform identity model. Any feature needing org name, slug, logo, address or contact reads from it — never a parallel store. → kernel principle
• Compose from the shared micro-primitives — action results, JSON coercion, route constants. A page-local helper under a route segment must not become a second home for a shared concern. Schema audit before a large feature is the §1 check at data-model altitude. → kernel principle