AI Database Schema Design

A database schema is the part of the system that is hardest to change after the fact and the part that most directly determines whether the application will be fast or slow, correct or buggy, easy to reason about or impossible. The schema is also the part most often designed in five minutes by someone who needed a feature shipped, and the resulting decisions sit in the codebase for years. Indexes that should exist do not. Constraints that should be enforced at the database level live in application code that gets bypassed by every batch script and migration. Tables that should be normalized are denormalized for no reason, and tables that should be denormalized for read performance are fully normalized in the name of purity. The cost of a bad schema is paid every day by the queries that run against it, and the cost compounds with every feature that builds on top of it.

Designing a schema correctly is an engineering discipline that requires more context than most prompts contain. It requires knowing the expected query patterns (so the indexes match the reads), the expected write volume (so the indexes do not slow the writes too much), the data integrity rules that must hold (so the constraints encode them), and the read-versus-write balance on each table (so the denormalization decisions are justified). Mainstream AI coding tools generate schemas from the prompt without any of that context, which produces schemas that look correct in isolation and turn out to be wrong against real workloads. The /flux-schema skill is built for the actual discipline: it asks for the query patterns, designs the indexes against them, encodes the constraints at the database level, and explains the reasoning behind every decision so the team can review it.

Why generalist AI gets schemas wrong

Ask Cursor or ChatGPT for a schema for a CRM. You get tables for accounts, contacts, deals, activities. The columns look reasonable. The foreign keys are in the right places. There are no indexes. There are no constraints beyond the foreign keys. The numeric types are picked by guessing (amount DECIMAL(10,2) for currency, which is wrong because it does not handle every currency, and INTEGER cents is the right answer). The text fields are unbounded TEXT columns that will accept a megabyte from a malformed import. The schema is technically valid SQL. It is also a starter kit for a hundred future bugs, because the actual queries the application will run against this schema have not been thought through.

The deeper problem is that a schema is not just data. It is the contract between the application and the storage layer, and the contract has to be calibrated to the workload. A high-write table with a hot timestamp index will slow writes to a crawl if the index is on the wrong column. A read-heavy table without the right composite index will run sequential scans on every query. A many-to-many relationship modeled with a junction table needs an index on each direction or one side will be unusable. None of this is information the prompt provides, and a generalist tool cannot infer it without asking. The schemas that ship from generalist tools are the ones that work for the dummy data in the example and fail at production scale, and the failure shows up in the query logs months later when nobody remembers who designed the table.

What schema design actually requires

A correct schema balances several competing concerns. Normalization keeps the data clean: each fact lives in one place, updates touch one row, the schema describes the domain rather than a snapshot of one screen's needs. Denormalization optimizes the hot read paths: the dashboard query that runs ten thousand times an hour cannot afford a six-table join, so the data needed for that query is materialized in a form the query can read in one or two index seeks. Indexes match the actual queries: the WHERE clauses, the ORDER BY clauses, the foreign key joins. Constraints encode the data integrity rules at the database level so the application cannot accidentally violate them: NOT NULL on required fields, CHECK constraints on enums, UNIQUE on natural keys, foreign keys with the right ON DELETE behavior.

The decisions are coupled. Adding an index speeds the matching read and slows every write to that table; the right answer depends on the read-write ratio. Denormalizing a column speeds the join-free read and creates a consistency problem that has to be solved with a trigger, a materialized view, or application-level care; the right answer depends on how often the source data changes and how stale the denormalized copy is allowed to be. Constraint design is similarly coupled: a strict constraint produces clean data and rejects writes that violate it, which is great for integrity and bad if the constraint surprises the application. The schema has to be designed with all of these tensions in mind, which is why the right tool has to ask questions before writing tables.

How /flux-schema works

Step one: ask about the query patterns

Before designing any tables, /flux-schema asks for the expected query patterns. Which screens will read this data, and which fields do they filter and sort by. Which background jobs will write this data, and at what volume. Which columns are looked up by primary key only, and which are looked up by other fields. The questions are surfaced in writing so the team can answer them deliberately rather than guess at them in the model's head. The query pattern list becomes the input to the index design and the denormalization decisions; without it, the schema is being designed for a workload that has not been described.

Step two: produce the schema with reasoning

The schema output is a set of CREATE TABLE statements with indexes, constraints, and a paragraph of reasoning per table that explains the choices. "Why is email a CITEXT and not TEXT" gets a sentence answer ("case-insensitive lookup is the dominant query pattern, CITEXT avoids LOWER() in every WHERE"). "Why is there a composite index on (account_id, created_at DESC)" gets a sentence answer ("the activity feed query filters by account and orders by recency; this index serves both"). The reasoning is not decoration; it is the part of the schema that survives the next person's review. A schema with reasons can be challenged on its assumptions; a schema without reasons can only be accepted on faith.

Step three: constraints at the database level

Data integrity rules go in the database, not just in the application. NOT NULL on every required field. CHECK constraints on enums and ranges (status IN ('draft', 'open', 'paid'), amount_cents > 0). UNIQUE on natural keys (the email, the slug, the customer-and-period composite). Foreign keys with the right ON DELETE behavior (CASCADE for owned children, RESTRICT for shared references, SET NULL for soft links). The discipline is to encode every rule the application assumes, so a misbehaving migration script or a buggy admin tool cannot violate them. The schema is the last line of defense for data integrity, and /flux-schema treats it as such.

Step four: indexes for the actual reads

Indexes are designed against the query patterns surfaced in step one. Each index has a justification: which query pattern it serves, what its size and write cost is approximately, and whether it overlaps with other indexes (so duplicate or covered indexes are flagged). The skill is conservative about adding indexes (they have a real write cost) and aggressive about removing redundant ones (a smaller index set is faster to maintain). For high-volume tables, the index design includes notes on partial indexes, expression indexes, and covering indexes where they pay off.

Tonone's /flux-schema skill designs database schemas calibrated to actual query patterns, with indexes that match the reads, constraints that encode integrity rules, and reasoning per decision.

When to use /flux-schema, and when not to

/flux-schema is the right call when designing a new data model from scratch. The signal is when the brief contains "new feature," "new service," "new system," or "redesign the data model." The skill is also the right call for a structured review of an existing schema that has grown without direction; running /flux-schema against the existing tables produces a critique with index recommendations and constraint additions, often without any structural change to the tables themselves.

Skip the skill for trivial single-table additions where the design is obvious (adding an audit_log table, adding a notes field). For evolving an existing schema under live traffic, /flux-migrate is the right call; it produces the zero-downtime migration plan to apply the change. For diagnosing slow queries against an existing schema, /flux-query is the right call; it analyzes the execution plan and proposes index or query rewrites without redesigning the schema.

A worked example: schema for a billing system

Suppose the brief is: design the schema for a billing system that handles customers, subscriptions, invoices, and payments. Run /flux-schema and the output starts with the questions, then the schema with reasoning.

-- Query patterns surfaced and resolved:
-- 1. List a customer's invoices, paginated by created_at DESC
-- 2. Look up an invoice by ID for the customer dashboard
-- 3. Sum unpaid invoices per customer for the dunning job
-- 4. Find subscriptions due to renew in the next 24 hours
-- 5. Report on revenue per period (monthly close)

CREATE TABLE customers (
  id          TEXT PRIMARY KEY,
  email       CITEXT NOT NULL,
  name        TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT customers_email_uq UNIQUE (email)
);
-- Reasoning: CITEXT for case-insensitive email lookup. UNIQUE on email
-- because the application treats it as a natural key.

CREATE TABLE subscriptions (
  id              TEXT PRIMARY KEY,
  customer_id     TEXT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  status          TEXT NOT NULL,
  current_period_start  TIMESTAMPTZ NOT NULL,
  current_period_end    TIMESTAMPTZ NOT NULL,
  CONSTRAINT subs_status_chk CHECK (status IN
    ('trialing', 'active', 'past_due', 'canceled', 'incomplete')),
  CONSTRAINT subs_period_chk CHECK (current_period_end > current_period_start)
);
CREATE INDEX subs_renewal_idx
  ON subscriptions (current_period_end)
  WHERE status IN ('active', 'trialing');
-- Reasoning: ON DELETE RESTRICT so customers cannot be deleted while
-- they have subscriptions. Partial index on (current_period_end)
-- WHERE active; this serves the renewal job (query 4) without
-- indexing canceled rows that the job never reads.

CREATE TABLE invoices (
  id              TEXT PRIMARY KEY,
  customer_id     TEXT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  subscription_id TEXT REFERENCES subscriptions(id) ON DELETE SET NULL,
  amount_due_cents BIGINT NOT NULL,
  currency        TEXT NOT NULL,
  status          TEXT NOT NULL,
  paid_at         TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT inv_status_chk CHECK (status IN
    ('draft', 'open', 'paid', 'void')),
  CONSTRAINT inv_amount_chk CHECK (amount_due_cents >= 0),
  CONSTRAINT inv_paid_consistency CHECK
    ((status = 'paid') = (paid_at IS NOT NULL))
);
CREATE INDEX inv_customer_recent_idx
  ON invoices (customer_id, created_at DESC);
CREATE INDEX inv_unpaid_idx
  ON invoices (customer_id)
  WHERE status IN ('open', 'past_due');
-- Reasoning: BIGINT cents (not DECIMAL) because we never want to
-- negotiate floating-point in money math. Composite index on
-- (customer_id, created_at DESC) serves query 1 (list customer's
-- invoices, recency-ordered). Partial index on customer_id WHERE
-- unpaid serves query 3 (dunning job). The CHECK on paid_at and
-- status keeps them consistent at the database level.

Five tables (the rest omitted for the example), each with constraints and indexes justified by a specific query pattern. The reasoning is part of the output, not buried in a separate document. When the next engineer adds a feature that needs a new query, they read the existing reasoning, identify whether their query is covered by an existing index, and either reuse it or add a new one with its own reasoning. That is how a schema stays coherent over years.

Related skills

/flux-schema produces the initial schema design. Once it is in production and needs to evolve, /flux-migrate writes the zero-downtime migration plan. For diagnosing slow queries on an existing schema, /flux-query is the right call. For a structured read of an unfamiliar database, /flux-recon produces the inventory.

Install

/flux-schema ships with the Flux agent in the Tonone for Claude Code package. Install Tonone, invoke /flux-schema from any Claude Code session, and the skill produces a query-pattern-aware schema with reasoning per decision.

Schemas are the slowest layer of the stack to change and the most consequential to get right. The skill is built so the design discipline is the default, and the reasoning is captured at design time when it is cheapest.