ADR 0002 — Substrate: Postgres + pgvector
Status: Accepted
Date: 2026-05-05
Context
Substrate must store: artefacts (ideas, prototypes, opportunities), lineage graph, agent state, embeddings for semantic search, full-text/keyword search, structured metadata. Targeted scale for v0: O(10⁵) artefacts, O(10⁶) lineage edges, single-region single-machine deployment. Long-term ceiling envisaged: O(10⁸) artefacts.
Decision
Postgres 16 as the primary store, with pgvector for embeddings and pg_trgm for trigram-based fuzzy/keyword search.
Single Postgres instance covers the full data model:
- Relational tables for artefacts, agents, lineage edges.
vector(1536)columns on artefacts for embeddings (matches OpenAItext-embedding-3-small).- HNSW index for cosine similarity.
- GIN trigram index for keyword search.
- JSONB for free-form metadata where shape will evolve.
Consequences
- Operational simplicity. One database, one backup story, one connection pool, one ACID boundary across artefacts + lineage + agent state. Especially valuable when the schema is still discovering itself.
- Hybrid search is trivial. Filter by tag, kind, time + ANN-rank by embedding + re-rank by trigram, all in a single SQL query.
- Migrations are normal Postgres migrations. Alembic handles schema; no separate vector-store migration tooling.
- Cost. Free, runs on commodity hardware, no SaaS bill.
- Scale ceiling. pgvector with HNSW handles ~10–100M vectors comfortably on modest hardware. We are nowhere near that. If we ever cross it, swap-out is a focused project on a single column.
Alternatives considered
- Dedicated vector DB (Pinecone, Weaviate, Qdrant, Milvus). Rejected: introduces a second store + sync layer + extra ops surface for a problem we don't yet have. We are well below the scale where dedicated vector DBs win.
- SQLite + sqlite-vec. Tempting for embedded simplicity. Rejected: write concurrency from a multi-service stack is awkward; we'd outgrow it within months.
- Postgres without pgvector, vectors in a sidecar. Rejected: defeats the point of unified search.