> ## Documentation Index
> Fetch the complete documentation index at: https://docs.reflections.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# ADR-0004: Primary data platform: Supabase Postgres

> Use a relational core with vector and text-search extensions to support transactional learning and retrieval workloads.

<Info>**Status:** Accepted **Date:** 2026-02-06 **Deciders:** Reflections Maintainers</Info>

## 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.

### Alternative 2: Fully managed document store with secondary search

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`.

## Related ADRs

* [ADR-0005: Temporal fact and patch lifecycle](/decisions/adr-0005)
* [ADR-0012: CI/CD quality and release gates](/decisions/adr-0012)
* [ADR-0013: Migration governance and drift control](/decisions/adr-0013)