Designing DB Schemas

WSO2 API Platform — Database Schema Design

This skill governs all schema work for the WSO2 API Platform: designing new tables, adding to existing ones, and reviewing DDL changes. It is not a post-hoc review tool — it is the process to follow when writing DDL.

The rules live in references/api-platform-db-schema-rules.md (next to this skill). That file is the source of truth for every rule; this file is the workflow.

Usage

/designing-db-schemas [table-name | path-to-schema-file]

• No argument — review all in-scope schema files.
• Table name — apply the relevant workflow for that table.
• Schema file path — review that file only.

Schema file scope

Discover schema files with:

find . -name "*.sql" -not -path "*/node_modules/*" -not -path "*/target/*" | sort

Do not use find . -name "schema*.sql" — it matches 10 of the 19 schema files and misses every gateway-controller and event-gateway file.

Type exemption — gateway-controller and event-gateway schemas are owned by separate teams who manage their own type choices. Apply all structural, constraint, audit, index, alignment, and idempotency rules (R0–R2, R4–R10) as normal, but do not raise R3 findings (column types, JSONB, BOOLEAN, TIMESTAMPTZ, VARCHAR widths) against those files.

Two copies — gateway/gateway-controller keeps the same schema under both pkg/storage/ and resources/. Grep repo-wide before editing: grep -rln "<table_name>" --include="*.sql" .

Workflows

Workflow A — Making a schema change

Step A0 — Admissibility gate (R0)

These are GA products. Decide whether the change is allowed before touching a file.

Confirm the table has actually shipped before applying the freeze — one added earlier in the same unreleased branch is still malleable:

git log --oneline -1 -S"CREATE TABLE IF NOT EXISTS <table>" -- '*.sql'

When blocked, don't silently narrow the task. Say which part is blocked and why, deliver the allowed remainder, and offer the migration-plan route as separate, approved work.

Step A1 — Read the schemas first

Locate every schema file with the glob above and read each in full before drafting anything. Apply R3 type rules to all files except gateway-controller and event-gateway.

Step A2 — Open the rules reference

Read references/api-platform-db-schema-rules.md. The rules you need depend on the change:

Step A3 — Self-review checklist

[ ] R0  Change is additive — no retype/rename/PK-FK change/NOT NULL/UNIQUE on a shipped table
[ ] R0  Column added to an existing table: per-dialect ALTER TABLE written for already-provisioned
        databases (new tables/indexes need none — their guarded CREATE covers both cases)
[ ] R1  Entity tables: uuid VARCHAR(40) PRIMARY KEY
[ ] R1  Junction/mapping tables: composite PRIMARY KEY — not UNIQUE-only, not surrogate UUID
[ ] R1  Non-leading FK columns of a composite PK have their own indexes
[ ] R1  Named resource tables carry handle + name + version (all NOT NULL)
[ ] R1  handle VARCHAR(40) slug ≠ name VARCHAR(255) display string; no UNIQUE on name
[ ] R2  organization_uuid FK present; UNIQUE constraints include it (if org-scoped)
[ ] R3  No bare TEXT in Postgres — SQLite TEXT / SQL Server NVARCHAR(MAX) are intentional (R8)
[ ] R3  Large/variable payloads use BYTEA/BLOB/VARBINARY(MAX) — not wide VARCHAR
[ ] R3  JSONB only when a repository query reads inside it with JSON operators; scan target checked
        against the driver in use (pgx v5 also takes *string/*[]byte — see R3-JSONB-SCAN-COMPAT)
[ ] R3  Boolean flags: SMALLINT (Postgres/SQL Server) or INTEGER (SQLite), 0/1 — no BOOLEAN
[ ] R3  VARCHAR widths match R3-VARCHAR-SIZES; nothing above VARCHAR(1023) for plain storage
[ ] R3  Indexed/UNIQUE columns ≤ VARCHAR(255); hashes are VARCHAR(255)
[ ] R3  Timestamps: TIMESTAMPTZ (Postgres) / DATETIME (SQLite) / DATETIME2(7) (SQL Server), UTC
[ ] R4  No CHECK constraints for enum/status values — validated in the service layer instead
[ ] R4  Required columns NOT NULL, with a DEFAULT where one is sensible
[ ] R4  No plaintext credential/token column; hashed or vault-referenced, named accordingly
[ ] R4  Every FK has an explicit ON DELETE clause
[ ] R5  User-initiated table → all four audit columns; system-managed → created_by/updated_by ABSENT
[ ] R5  Every domain entity table has data_version VARCHAR(20) NOT NULL DEFAULT '1.0'
[ ] R6  FK columns, organization_uuid, and filtered status columns have indexes — except where the
        column is already the leftmost part of the PK or a covering UNIQUE constraint
[ ] R7  Go model/repository/DTO updated in the same commit; named columns, no SELECT *
[ ] R8  Change applied to every dialect file (or divergence is intentional and documented)
[ ] R9  All DDL is idempotent (IF NOT EXISTS / OBJECT_ID / sys.indexes guards)
[ ] R10 All identifiers lowercase snake_case; pure mapping tables have a _mappings suffix

Step A4 — Write the DDL

Use the engine-specific guards (R9) — CREATE TABLE IF NOT EXISTS is not valid T-SQL:

-- PostgreSQL / SQLite
CREATE TABLE IF NOT EXISTS <table> (...);
CREATE INDEX IF NOT EXISTS idx_... ON <table>(...);

-- SQL Server
IF OBJECT_ID(N'dbo.<table>', N'U') IS NULL
CREATE TABLE dbo.<table> (...);

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'idx_...' AND object_id = OBJECT_ID(N'dbo.<table>'))
CREATE INDEX idx_... ON dbo.<table>(...);

Keep CREATE INDEX statements in a dedicated block after all CREATE TABLE statements. Start from the quick-reference templates at the end of the rules file.

Step A5 — Apply to all schema files, then ship the upgrade path

Apply to every in-scope dialect file, same column order, same position. Only the R8 divergences may differ.

Then, if the change adds a column to an existing table, write the per-dialect ALTER TABLE (R0-UPGRADE-PATH) — CREATE TABLE IF NOT EXISTS does nothing to a database that already exists, so a column added to a CREATE TABLE body reaches fresh installs only. A new table or a new index needs no ALTER: its guarded CREATE ... IF NOT EXISTS / OBJECT_ID / sys.indexes form (R9, Step A4) already applies to fresh and already-provisioned databases alike.

Step A6 — Verify on more than one dialect

cd <component> && go build ./... && go test ./internal/repository/... ./internal/database/...

Then: start from an empty SQLite database; start from an empty server dialect (Postgres or SQL Server); and apply the ALTER to a pre-existing database from the previous release. Report which dialects were actually exercised and which were only edited.

Workflow B — Reviewing existing DDL (PR / audit)

Step B1 — Locate and read all schema files

Use the glob above. Note which files are gateway-controller or event-gateway — those skip R3.

Step B2 — Open the rules reference

Read references/api-platform-db-schema-rules.md. Evaluate every rule group (R0–R10) in order.

Step B3 — Record findings

Two adjustments this repo requires:

• A shipped table's violation is LEGACY-ACCEPTED, not HIGH. Record it in the deviations table of .claude/rules/db-schema-changes.md so later reviews stop re-reporting it. Do not propose remediation DDL.
• Blanket-missing findings collapse. When a rule is violated uniformly across many tables, record one representative finding naming the pattern with a couple of examples — not one per table.

Findings on new tables/columns in the diff are live and use the normal severities.

Step B4 — Cross-check multi-engine alignment

Verify all dialect files are structurally in sync (R8). Intentional type-level divergences are not findings. To compare one table across dialects:

for f in platform-api/internal/database/schema*.sql; do
  echo "--- $f"
  awk '/CREATE TABLE.*<table>/,/\);/' "$f" | grep -oE '^\s+[a-z_]+' | tr -d ' '
done

Step B5 — Write findings to JSON

Run from this skill's directory, with an absolute --out so the report lands in the project:

node scripts/generate-schema-report.js \
  --findings '<findings-json-array>' \
  --schema   '<path-to-schema-file>' \
  --out      "$(git rev-parse --show-toplevel)/schema-reports/schema-review.json"

Output shape:

{
  "meta": { "schema": "<path>", "reviewedAt": "<ISO-8601>", "rules": ["R0","R1","R2","R3","R4","R5","R6","R7","R8","R9","R10"] },
  "summary": { "HIGH": 0, "MEDIUM": 0, "LOW": 0, "LEGACY-ACCEPTED": 0 },
  "findings": [
    { "id": "r3-001", "severity": "HIGH", "rule": "R3-NO-TEXT", "table": "<table>", "column": "<col>", "finding": "...", "fix": "..." }
  ]
}

Step B6 — Report summary

Produce a findings table sorted by severity. Include a "No issues" row for any rule group that passed cleanly — reviewers need to know what was checked.

Quick-reference templates

New entity table (Postgres and SQL Server), the standard column-type/width cheat sheet, and the junction/mapping table pattern (under R1-COMPOSITE-PK) all live in references/api-platform-db-schema-rules.md. Use them as the starting point in Step A4.