AI-Assisted Database Design

Database Design Is Hard to Undo

Most code mistakes are cheap: a bad function gets refactored, a wrong variable gets renamed, a broken component gets rewritten. Database mistakes are different. A poorly normalized schema, a missing index, a column that should have been nullable — these live in production, in backups, in millions of rows that all need to be migrated. They compound.

AI doesn't eliminate that risk. But it changes where you catch the mistakes. Design conversations that used to happen with a senior engineer over a whiteboard can now happen with AI before you write the first CREATE TABLE. The model can suggest design flaws early, but its critique still needs to be checked against constraints and executable tests.

This guide covers the full lifecycle: initial schema design, normalization, indexing, migrations, query optimization, and evolving a database that's already in production.

Part 1: Schema Design

The best time to involve AI in database design is at the very beginning — before any tables exist. Describe your domain in plain language. Let AI draft the schema. Then critique it before you commit to anything.

Starting from a Description

You don't need to know your exact schema before talking to AI. Start with what the system needs to do:

Use this PostgreSQL 18 draft as a starting point. It enforces the one-level task hierarchy, but does not yet enforce organization membership or project visibility. The application must authorize every read and write; foreign keys alone do not provide tenant isolation.

-- Users (shared across organizations)
CREATE TABLE users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  avatar_url  TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Organizations
CREATE TABLE organizations (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  slug        TEXT NOT NULL UNIQUE,   -- URL-safe identifier
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Membership: one user can belong to many orgs, with a role per org
CREATE TABLE org_members (
  org_id      UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role        TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  joined_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (org_id, user_id)
);

-- Projects belong to one organization
CREATE TABLE projects (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id      UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  description TEXT,
  status      TEXT NOT NULL DEFAULT 'active'
                CHECK (status IN ('active', 'archived', 'completed')),
  due_date    DATE,
  created_by  UUID NOT NULL REFERENCES users(id),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Tasks and subtasks in one table (parent_id = NULL means top-level)
CREATE TABLE tasks (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id  UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  parent_id   UUID,  -- NULL = top-level task
  is_root     BOOLEAN GENERATED ALWAYS AS (parent_id IS NULL) STORED,
  parent_is_root BOOLEAN NOT NULL DEFAULT true CHECK (parent_is_root),
  title       TEXT NOT NULL,
  description TEXT,
  status      TEXT NOT NULL DEFAULT 'todo'
                CHECK (status IN ('todo', 'in-progress', 'done')),
  priority    TEXT NOT NULL DEFAULT 'medium'
                CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
  -- Text sorts alphabetically (high, low, medium, urgent); sort and index on this instead
  priority_rank SMALLINT GENERATED ALWAYS AS (
                CASE priority WHEN 'urgent' THEN 4 WHEN 'high' THEN 3
                              WHEN 'medium' THEN 2 ELSE 1 END) STORED,
  assignee_id UUID REFERENCES users(id) ON DELETE SET NULL,
  due_date    DATE,
  created_by  UUID NOT NULL REFERENCES users(id),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  -- A child can reference only a root in the same project.
  UNIQUE (id, project_id, is_root),
  FOREIGN KEY (parent_id, project_id, parent_is_root)
    REFERENCES tasks(id, project_id, is_root) ON DELETE CASCADE
);

-- The generated is_root value changes when parent_id changes.
-- Reparenting a root with children or moving it to another project is
-- rejected while those references exist. Plan such moves explicitly.

-- Comments on tasks
CREATE TABLE task_comments (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id     UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  author_id   UUID NOT NULL REFERENCES users(id),
  body        TEXT NOT NULL,           -- stored as markdown
  edited_at   TIMESTAMPTZ,             -- NULL means never edited
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Activity records; append-only permissions and event writes are application work
CREATE TABLE task_activity (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id     UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  actor_id    UUID NOT NULL REFERENCES users(id),
  action      TEXT NOT NULL,           -- e.g. 'status_changed', 'assigned', 'commented'
  payload     JSONB,                   -- old/new values or other context
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

The Critique Step

Never accept the first draft of a schema. Ask AI to attack it before you build anything on top of it:

Common issues a good critique surfaces:

• Nesting rules can drift if they're only enforced in app code. The composite foreign key above requires every child to reference a root in the same project, including when either row changes. The extra unique index supports this integrity rule. Test deeper nesting, self-parenting, project changes, and concurrent reparenting before deployment.
• No policy for deleting users. task_comments.author_id, task_activity.actor_id and the created_by columns reference users without ON DELETE, so deleting anyone who has written a comment or created a task fails. Decide a retention and deletion policy up front. Deactivation preserves the row and its references; it does not erase personal data. Whether anonymization or deletion is appropriate requires a separate policy review.
• JSONB payload has no schema. task_activity.payload is flexible now but will be queried in specific ways later. Document the expected shape per action type, or model common fields explicitly.
• No project-level permissions. The schema does not define who can see a project; visibility depends on the authorization layer. If project-level visibility is ever needed (private projects within an org), the schema has no hook for it.
• Missing: task ordering. No position column for drag-and-drop ordering within a project. Adding this to millions of rows later is painful.

Asking for Design Alternatives

When a design decision is non-obvious, ask AI to present the tradeoffs:

This kind of explicit tradeoff question gets useful answers. AI will explain that the single-table approach is simpler to query for feeds but harder to enforce payload shape; the multi-table approach is strongly typed but requires a UNION or application-level assembly to render a feed. For an activity feed that needs to display heterogeneous events in chronological order, the single table with documented payload shapes is usually the right call when its write and query tradeoffs fit your application.

Part 2: Normalization and Relationships

Normalization is the process of organizing a schema to reduce redundancy and ensure data integrity. Under-normalized schemas store the same data in multiple places, causing update anomalies. Over-normalized schemas split data across so many tables that simple queries require five joins.

AI is good at both diagnosing normalization issues and explaining the tradeoffs when you deliberately denormalize for performance.

Diagnosing Normalization Problems

CREATE TABLE orders (
  id           UUID PRIMARY KEY,
  customer_id  UUID NOT NULL,
  customer_email TEXT NOT NULL,      -- repeated from users table
  customer_name  TEXT NOT NULL,      -- repeated from users table
  product_id   UUID NOT NULL,
  product_name TEXT NOT NULL,        -- repeated from products table
  product_price DECIMAL(10,2) NOT NULL,
  quantity     INTEGER NOT NULL,
  total        DECIMAL(10,2) NOT NULL, -- computed: price * quantity
  status       TEXT NOT NULL
);

AI will identify three issues here: customer_email and customer_name should come from a join to users, not be stored redundantly; product_name is also redundant but with a nuance — in an orders context, you might want to snapshot the product name at order time in case the product is renamed later; and total is derived — compute it, or store it deliberately as an order-time snapshot together with discounts and tax. It will also distinguish between the cases where denormalization is a legitimate choice (historical snapshot of product price) vs. a maintenance problem (current customer name).

Modeling Many-to-Many Relationships

Many-to-many relationships are one of the most common sources of schema confusion. Ask AI to explain the pattern and when to add payload to the junction table:

This is the next revision of the draft. Run it after the initial schema in a disposable database. Dropping assignee_id is safe only for this empty exercise: an existing deployment needs a data migration before the old column is removed.

ALTER TABLE tasks DROP COLUMN assignee_id;

CREATE TABLE task_assignees (
  task_id     UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  assigned_by UUID NOT NULL REFERENCES users(id),   -- who made the assignment
  assigned_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (task_id, user_id)
);

-- Index for the other direction: find all tasks for a user
CREATE INDEX idx_task_assignees_user ON task_assignees(user_id);

The key insight AI surfaces: junction tables often need payload. Here, knowing who assigned whom and when is valuable for the activity feed. Without it, you'd need to reconstruct that from the activity log. Note that this junction table replaces tasks.assignee_id from the first draft — keep one source of truth for assignment. The queries and final TypeScript types below use task_assignees. Check that assignees and assigning users are authorized organization members before writing these rows.

Self-Referencing Relationships

Hierarchical data (categories, org charts, thread replies) is tricky to model. Ask AI to compare approaches for your specific depth requirements:

Compare write complexity and query plans for your actual depth and access patterns. The following separate catalog example assumes categories(id, name, parent_id) forms an acyclic tree and products has a category_id. Prevent cycles when categories change; UNION ALL does not stop a cycle for you.

WITH RECURSIVE category_tree AS (
  -- Base case: the category we're querying
  SELECT id, name, parent_id
  FROM categories
  WHERE id = $1

  UNION ALL

  -- Recursive case: children of each node
  SELECT c.id, c.name, c.parent_id
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT p.*
FROM products p
WHERE p.category_id IN (SELECT id FROM category_tree)
ORDER BY p.name;

Part 3: Indexes and Query Performance

A schema that's correct but slow is still a problem. Indexes are the primary tool — but the wrong indexes are nearly as bad as none, because they add write overhead and false confidence. AI is useful both for deciding what to index and for diagnosing slow queries.

Getting Indexing Recommendations

-- Query 1: tasks in a project, ordered by priority and created_at
-- Composite index covering the filter (project_id) and sort columns
-- Uses priority_rank: indexing the TEXT column would sort high, low, medium, urgent
CREATE INDEX idx_tasks_project_priority_created
  ON tasks(project_id, priority_rank DESC, created_at DESC);

-- Query 2: tasks assigned to a user
-- Start with idx_task_assignees_user; verify the plan and selected columns.
-- The join back to tasks uses the primary key.

-- Query 3: overdue tasks in an organization
-- Needs org context via project, then filter on due_date and status.
-- Partial index — only indexes rows where status != 'done', reducing index size.
CREATE INDEX idx_tasks_overdue
  ON tasks(project_id, due_date)
  WHERE status != 'done';

-- To scope by org, join through projects, which needs its own index:
CREATE INDEX idx_projects_org ON projects(org_id);

-- Query 4: full-text search on task titles
CREATE INDEX idx_tasks_title_fts
  ON tasks USING gin(to_tsvector('english', title));

-- Query 5: count by status for one project.
-- A (project_id, status) index lets PostgreSQL answer this with an index-only scan
-- when the visibility map is current:
CREATE INDEX idx_tasks_project_status
  ON tasks(project_id, status);

Diagnosing a Slow Query

Paste the query, the EXPLAIN ANALYZE output, and ask AI to interpret it:

SELECT t.*, u.name as assignee_name
FROM tasks t
LEFT JOIN task_assignees ta ON ta.task_id = t.id
LEFT JOIN users u ON u.id = ta.user_id
WHERE t.project_id = $1
  AND t.status != 'done'
ORDER BY t.priority_rank DESC, t.created_at DESC
LIMIT 50;

Limit  (cost=12847.23..12847.35 rows=50) (actual time=798.432..798.445 rows=50)
  -> Sort  (cost=12847.23..12872.23 rows=10000) (actual time=798.429..798.435 rows=50)
      Sort Key: t.priority_rank DESC, t.created_at DESC
      Sort Method: top-N heapsort  Memory: 32kB
      -> Hash Left Join  (cost=5234.00..12597.23 rows=10000) (actual time=234.2..791.1 rows=10000)
            Hash Cond: (ta.task_id = t.id)
            -> Seq Scan on tasks t  (cost=0.00..7123.45 rows=10000) ...
               Filter: ((status <> 'done') AND (project_id = $1))

The Seq Scan on tasks node shows a sequential scan in this fictional plan; it does not prove that a suitable index is absent. Compare estimated and actual rows and run ANALYZE when statistics need refreshing. A partial index on (project_id, priority_rank DESC, created_at DESC) WHERE status <> 'done' is one candidate for active tasks. The join can produce several rows per task, so LIMIT 50 here limits joined rows, not distinct tasks. If you need 50 tasks, select those tasks first, then join or aggregate their assignees. Compare EXPLAIN (ANALYZE, BUFFERS) before and after; an index does not guarantee the final plan avoids sorting.

Writing Optimized Queries

WITH task_stats AS (
  SELECT
    COUNT(*)                                          AS total,
    COUNT(*) FILTER (WHERE status = 'todo')           AS todo_count,
    COUNT(*) FILTER (WHERE status = 'in-progress')    AS in_progress_count,
    COUNT(*) FILTER (WHERE status = 'done')           AS done_count,
    COUNT(*) FILTER (WHERE due_date < $2 AND status != 'done') AS overdue_count,
    COUNT(*) FILTER (
      WHERE NOT EXISTS (SELECT 1 FROM task_assignees ta WHERE ta.task_id = tasks.id)
    )                                                 AS unassigned_count
  FROM tasks
  WHERE project_id = $1
),
recent_tasks AS (
  SELECT
    t.id,
    t.title,
    t.status,
    t.priority,
    t.updated_at,
    COALESCE(array_agg(u.name ORDER BY ta.assigned_at, ta.user_id)
      FILTER (WHERE u.id IS NOT NULL), ARRAY[]::text[]) AS assignees
  FROM tasks t
  LEFT JOIN task_assignees ta ON ta.task_id = t.id
  LEFT JOIN users u ON u.id = ta.user_id
  WHERE t.project_id = $1
  GROUP BY t.id, t.title, t.status, t.priority, t.updated_at
  ORDER BY t.updated_at DESC, t.id
  LIMIT 5
)
SELECT
  row_to_json(task_stats) AS stats,
  COALESCE((SELECT json_agg(recent_tasks ORDER BY updated_at DESC, id)
            FROM recent_tasks), '[]'::json) AS recent
FROM task_stats;

Part 4: Migrations

A migration is a versioned, repeatable change to a database schema. Choose transaction boundaries deliberately, preserve compatibility during rollout, and test both directions before production. The labels DDL below can run in a transaction; other operations, such as concurrent index creation, have different transaction restrictions. AI accelerates writing them — but you still need to review them carefully.

Writing a Migration from a Description

-- UP

CREATE TABLE labels (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id      UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  color       TEXT NOT NULL CHECK (color ~ '^#[0-9a-fA-F]{6}$'),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (org_id, name)
);

-- Application authorization must reject labels from a different organization.
CREATE TABLE task_labels (
  task_id     UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  label_id    UUID NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
  PRIMARY KEY (task_id, label_id)
);

CREATE INDEX idx_task_labels_label ON task_labels(label_id);
CREATE INDEX idx_labels_org ON labels(org_id);

-- DOWN

DROP TABLE IF EXISTS task_labels;
DROP TABLE IF EXISTS labels;

Safe Migrations on Live Databases

Some migrations look harmless but lock tables in production. Ask AI to flag danger before you run anything:

A good answer starts with the actual problem: ALTER TABLE tasks ADD COLUMN position INTEGER NOT NULL fails outright on a table that already has rows, because there is no value for them. Adding it with a constant DEFAULT is fast — metadata-only since PostgreSQL 11 — but gives every existing task the same position. For a value that must be computed per row, the safe pattern is:

-- Step 1: Add the column as nullable (metadata-only, but ACCESS EXCLUSIVE).
-- Bound lock waits in your migration runner; no rewrite does not mean no lock.
-- Then deploy code that sets position on every new task before you backfill.
-- This assigns initial values only; it does not enforce unique positions.
-- Coordinate concurrent reordering with the backfill.
ALTER TABLE tasks ADD COLUMN position INTEGER;

-- Step 2: Backfill in short batches — one project per transaction, run from a script.
-- A project is not a bounded batch if it contains millions of tasks.
-- Bound and checkpoint batches; competing writers can still wait on row locks.
UPDATE tasks SET position = numbered.rn
FROM (
  SELECT id, ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn
  FROM tasks
  WHERE project_id = $1
) AS numbered
WHERE tasks.id = numbered.id
  AND tasks.position IS NULL;

-- Step 3: Once backfill is complete and verified, add the NOT NULL constraint
-- A validated CHECK lets SET NOT NULL skip its full-table scan (PostgreSQL 12+):
ALTER TABLE tasks ADD CONSTRAINT tasks_position_not_null
  CHECK (position IS NOT NULL) NOT VALID;

-- VALIDATE scans the table but takes only a SHARE UPDATE EXCLUSIVE lock,
-- so reads and writes continue:
ALTER TABLE tasks VALIDATE CONSTRAINT tasks_position_not_null;

-- Finally convert to a real NOT NULL (ACCESS EXCLUSIVE; scan skipped).
-- PostgreSQL 18 also accepts NOT VALID on a not-null constraint directly.
ALTER TABLE tasks ALTER COLUMN position SET NOT NULL;
ALTER TABLE tasks DROP CONSTRAINT tasks_position_not_null;

Renaming Columns Safely

AI explains the expand-contract pattern: add the new column (body), deploy code that writes to both, backfill old rows, deploy code that reads from the new column only, then drop the old column. Keep dual writes consistent while old and new application versions coexist. Make the backfill resumable and avoid overwriting a newer value with an older one. Switch reads only after checking the data, then remove old writers and dependencies before dropping the old column. The pattern reduces rollout risk; it does not guarantee zero downtime or eliminate DDL locks.

Part 5: Evolving a Schema That's Already in Production

The hardest database work isn't the initial design — it's changing a schema after real data is in it. AI helps most here by acting as a review partner who knows the failure modes.

Impact Analysis Before Making Changes

Before any schema change, ask AI what will break:

A thorough AI response covers: every foreign key that references users.id (those stay as-is); every query that selects from users (those need to be updated or use a view); API endpoints that return user data (need updating); the migration itself; and a recommended view to ease the transition:

-- Create a view that looks like the old users table
-- Old callers must explicitly use this view; it does not redirect users queries
CREATE VIEW users_legacy AS
SELECT
  u.id,
  u.email,
  u.created_at,
  p.name,
  p.avatar_url
FROM users u
LEFT JOIN user_profiles p ON p.user_id = u.id;

Modeling Schema Versions in TypeScript

When using a query builder like Kysely, ask AI to generate the TypeScript types that match your schema:

import type { ColumnType, Generated, Insertable, Selectable, Updateable } from 'kysely';

// PostgreSQL 18, Kysely 0.28.8, pg 8.16.3, TypeScript 7.0.2, Node 24.
// Assumes pg returns timestamptz and date as Date / Date | null.
// Check your driver's parsers; these declarations do not convert runtime values.
type CreatedAt = ColumnType<Date, never, never>;
type NullableDate = ColumnType<Date | null, string | null, string | null>;

export interface Database {
  users: UsersTable;
  organizations: OrganizationsTable;
  org_members: OrgMembersTable;
  projects: ProjectsTable;
  tasks: TasksTable;
  task_assignees: TaskAssigneesTable;
  task_comments: TaskCommentsTable;
  task_activity: TaskActivityTable;
  labels: LabelsTable;
  task_labels: TaskLabelsTable;
}

interface UsersTable {
  id: Generated<string>;
  email: string;
  name: string;
  avatar_url: string | null;
  created_at: CreatedAt;
}
interface OrganizationsTable {
  id: Generated<string>;
  name: string;
  slug: string;
  created_at: CreatedAt;
}
interface OrgMembersTable {
  org_id: string;
  user_id: string;
  role: 'owner' | 'admin' | 'member';
  joined_at: CreatedAt;
}
interface ProjectsTable {
  id: Generated<string>;
  org_id: string;
  name: string;
  description: string | null;
  status: Generated<'active' | 'archived' | 'completed'>;
  due_date: NullableDate;
  created_by: string;
  created_at: CreatedAt;
}
interface TasksTable {
  id: Generated<string>;
  project_id: string;
  parent_id: string | null;
  is_root: ColumnType<boolean, never, never>;
  parent_is_root: ColumnType<true, never, never>;
  title: string;
  description: string | null;
  status: Generated<'todo' | 'in-progress' | 'done'>;
  priority: Generated<'low' | 'medium' | 'high' | 'urgent'>;
  priority_rank: ColumnType<number, never, never>;
  due_date: NullableDate;
  created_by: string;
  created_at: CreatedAt;
  // DEFAULT now() applies on INSERT, not on UPDATE. The app sets this field.
  updated_at: ColumnType<Date, never, string>;
}
interface TaskAssigneesTable {
  task_id: string;
  user_id: string;
  assigned_by: string;
  assigned_at: CreatedAt;
}
interface TaskCommentsTable {
  id: Generated<string>;
  task_id: string;
  author_id: string;
  body: string;
  edited_at: ColumnType<Date | null, string | null, string | null>;
  created_at: CreatedAt;
}
interface TaskActivityTable {
  id: Generated<string>;
  task_id: string;
  actor_id: string;
  action: string;
  payload: ColumnType<unknown, string | null, string | null>;
  created_at: CreatedAt;
}
interface LabelsTable {
  id: Generated<string>;
  org_id: string;
  name: string;
  color: string;
  created_at: CreatedAt;
}
interface TaskLabelsTable {
  task_id: string;
  label_id: string;
}

export type User = Selectable<UsersTable>;
export type NewUser = Insertable<UsersTable>;
export type UserUpdate = Updateable<UsersTable>;
export type Task = Selectable<TasksTable>;
export type NewTask = Insertable<TasksTable>;
export type TaskUpdate = Updateable<TasksTable>;

Part 6: Prompt Patterns That Work

Schema prompts need constraints, access patterns, and migration context. The following patterns make a proposed design easier to review; they do not establish its correctness.

Always Include Your Constraints

Generic schema prompts produce generic schemas. Specific constraints produce specific, useful designs:

// Include as many as apply:
- Database: PostgreSQL 18 / MySQL 8.4 / SQLite
- Scale expectation: 100 users / 1M rows / 10M rows
- Read/write ratio: mostly reads / mostly writes / balanced
- Consistency requirements: eventual OK / strong required
- Specific query patterns: [list your most common queries]
- Existing tables it must integrate with: [paste relevant schema]
- Non-negotiable constraints: multi-tenancy, soft deletes, audit log, etc.

Ask for Rationale, Not Just SQL

"Write the schema" produces SQL you have to trust. "Write the schema and explain each design decision" produces SQL you can evaluate:

Use AI to Review Your Own Schema

If you've already designed a schema, AI is useful as a second opinion:

The "what will I regret" framing tends to produce forward-looking critique rather than surface-level nitpicks. AI will think about growth, changing requirements, and schema evolution — not just whether the SQL is syntactically correct.

Iterating on a Schema in One Conversation

Keep the schema in the conversation and revise it incrementally. Paste the current state, ask for a specific change, paste the updated version back:

This iterative pattern — revise, paste back, extend — keeps AI grounded in what actually exists rather than reimagining the schema from scratch on every turn.

Sources and review scope

PostgreSQL 18 documentation: constraints and foreign keys, generated columns, ALTER TABLE, partial indexes, and EXPLAIN. The Kysely types follow ColumnType's select/insert/update contract. Sources checked Oct 29, 2026. The SQL and final type declarations were checked in an isolated PostgreSQL 18.6 / Kysely 0.28.8 lab, including invalid hierarchy changes and an empty dashboard. Prompt responses and timings are illustrative. No AI model, production rollout, large-table benchmark, or application authorization system was tested. Before using the fragments, adapt access control, migration tooling, driver parsers, and transaction boundaries to your application.

Related Guides