Skip to main content
Status: Accepted Date: 2026-02-06 Deciders: Reflections Maintainers

​
Context

The platform requires transactional integrity, temporal modeling, relational joins, and vector similarity search over embeddings. Operationally, migrations and CI checks must remain straightforward for a small team.

​
Decision

Adopt Supabase-managed Postgres as the primary data platform with:
  • pgvector for embedding storage and nearest-neighbor search.
  • pg_trgm for fuzzy/text matching support.
  • SQL migrations in supabase/migrations as source-controlled schema history.
  • Local-first drift control through reset/lint/migration contract checks.

​
Alternatives considered

​
Alternative 1: Split relational DB + dedicated vector DB

Pros:
  • Potentially better specialized vector search tuning.
Cons:
  • Dual-write and consistency complexity.
  • More infrastructure and operational overhead.
Pros:
  • Flexible schema evolution.
Cons:
  • Weaker relational constraints for lifecycle/state invariants.
  • More complex temporal validity and supersession enforcement.

​
Alternative 3: Self-hosted Postgres without Supabase toolchain

Pros:
  • Full control over deployment/runtime.
Cons:
  • Higher operational burden for backups, policies, and environments.
  • Less streamlined local DX for schema reset and migration workflows.

​
Consequences

Benefits:
  • Strong transactional semantics and mature SQL tooling.
  • One platform supports both relational and vector retrieval needs.
  • Migration workflows can be codified in CI.
Costs:
  • SQL-heavy schema evolution requires discipline and review rigor.
  • Vector indexing and query tuning responsibility remains in-house.

​
Implementation notes

  • Extensions and foundational schema are initialized in the first migration.
  • Supabase CLI workflows are anchored by db:migrate, db:reset, db:lint, and db:check scripts in root package.json.
  • Drift and migration ordering contracts are enforced by tests under packages/db.