ring-designing-data-model

name: ring:designing-data-model description: "Designing the physical data model as a real stack-native schema (schema.sql with CREATE TABLE DDL, indexes, and constraints for Postgres/Go; schema.prisma for Prisma/TS; Postgres schema.sql as fallback) from the Gate 4 OpenAPI spec and TRD. Gate 5 of ring:planning-large-features; runs after ring:designing-api-contracts, before ring:pinning-dependency-versions. Use when the system stores persistent data. Skip for Small Track, no persistent data, or an unvalidated API contract."

Data Modeling — Producing the Stack-Native Schema

When to use

• OpenAPI spec passed Gate 4 validation (or Gate 4 SKIPPED for features with no API surface)
• System stores persistent data
• Large Track workflow (2+ day features)

Skip when

• Small Track workflow → skip to ring:writing-plans
• No persistent data → skip to Dependency Map
• OpenAPI spec not validated (and Gate 4 not SKIPPED) → complete Gate 4 first

Sequence

Runs before: ring:pinning-dependency-versions Runs after: ring:designing-api-contracts

The deliverable is a REAL, stack-native schema file — DDL that becomes migrations, not markdown tables. The DDL IS the deliverable: CREATE TABLE, indexes, and constraints are required, not forbidden.

Phase 0: Database Field Naming Strategy (MANDATORY)

See shared-patterns/standards-discovery.md for the complete workflow.

Step 1: Check for Gate 4 API standards

If docs/pre-dev/{feature}/api-standards-ref.md exists, auto-detect naming convention.

Step 2: Ask user

If api-standards-ref.md EXISTS: AskUserQuestion: "How should database fields be named?"

• "Convert to snake_case (Recommended)" — API: userId → DB: user_id
• "Keep same as API (camelCase)" — API: userId → DB: userId
• "Different standards — provide DB dictionary"
• "Define manually"

If api-standards-ref.md DOES NOT EXIST: AskUserQuestion: "How should database fields be named?"

• "Use snake_case (Recommended)" — standard for PostgreSQL/MySQL
• "Use camelCase" — standard for MongoDB/document DBs
• "Load from standards document"
• "Define manually"

Step 3: Generate db-standards-ref.md

Option: Convert to snake_case — apply automatic rules:

• userId → user_id, createdAt → created_at, isActive → is_active, phoneNumber → phone_number, userID → user_id

Option: Keep same — copy field names without modification.

Option: Load from doc — WebFetch or read, extract field definitions, save to db-standards-ref.md.

Phase 1: Stack Detection (MANDATORY)

Determine the schema format from the TopologyConfig (research.md frontmatter) plus repo manifests:

Postgres is the Lerian default. When in doubt, write schema.sql.

Mandatory Workflow

Schema Requirements

The schema file MUST be migration-ready:

• Every table/model: explicit PK (uuid default at Lerian), created_at/updated_at timestamps (UTC, timestamptz)
• Foreign keys with explicit ON DELETE behavior — no implicit cascade decisions
• Lifecycle states as CHECK constraints or native enums (ACTIVE, INACTIVE, ...)
• Indexes for every query pattern implied by the OpenAPI list/filter operations
• Soft-delete (deleted_at) only where the TRD requires retention
• Entity ownership documented as comment headers per table/model: -- owner: {service-name} (or /// owner: in Prisma). Multi-service writes to one table are a design smell — flag them.

ER diagram, if useful, goes in a comment header at the top of the schema file or in the TRD — there is no separate data-model.md appendix.

schema.sql sketch

-- owner: accounts-service
CREATE TABLE accounts (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name        varchar(255) NOT NULL,
    status      varchar(16) NOT NULL CHECK (status IN ('ACTIVE','INACTIVE','BLOCKED')),
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_accounts_status ON accounts (status);

Gate 5 Validation Checklist

Gate Result: ✅ PASS → Dependency Map | ⚠️ CONDITIONAL (fix naming/missing indexes) | ❌ FAIL (missing entities or non-parsing schema)

Topology-Aware Output

Related

• ring:designing-api-contracts — produces the OpenAPI spec the schema is derived from
• ring:pinning-dependency-versions — consumes the schema to pin database products and versions