Databases: SQLite, Postgres, and Per-User Schema Design

1. Relational Basics

The relational model organizes data into tables. Each row (a tuple) is one record — one user, one message — and each column is a typed field shared by every row. Two kinds of keys give tables their power. A primary key uniquely identifies each row (no two users share a user_id). A foreign key is a column that must match a primary key in another table, enforcing referential integrity: the database refuses to insert a message pointing at a conversation that does not exist. Relationships come in three shapes: one-to-one, one-to-many (a conversation has many messages), and many-to-many (users and conversations), resolved with a linking table.

SQLite and Postgres both speak SQL and provide ACID transactions, but sit at opposite ends of the deployment spectrum. SQLite is an embedded library that reads and writes a single file inside your own process; the project frames it as competing "with fopen()," not with a database server. Postgres is a separate server process you connect to over a socket or network. Concurrency is the decisive difference: SQLite supports unlimited readers but only one writer at a time (WAL mode lets readers proceed alongside that writer), while Postgres uses MVCC (Multi-Version Concurrency Control) so readers and writers never block each other and hundreds of clients can write simultaneously.

A transaction is a group of operations treated as one indivisible unit, guaranteed to be ACID. Atomicity: all succeed or none do. Consistency: constraints always hold. Isolation: concurrent transactions do not see each other's uncommitted changes. Durability: committed data survives a crash. Atomicity is why we wrap "insert message + bump last_message_at" in one transaction — a crash between the two writes can never leave the timestamp lying about a message that was not stored. Isolation levels (default READ COMMITTED, stricter SERIALIZABLE) trade consistency against throughput; for a chat gateway the defaults are almost always correct.

2. Schema Design for a Gateway

A chat backend maps cleanly onto the relational model. The gateway uses five related tables. A users table holds one row per account, independent of platform. A channels table records each platform integration plus the platform-specific identity, so one human on both Slack and Discord resolves to a single user_id. A conversations table holds one row per thread with a type column and a denormalized last_message_at. A conversation_participants junction resolves the many-to-many between users and conversations. A messages table holds one row per turn, carrying a role column that aligns with LLM chat-completion formats.

Figure 11.1: Entity-Relationship diagram of the gateway schema. Read the crow's-foot ||--o{ as "has many."

Here is the worked schema (Postgres syntax; BIGSERIAL becomes INTEGER PRIMARY KEY and TIMESTAMPTZ becomes TEXT in SQLite):

CREATE TABLE users (
    user_id       BIGSERIAL PRIMARY KEY,
    display_name  TEXT        NOT NULL,
    email         TEXT        UNIQUE,
    status        TEXT        NOT NULL DEFAULT 'active',
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE channels (
    channel_id        BIGSERIAL PRIMARY KEY,
    user_id           BIGINT      NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    platform          TEXT        NOT NULL,          -- 'slack' | 'discord' | 'webex'
    platform_user_id  TEXT        NOT NULL,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (platform, platform_user_id)              -- one identity maps to one user
);

CREATE TABLE conversations (
    conversation_id  BIGSERIAL PRIMARY KEY,
    type             TEXT        NOT NULL DEFAULT 'direct',  -- 'direct' | 'group'
    title            TEXT,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_message_at  TIMESTAMPTZ                             -- denormalized for inbox sort
);

CREATE TABLE conversation_participants (
    conversation_id  BIGINT NOT NULL REFERENCES conversations(conversation_id) ON DELETE CASCADE,
    user_id          BIGINT NOT NULL REFERENCES users(user_id)                 ON DELETE CASCADE,
    joined_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_read_at     TIMESTAMPTZ,
    PRIMARY KEY (conversation_id, user_id)           -- composite PK: no duplicate members
);

CREATE TABLE messages (
    message_id       BIGSERIAL PRIMARY KEY,
    conversation_id  BIGINT      NOT NULL REFERENCES conversations(conversation_id) ON DELETE CASCADE,
    sender_id        BIGINT              REFERENCES users(user_id),   -- NULL for assistant/system
    role             TEXT        NOT NULL,                            -- 'user'|'assistant'|'system'
    content          TEXT        NOT NULL,
    sent_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

Normalization stores each fact exactly once. Third Normal Form (3NF) — the practical target — means non-key columns depend only on the primary key. Do not store sender_username inline in every message; a rename would force updating thousands of rows (an update anomaly). Store only sender_id and join. The one sanctioned denormalization is last_message_at, kept in sync on each insert so the inbox sorts without scanning messages.

ON DELETE CASCADE removes participant rows and messages when a conversation is deleted, preventing orphans. Critical SQLite caveat: foreign key enforcement is off by default and must be enabled per connection with PRAGMA foreign_keys = ON;. Postgres enforces unconditionally. An index lets the database find rows without scanning the whole table (like a book's back-of-index). The dominant query — recent N messages per conversation, paginated — is served by a composite index on messages(conversation_id, sent_at). Also index foreign-key columns, because Postgres does not auto-index the referencing side.

CREATE INDEX idx_messages_conv_time ON messages (conversation_id, sent_at);
CREATE INDEX idx_messages_sender    ON messages (sender_id);
CREATE INDEX idx_participants_user  ON conversation_participants (user_id);

Linking accounts: when an event arrives, ingress looks up (platform, platform_user_id) in channels. A hit yields the internal user_id; a miss creates a new users row and a channels row in one transaction. The UNIQUE (platform, platform_user_id) constraint guarantees each platform identity resolves to exactly one internal user, while multiple channels rows can point at the same user_id — that is account linking. From then on, every message and conversation attaches to the stable internal ID, not the volatile platform ID.

3. Per-User Data Design

Multi-tenancy serves many independent users from one application while keeping each tenant's data separate. The shared-schema approach stores every tenant's rows in the same tables, distinguished by a tenant_id (here, effectively user_id) column; every query filters WHERE user_id = $1. It is the simplest to operate and scales to enormous tenant counts, but isolation lives in application code — forget the filter and you leak. The schema-per-tenant approach gives strong logical isolation but bloats system catalogs and makes migrations slow across thousands of schemas.

The modern default is shared schema hardened with Postgres Row-Level Security (RLS): a policy attached to a table so the engine itself appends the tenant filter to every query. RLS eliminates the "forgotten WHERE tenant_id" bug at the database level — even a query that omits the filter returns only the current tenant's rows.

Figure 11.2: Shared-schema row isolation — every query scoped to the authenticated user.

ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

CREATE POLICY messages_isolation ON messages
    USING (
        conversation_id IN (
            SELECT conversation_id FROM conversation_participants
            WHERE user_id = current_setting('app.current_user_id')::bigint
        )
    );

Two disciplines defend against cross-user leakage — the most dangerous class of bug because it is silent, a privacy breach, and can persist undetected. First, make the tenant filter unavoidable: funnel all access through a thin data-access layer whose functions always take a user_id and always include it in the WHERE clause, backed by RLS as the final line of defense. Second, never trust the client's claimed identity: the user_id must come from the authenticated identity established at ingress (Chapter 5), not from a request-body field. A user who can pass an arbitrary user_id has defeated every isolation mechanism above it.

A schema is never finished. A migration is a versioned, ordered script that applies one change so every environment reaches the same state deterministically. Tools like Alembic record which migrations ran and apply pending ones in order. Migrations also execute the SQLite-to-Postgres graduation without surprises. Production migrations carry an operational hazard: locking. Adding a foreign key on Postgres takes a lock that can block writes while it validates rows. Set lock_timeout and statement_timeout so a migration fails fast rather than freezing the app, and prefer additive, backward-compatible changes (add a nullable column, backfill, then enforce) over destructive ones.

4. Async Database Access

The cardinal asyncio rule applies with full force: a single blocking call stalls every concurrent coroutine. A synchronous driver waiting on a socket blocks the loop, so no other webhook is acknowledged and no worker progresses — the gateway freezes. asyncpg solves this for Postgres: written for the event loop, it performs genuinely non-blocking I/O, implements the Postgres binary wire protocol directly, and uses prepared statements by default. For SQLite in dev and tests, aiosqlite wraps calls in a background thread to present an async interface.

Pooling is essential. Opening a new Postgres connection is expensive (TCP handshake, TLS, auth, backend startup); reusing pooled connections can cut short-query latency by 10–100x. A connection pool maintains established connections that coroutines borrow and return; asyncpg ships pooling built in. Create a single, process-wide pool at startup — never per request. min_size keeps connections warm; max_size bounds the load your app can place on the database so a stampede of coroutines cannot open unlimited connections.

Figure 11.3: A coroutine borrowing and returning a pooled connection.

import asyncpg

pool: asyncpg.Pool | None = None

async def init_db() -> None:
    """Call once at application startup (e.g., a FastAPI lifespan handler)."""
    global pool
    pool = await asyncpg.create_pool(
        dsn="postgresql://user:pass@host/gateway",
        min_size=4,               # connections kept warm
        max_size=20,              # hard cap on concurrency to the DB
        statement_cache_size=512, # speeds repeated queries
    )

Borrow a connection with an async context manager (guaranteed return even on error) and wrap multi-statement writes in a transaction. Here the schema and async pool tie together — recording a message and bumping the conversation atomically:

async def store_message(conversation_id: int, sender_id: int,
                        role: str, content: str) -> int:
    async with pool.acquire() as conn:          # borrow, auto-return
        async with conn.transaction():          # atomic: both or neither
            message_id = await conn.fetchval(
                """
                INSERT INTO messages (conversation_id, sender_id, role, content)
                VALUES ($1, $2, $3, $4)
                RETURNING message_id
                """,
                conversation_id, sender_id, role, content,
            )
            await conn.execute(
                "UPDATE conversations SET last_message_at = now() "
                "WHERE conversation_id = $1",
                conversation_id,
            )
            return message_id

Several patterns keep the loop healthy. Bound fan-out: guard many concurrent DB tasks with an asyncio.Semaphore so you never spawn thousands of coroutines contending for max_size connections — otherwise they queue on acquire() and latency balloons. Set explicit timeouts with asyncio.timeout(...) so a slow query cannot pin a connection forever. And never mix a blocking sync driver into the async path; offload blocking code with run_in_executor.

Look again at every example: each user value is passed as a separate argument ($1, $2), not glued into the SQL string. This is a parameterized query — the database receives the template and the values separately, so a value can never be interpreted as SQL. SQL injection is the attack this defeats: build a query with f"... WHERE content = '{user_input}'" and an attacker sending '; DROP TABLE messages; -- injects their own SQL. Because a chat gateway feeds arbitrary user text into queries, this is the expected threat, not a theoretical one. asyncpg uses numbered placeholders ($1); aiosqlite uses ?; both bind safely. The rule is absolute: never interpolate user input into SQL, always bind it as a parameter.