PostgreSQL data dictionary generator

CREATE TABLE accounts (
    id          bigserial PRIMARY KEY,
    email       varchar(255) NOT NULL UNIQUE,
    plan        varchar(20)  NOT NULL DEFAULT 'trial',
    settings    jsonb        NOT NULL DEFAULT '{}',
    created_at  timestamptz  NOT NULL DEFAULT now()
);
COMMENT ON TABLE accounts IS 'Workspaces; billing attaches here, not to users';
COMMENT ON COLUMN accounts.plan IS 'trial -> starter -> team; see pricing table';
COMMENT ON COLUMN accounts.settings IS 'Feature flags and preferences, schemaless';

CREATE TABLE users (
    id          bigserial PRIMARY KEY,
    account_id  bigint       NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    email       varchar(255) NOT NULL,
    role        varchar(20)  NOT NULL DEFAULT 'member',
    last_seen   timestamptz
);
COMMENT ON COLUMN users.role IS 'owner / admin / member — checked in the API layer';

CREATE TABLE invoices (
    id          bigserial PRIMARY KEY,
    account_id  bigint        NOT NULL,
    total       numeric(10,2) NOT NULL,
    issued_on   date          NOT NULL
);
ALTER TABLE ONLY invoices
    ADD CONSTRAINT invoices_account_fk FOREIGN KEY (account_id) REFERENCES accounts(id);
COMMENT ON TABLE invoices IS 'Issued monthly; the FK arrives via ALTER TABLE, pg_dump style';

CREATE TABLE usage_events (
    account_id  bigint      NOT NULL REFERENCES accounts(id),
    happened_at timestamptz NOT NULL,
    kind        text        NOT NULL
) PARTITION BY RANGE (happened_at);
COMMENT ON TABLE usage_events IS 'Metering stream; partitioned by month';

CREATE TABLE usage_events_2026_07 PARTITION OF usage_events
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE usage_events_2026_08 PARTITION OF usage_events
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE INDEX idx_users_account ON users(account_id);
CREATE INDEX idx_invoices_account ON invoices(account_id);