AI Tools Compared

Database schema design is a task where AI provides substantial value — not by replacing judgment, but by generating a solid starting point, catching normalization problems, suggesting indices, and modeling constraints you might miss. Claude handles the structured reasoning required for relational schemas particularly well. This guide shows specific prompts and workflows.

Starting from Domain Requirements

The most effective approach is describing your domain in plain language, not trying to specify the schema yourself:

Prompt:

Design a PostgreSQL schema for a subscription billing system with these requirements:

Domain:
- Customers can have multiple subscriptions
- Subscriptions are to plans (monthly/annual, different tiers)
- Each billing cycle creates an invoice with line items
- Invoices can have prorations when plans change mid-cycle
- Payment attempts are tracked with status and error codes
- Customers can have multiple payment methods, one marked as default
- All monetary values in cents (integer), no floating point

Constraints:
- Need to query "all unpaid invoices for a customer" fast
- Need to query "all subscriptions due for renewal today" fast
- Schema should support plan changes without data loss
- Audit trail: who changed what and when (on subscriptions)

Output: CREATE TABLE statements with comments explaining decisions.

Claude’s Output (partial):

-- Customers: central entity, minimal data here
-- Payment methods and subscription details live in related tables
CREATE TABLE customers (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email       TEXT NOT NULL,
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT customers_email_unique UNIQUE (email)
);

-- Plans are immutable once created; price changes create new plan versions
-- This preserves historical pricing on existing subscriptions
CREATE TABLE plans (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name         TEXT NOT NULL,              -- 'Pro Monthly', 'Pro Annual'
    tier         TEXT NOT NULL,              -- 'starter', 'pro', 'enterprise'
    interval     TEXT NOT NULL               -- 'month', 'year'
                 CHECK (interval IN ('month', 'year')),
    amount_cents INTEGER NOT NULL CHECK (amount_cents > 0),
    active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE subscriptions (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id         UUID NOT NULL REFERENCES customers(id),
    plan_id             UUID NOT NULL REFERENCES plans(id),
    status              TEXT NOT NULL
                        CHECK (status IN ('active', 'past_due', 'cancelled', 'trialing')),
    current_period_start TIMESTAMPTZ NOT NULL,
    current_period_end   TIMESTAMPTZ NOT NULL,
    cancel_at_period_end BOOLEAN NOT NULL DEFAULT FALSE,
    trial_end            TIMESTAMPTZ,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Audit log for subscription changes (who changed what, when, and previous value)
CREATE TABLE subscription_audit_log (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    subscription_id UUID NOT NULL REFERENCES subscriptions(id),
    changed_by      UUID,    -- NULL = system action
    field           TEXT NOT NULL,
    old_value       TEXT,
    new_value       TEXT,
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Claude explains the immutable plans decision: “Plans are immutable once created. If you change a price, create a new plan and update the subscription to point to it. This preserves the historical price on each subscription and makes invoices auditable.”

Asking for Index Strategy

After generating the schema, ask specifically about indexes:

Given this schema and these query patterns:
1. "All subscriptions due for renewal today" — runs every minute in a cron job
2. "All unpaid invoices for customer X" — runs on every customer page load
3. "Payment method for subscription S" — runs before every charge attempt
4. "Audit log for subscription S, last 30 entries" — runs on admin requests

What indexes should I create? Explain why each is needed.

Claude’s index recommendations:

-- For query 1: subscriptions due for renewal
-- Status filter eliminates most rows; then sort/filter on current_period_end
-- Partial index is smaller and faster than a full index
CREATE INDEX idx_subscriptions_renewal
    ON subscriptions (current_period_end)
    WHERE status = 'active';

-- For query 2: unpaid invoices by customer
-- Composite index: customer_id first (equality filter), then status (equality filter)
-- Covering index includes invoice amount to avoid heap fetch for summary queries
CREATE INDEX idx_invoices_customer_unpaid
    ON invoices (customer_id, status)
    INCLUDE (amount_cents, due_date)
    WHERE status IN ('open', 'past_due');

-- For query 3: payment method lookup
-- subscription_id is the lookup key; include the fields needed by the charge flow
CREATE INDEX idx_subscriptions_payment_method
    ON subscriptions (id)
    INCLUDE (payment_method_id);

-- For query 4: audit log pagination
-- subscription_id + changed_at covers the filter + ORDER BY + LIMIT
CREATE INDEX idx_audit_log_subscription
    ON subscription_audit_log (subscription_id, changed_at DESC);

The partial index on subscriptions (only for status = 'active') is an optimization most developers wouldn’t reach for without prompting — it’s smaller, faster to update, and more cache-friendly.

Modeling Complex Constraints

Describe business rules in plain language, let Claude translate them to SQL constraints:

Add database-level enforcement for these rules:

1. A subscription cannot have current_period_end before current_period_start
2. A customer cannot have two active subscriptions to plans at the same tier
3. An invoice's total_cents must equal the sum of its line_items amounts
4. A payment can only reference an invoice that belongs to the same customer as the payment

Add these as CHECK constraints or triggers as appropriate.

Claude’s output:

-- Rule 1: Simple CHECK constraint
ALTER TABLE subscriptions
    ADD CONSTRAINT subscriptions_period_order
    CHECK (current_period_end > current_period_start);

-- Rule 2: Unique partial index (more flexible than a constraint)
-- Only one active subscription per customer per tier
CREATE UNIQUE INDEX idx_subscriptions_one_active_per_tier
    ON subscriptions (customer_id,
                       (SELECT tier FROM plans WHERE plans.id = plan_id))
    WHERE status = 'active';

-- Rule 3: Trigger to verify invoice total integrity
CREATE OR REPLACE FUNCTION check_invoice_total()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.total_cents != (
        SELECT COALESCE(SUM(amount_cents), 0)
        FROM invoice_line_items
        WHERE invoice_id = NEW.id
    ) THEN
        RAISE EXCEPTION 'Invoice total_cents (%) does not match sum of line items (%)',
            NEW.total_cents,
            (SELECT COALESCE(SUM(amount_cents), 0) FROM invoice_line_items WHERE invoice_id = NEW.id);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER invoice_total_check
    AFTER INSERT OR UPDATE ON invoices
    FOR EACH ROW EXECUTE FUNCTION check_invoice_total();

-- Rule 4: FK with join validation via trigger (FK alone can't cross-check)
CREATE OR REPLACE FUNCTION check_payment_customer_match()
RETURNS TRIGGER AS $$
DECLARE
    invoice_customer UUID;
BEGIN
    SELECT customer_id INTO invoice_customer
    FROM invoices WHERE id = NEW.invoice_id;

    IF invoice_customer != NEW.customer_id THEN
        RAISE EXCEPTION 'Payment customer_id (%) does not match invoice customer_id (%)',
            NEW.customer_id, invoice_customer;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Schema Evolution Planning

Ask Claude to plan migration paths before you commit to a design:

We're starting with this schema but know we'll need these features later:
- Multi-currency support (currently USD only)
- Team/organization accounts (currently individual only)
- Usage-based billing (currently flat-rate only)

What schema decisions should we make NOW to make these easier later?
What should we avoid that would make migrations painful?

Claude’s response typically identifies:

Verifying Schema Quality

After generating a schema, ask Claude to critique it:

Review this schema for: normalization issues, missing constraints, index gaps,
and any design decisions that will cause problems at scale (>10M rows per table).
Be specific about each problem and suggest the fix.

This “adversarial review” catches issues the initial generation missed. Running the generated schema through this critique loop produces better output than a single large prompt.

Built by theluckystrike — More at zovo.one

Explore Our Developer Guides

© 2026 theluckystrike Developer Guides