Designing a PostgreSQL Schema for Review History and Semantic Retrieval

Hello! In the previous lesson, you defined four domain objects without tying them to a database: Note, ReviewEvent, SchedulingState, and ReminderAttempt. Now we need a storage design that preserves their meanings. In particular, the database must keep review history even as schedules change, find due notes efficiently, and connect semantic-search results back to the right notes.

This lesson develops a single-vault PostgreSQL schema for those workflows. Allow about 40–50 minutes, including the short videos and targeted reading. The SQL is a design to understand and inspect; the next lesson covers applying it as a versioned migration.

Start with identities, guarantees, and queries

A table design is easier to judge when you know what must remain true and what the application will ask of it. Here are the central decisions:

• A note’s stable note_id identifies it; source_path describes its current location. Renaming a file must not replace its review history.
• Each accepted submission creates a review_events row. A unique submission_key distinguishes a retry from a new review.
• scheduling_states holds one current state per note. It is separate from the many historical events for that note.
• An embedding belongs to a note, but is generated from a particular version of its content using a particular model. It needs provenance as well as a vector.
• A reminder claim is unique for a recipient and window start, matching the at-most-one-request policy from the previous lesson.

PostgreSQL constraints are useful here because they protect these rules even if two application processes act at once. Read the selected parts of PostgreSQL’s Constraints documentation before looking at the schema. Pay particular attention to the difference between a CHECK on one row, uniqueness across rows, and a foreign key between tables.

The other design input is the expected query. An index on a primary key helps find one known note, but the daily-review workflow instead asks for states ordered by due time. Review history asks for events belonging to one note, newest first. Indexes should follow those access patterns rather than simply repeat every column name.

For a visual explanation of why, watch these brief portions of Hussein Nasser’s Database Indexing Explained (with PostgreSQL). The large demonstration table is not a proposed design for this application; it makes the difference between an index lookup and a scan visible.

Translate the domain into tables

Semantic retrieval adds one storage concept beyond the four Python models: a note embedding. It lives in its own table so note ingestion can update text independently of embedding generation. Before examining that table, watch the selected portions of NeuralNine’s PGVector: Turn PostgreSQL Into A Vector Database. They show why the vector column has a fixed dimension and why enabling the extension is a separate step from installing it.

Below is a proposed schema. vector(768) is illustrative: before applying this design, replace 768 with the verified output dimension of one explicitly selected embedding model. The pgvector extension must also be installed on the PostgreSQL server before CREATE EXTENSION can enable it in this database.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE notes (
    note_id       text PRIMARY KEY,
    source_path   text NOT NULL UNIQUE,
    title         text NOT NULL,
    body          text NOT NULL,
    tags          text[] NOT NULL DEFAULT '{}',
    content_hash  text NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now(),
    updated_at    timestamptz NOT NULL DEFAULT now(),
    archived_at   timestamptz,

    CONSTRAINT notes_id_nonblank CHECK (btrim(note_id) <> ''),
    CONSTRAINT notes_path_nonblank CHECK (btrim(source_path) <> ''),
    CONSTRAINT notes_title_nonblank CHECK (btrim(title) <> ''),
    CONSTRAINT notes_hash_format
        CHECK (content_hash ~ '^[0-9a-f]{64}$')
);

CREATE TABLE scheduling_states (
    note_id           text PRIMARY KEY
                      REFERENCES notes(note_id) ON DELETE RESTRICT,
    due_at            timestamptz NOT NULL,
    last_reviewed_at  timestamptz,
    review_count      integer NOT NULL DEFAULT 0,
    interval_days     integer NOT NULL DEFAULT 0,
    updated_at        timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT scheduling_nonnegative
        CHECK (review_count >= 0 AND interval_days >= 0),
    CONSTRAINT scheduling_first_review_consistent
        CHECK (
            (review_count = 0
             AND last_reviewed_at IS NULL
             AND interval_days = 0)
            OR
            (review_count > 0 AND last_reviewed_at IS NOT NULL)
        )
);

CREATE TABLE review_events (
    event_id        text PRIMARY KEY,
    submission_key  text NOT NULL UNIQUE,
    note_id         text NOT NULL
                    REFERENCES notes(note_id) ON DELETE RESTRICT,
    rating          text NOT NULL,
    reviewed_at     timestamptz NOT NULL,
    recorded_at     timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT review_event_id_nonblank CHECK (btrim(event_id) <> ''),
    CONSTRAINT review_submission_nonblank
        CHECK (btrim(submission_key) <> ''),
    CONSTRAINT review_rating_supported
        CHECK (rating IN ('again', 'hard', 'good', 'easy'))
);

CREATE TABLE note_embeddings (
    note_id      text PRIMARY KEY
                 REFERENCES notes(note_id) ON DELETE CASCADE,
    model_id     text NOT NULL,
    source_hash  text NOT NULL,
    embedding    vector(768) NOT NULL,
    embedded_at  timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT embedding_model_nonblank CHECK (btrim(model_id) <> ''),
    CONSTRAINT embedding_hash_format
        CHECK (source_hash ~ '^[0-9a-f]{64}$')
);

CREATE TABLE reminder_attempts (
    recipient_id         text NOT NULL,
    window_start         timestamptz NOT NULL,
    window_end           timestamptz NOT NULL,
    claimed_at           timestamptz NOT NULL DEFAULT now(),
    status               text NOT NULL DEFAULT 'claimed',
    request_accepted_at  timestamptz,

    PRIMARY KEY (recipient_id, window_start),
    CONSTRAINT reminder_recipient_nonblank
        CHECK (btrim(recipient_id) <> ''),
    CONSTRAINT reminder_window_order
        CHECK (window_end > window_start),
    CONSTRAINT reminder_status_supported
        CHECK (
            status IN (
                'claimed',
                'request_accepted',
                'request_outcome_unknown'
            )
        ),
    CONSTRAINT reminder_acceptance_consistent
        CHECK (
            (status = 'request_accepted')
            = (request_accepted_at IS NOT NULL)
        )
);

CREATE INDEX scheduling_due_idx
    ON scheduling_states (due_at, note_id);

CREATE INDEX review_events_by_note_idx
    ON review_events (note_id, reviewed_at DESC, event_id DESC);

CREATE INDEX notes_tags_gin_idx
    ON notes USING gin (tags);

The keys express different kinds of identity. notes.note_id survives a rename; UNIQUE (source_path) prevents two current records from claiming the same file within this single-vault design. A future multi-vault design would need to scope path uniqueness to a vault. scheduling_states.note_id is both a primary key and a foreign key, establishing at most one current schedule for each note. review_events.event_id identifies a historical fact, while the separate unique submission_key protects against inserting that same logical submission twice.

The CHECK constraints mirror useful parts of the Python invariants. For example, a first-review state has no last_reviewed_at. NOT NULL matters alongside CHECK: PostgreSQL does not treat a check that evaluates to null as a failure. None of these row-level checks can prove that review_count equals the number of event rows, or that note_embeddings.source_hash matches the current note hash. Those cross-row relationships belong to carefully designed service and transaction operations.

All event, schedule, claim, and embedding times use timestamptz so they represent instants rather than unspecified local times. reviewed_at is the accepted review time; recorded_at says when PostgreSQL recorded the event. created_at, updated_at, and embedded_at serve different purposes. A default fills a timestamp on insertion only: when ingestion changes a note, or scheduling changes a state, the repository must explicitly set its updated_at. The schema does not silently do that for it.

There is no physical delete requirement in the daily workflow. Setting archived_at can remove a note from active review and search while retaining its history. ON DELETE RESTRICT also prevents accidentally deleting a note with schedule or review rows. Embeddings can be discarded with a note if a deliberate physical deletion is possible; hence their different ON DELETE CASCADE policy.

Finally, calling an event “immutable” is a domain and application policy, not a property conferred by its primary key. This schema stops duplicate identities and invalid ratings, but a database role with update privileges could still edit an event. Restricting those privileges or adding further database enforcement is a separate decision.

Check the schema against its three principal reads

The index on (due_at, note_id) reflects the due-queue question: find states before a chosen day’s end and order ties consistently. The service will later calculate the relevant timezone-aware day boundary; a query can then compare that instant against due_at. It should also exclude notes with an archived_at value. The primary-key index on note_id alone would not provide this due-time ordering.

For one note’s history, (note_id, reviewed_at DESC, event_id DESC) supports filtering by note and displaying recent events first. The event ID supplies a stable order when two events share a review timestamp. These are separate indexes because the due queue and event history ask different questions.

The embedding read has an additional question: “Which stored vectors are closest to a query vector, among eligible notes?” Read pgvector’s Querying and Indexing portions for the available distance operators and the distinction between exact and approximate search.

For a small local note collection, start with exact vector search, without a vector index. For example, a retrieval query can join note_embeddings to notes, exclude archived notes, filter tags where appropriate, and require both the intended model_id and source_hash = content_hash. It can then rank remaining rows by cosine distance using pgvector’s <=> operator and apply a limit. The query vector must be produced by the same embedding model and have the stored dimension.

The source_hash comparison is important. Define content_hash as the hash of a canonical representation of exactly the note material sent for embedding—for example, a consistently formatted combination of title, body, and tags. If ingestion changes that material, the note hash changes; until a new embedding is stored, the old vector is recognizable as stale. model_id separately distinguishes a change of embedding model from a change of note content. The hash columns record this provenance, but the application must compute and update them correctly.

The GIN index on notes.tags is intended for containment filters such as “notes containing this tag.” It does not guarantee a faster query: PostgreSQL chooses a plan based on table size and the complete query. Likewise, adding a vector index is not automatically better. If exact similarity search later becomes too slow, an HNSW index with the cosine operator class is one possible measured improvement:

CREATE INDEX note_embeddings_cosine_hnsw_idx
    ON note_embeddings
    USING hnsw (embedding vector_cosine_ops);

That index matches cosine-distance ranking, but HNSW performs approximate search and can change which neighbors are returned. Metadata filtering can further affect how many matches an approximate search produces. Keep the exact approach until performance measurements justify that trade-off.

The reminder table has its own lookup guarantee rather than a speculative index: its composite primary key lets a reminder run attempt to claim a particular (recipient_id, window_start) only once. The service must claim the row before making the external request. As discussed previously, this prevents duplicate requests at the cost of potentially missing a reminder after a crash. A timeout must not be interpreted as permission to make a second request for the same window.

Conclusion

This schema separates current note content, current scheduling state, historical review events, embedding provenance, and reminder claims. Primary and foreign keys preserve identity and relationships; unique and check constraints guard important invariants; timestamps retain the distinction between occurrence and recording; and indexes follow the due-queue, history, and tag-filter queries. The vector column supports exact semantic retrieval now, with approximate indexing left as a measured option.

Next, you will turn a schema design into a versioned SQL migration—including how to apply changes safely instead of treating CREATE TABLE statements as one-off setup commands.