PostgreSQL Schema

PostgreSQL handles everything that is not scripture content. It is the system of record for users, subscriptions, application state, and AI embeddings.

Engine: PostgreSQL 16 + pgvector extension
Port: 5432
Database name: gospelib
Table prefix: gl_

Schema Groups

Auth — User Accounts

CREATE TABLE gl_users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    clerk_id        TEXT UNIQUE NOT NULL,
    email           TEXT NOT NULL,
    display_name    TEXT,
    plan_id         TEXT NOT NULL DEFAULT 'free',  -- denormalized from subscription
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE gl_user_devices (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES gl_users(id) ON DELETE CASCADE,
    platform        TEXT NOT NULL CHECK (platform IN ('ios', 'android', 'web')),
    push_token      TEXT,
    app_version     TEXT,
    last_seen_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

Billing — Subscriptions

CREATE TABLE gl_subscriptions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES gl_users(id),
    stripe_sub_id   TEXT UNIQUE NOT NULL,
    plan_id         TEXT NOT NULL,            -- matches plans.yaml key
    status          TEXT NOT NULL,            -- active | trialing | canceled | past_due
    current_period_start TIMESTAMPTZ NOT NULL,
    current_period_end   TIMESTAMPTZ NOT NULL,
    cancel_at_period_end BOOLEAN DEFAULT FALSE,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE gl_stripe_events (
    stripe_event_id TEXT PRIMARY KEY,         -- idempotency key
    event_type      TEXT NOT NULL,
    processed_at    TIMESTAMPTZ DEFAULT NOW()
);

The gl_stripe_events table ensures webhook idempotency — every Stripe event is checked against this table before processing.

Study Data — User-Generated Content

CREATE TABLE gl_highlights (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES gl_users(id) ON DELETE CASCADE,
    passage_id      TEXT NOT NULL,           -- graph node ID e.g. "gen.1.1"
    color           TEXT NOT NULL DEFAULT 'yellow',
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE gl_notes (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES gl_users(id) ON DELETE CASCADE,
    passage_id      TEXT,                    -- nullable (note may span passages)
    topic_id        TEXT,                    -- or attached to a TG topic
    title           TEXT,
    body            TEXT NOT NULL,
    is_private      BOOLEAN DEFAULT TRUE,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE gl_bookmarks (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES gl_users(id) ON DELETE CASCADE,
    passage_id      TEXT NOT NULL,
    label           TEXT,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE gl_reading_progress (
    user_id         UUID NOT NULL REFERENCES gl_users(id) ON DELETE CASCADE,
    book_id         TEXT NOT NULL,
    chapter         INT NOT NULL,
    last_verse      INT,
    completed       BOOLEAN DEFAULT FALSE,
    updated_at      TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (user_id, book_id, chapter)
);

AI — Embeddings (pgvector)

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE gl_passage_embeddings (
    passage_id      TEXT PRIMARY KEY,         -- graph node ID
    embedding       VECTOR(1024),             -- Voyage AI / sentence-transformers
    model_version   TEXT NOT NULL,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_passage_embeddings_hnsw
    ON gl_passage_embeddings USING hnsw (embedding vector_cosine_ops);

pgvector is sufficient for <10M vectors. If we outgrow it, Pinecone or Weaviate can be swapped in Phase 3.

Audit Trail

CREATE TABLE gl_audit_log (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID REFERENCES gl_users(id),
    action          TEXT NOT NULL,
    resource_type   TEXT,
    resource_id     TEXT,
    metadata        JSONB,
    ip_address      INET,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_audit_log_user ON gl_audit_log(user_id, created_at DESC);
CREATE INDEX idx_audit_log_action ON gl_audit_log(action, created_at DESC);

Migration Strategy

Migrations are managed by golang-migrate and live alongside the Go services that own each schema group:

Schema Group	Migration Directory
Auth (gl_users, gl_user_devices)	services/auth/migrations/
Billing (gl_subscriptions, gl_stripe_events)	services/billing/migrations/

Migrations follow these rules:

• Forward-only — never modify a published migration
• Each migration is numbered sequentially: 001_initial.sql, 002_add_index.sql
• Every migration has an up and down script
• Study data tables and AI tables are created by whichever service owns them

Hosting Progression

Phase	Provider	Instance	Multi-AZ
Phase 1	Docker (local) or RDS	db.t3.micro (free tier)	No
Phase 2	RDS	db.r6g.large	Yes
Phase 3	Aurora Serverless v2	Auto-scaling	Yes

Related Pages

• Data Architecture Overview — When to use PostgreSQL vs other stores
• FalkorDB Graph Model — Scripture content that lives in the graph
• Authentication Flow — How gl_users syncs with Clerk
• Entitlements — How gl_subscriptions drives plan gating