DatabaseOverview

Database

HiveCFM stores everything in one PostgreSQLPostgreSQLThe open-source relational database HiveCFM runs on. Replaces SQL Server for this project. instance — 16 with the pgvectorpgvectorA Postgres extension that adds a vector column type for similarity search. Used for AI-powered survey insights. extension enabled. There are no microservice databases, no read-side stores for analytics, no separate vector DB. Postgres is the single source of truth.

Shape of the world

PieceLives inWhy
Schema definitionhivecfm-core/packages/database/schema.prismaOne file defines every table, column, and relation.
Migrationshivecfm-core/packages/database/migrations/Timestamp-prefixed folders, applied in order.
Generated clienthivecfm-core/packages/database/dist/ (built from schema)Importable as @hivecfm/database.
Vector columnsCustom SQL migrations, plus PrismaPrismaThe TypeScript ORM HiveCFM uses to talk to Postgres. The schema lives at packages/database/schema.prisma. Unsupported("vector(N)") markerspgvector stores embeddings as a typed column.
Job queue tablesRiver-managed tablesThe RiverRiverThe Go background-job queue Hub uses. Jobs are rows in Postgres, so there is no separate broker to run. queue is just a few Postgres tables.

The interactive schema browser is at Schema Viewer — click a table to see its columns, types, and relations.

Connection and ports

  • Local dev: postgresql://postgres:postgres@localhost:5433/hivecfm?schema=public.
  • Port 5433 (not 5432) to avoid clashing with a system Postgres; set by docker-compose.dev-ports.yml.
  • The Hub connects to its own database (hivecfm_hub) on the same Postgres instance.
  • Prisma Client opens a pool per Node process; pool size is controlled by the connection_limit query-string param on DATABASE_URL.

Why PrismaPrismaThe TypeScript ORM HiveCFM uses to talk to Postgres. The schema lives at packages/database/schema.prisma.

Think of PrismaPrismaThe TypeScript ORM HiveCFM uses to talk to Postgres. The schema lives at packages/database/schema.prisma. as Entity Framework with the axis flipped: the schema file is the source of truth, and the C#/TypeScriptTypeScriptJavaScript with a static type system. Every HiveCFM Node service, the frontend, and the dev hub are written in it. classes are generated from it. This matters in practice because:

  • You never write T-SQL CREATE TABLE statements by hand. schema.prisma -> prisma migrate dev -> migration SQL gets generated.
  • Types flow automatically into every call site. If you add a column, every prisma.user.findMany() consumer picks up the new type.
  • You can still drop to raw SQL when the typed API is not enough (prisma.$queryRaw is tagged-template and parameterised).

See Prisma Migrations for the workflow.

Extensions and extras

Enabled in the Postgres container via the init SQL:

  • pgvector — vector column type + similarity operators (<=>, <->). Used by the Hub for semantic search.
  • pg_stat_statements — query stats for slow-query triage.
  • uuid-ossp — UUID generation functions (most UUIDs are generated app-side by Prisma, but a few default-on-insert columns use uuid_generate_v4()).

Operating posture

  • One write path. All writes go through Prisma from hivecfm-core or through the Go Hub’s typed repositories. No direct SQL from production code paths outside those two.
  • Read replicas — not yet. Heavy analytical reads go through Superset which is pointed at the same primary today.
  • Backups — managed by the operator. The dev Docker Compose has no backups; production uses managed Postgres snapshots (Azure Database for PostgreSQL flexible server in the Azure Container Apps target).

Read next

Server ActionsSchema Viewer