Data Layer and Persistence

Data Layer and Persistence

How Yosemite Crew stores data, and the migration that reshaped it. The decision behind this is ADR-0001; this page is the current-state view.

The system of record: PostgreSQL + Prisma

• Primary database: PostgreSQL, accessed via Prisma ORM. The Prisma schema and migrations live in packages/database (prisma/schema.prisma), the one place the schema is defined.
• Prisma Migrate is the schema source of truth. Schema changes ship as migrations, not hand-edited SQL or ad-hoc console changes.
• Hosting: the managed Postgres runs on Supabase, but the app talks to it as plain Postgres through the DATABASE_URL connection string — there is no Supabase SDK and no Supabase-specific coupling. Swapping the host is a connection-string change.
• Client pattern: a single PrismaClient singleton (guarded against hot-reload duplication in development) is shared across the backend.

The retired store: MongoDB (fully gone)

The backend originally persisted everything in MongoDB via Mongoose. That store is now fully retired - code and infrastructure: PR #1819 (merged 2026-07-18) deleted the dual-write/read-switch scaffold, the Mongo connection code, all remaining Mongoose models, and the mongoose dependency, and the backing MongoDB cluster has since been decommissioned entirely. PostgreSQL + Prisma is the sole system of record. Issue #1818 stays open only for follow-ups.

Historical note: the migration ran entity by entity behind two flags (shouldDualWrite to write both stores, READ_FROM_POSTGRES to flip reads), following dual-write, then read-switch, then Mongo-path removal, with a final Mongo-to-Prisma backfill before the scaffold came out. The full mechanics and trade-offs are in ADR-0001.

Why relational

As the product grew into multi-tenant clinic data with financial records and cross-entity joins (org scoping, RBAC, invoice ↔ appointment ↔ inventory), the document model pushed consistency and tenant-scoped querying into application code. Postgres moves foreign keys, joins, and relational integrity into the database. Full rationale and the trade-offs accepted during the migration (two live stores, non-transactional dual-write, a single global flag): ADR-0001.

Schema shape

The Prisma schema is large and still growing (190+ models, 200+ enums as of 2026-08) covering organizations, companions, appointments, SOAP/vitals/prescriptions, inventory, services/packages, invoices/finance, tasks, forms/documents, and RBAC. Recent additions include the ActivityPub federation tables (APActor and friends, with row-level security), an organization-verification override, breed/age-based husbandry recommendation rules, estimate-to-invoice conversion, and care-reminder opt-out - see prisma/migrations/ for the running history. The per-subsystem Backend deep dives walk the models that matter for each area; the schema file itself is canonical.

Async work and caching

• Jobs: BullMQ (Redis-backed) runs async work — lab syncs, reminders, notifications. See Backend: Realtime, Chat, Jobs.
• Cache / queues: Redis backs the BullMQ job queues and caching. The Socket.IO org-event bus described in Realtime and Notifications is a proposed design, not yet wired. A broader caching strategy is a tracked future-scope item (#1669).
• Multi-tenancy primitive: packages/database/src/tenant.ts implements schema-per-tenant provisioning for the developer platform — see Multi-Tenancy and RBAC and ADR-0004.

Related

• ADR-0001: Postgres + Prisma · Backend Overview · Shared Packages · MongoDB retirement follow-ups (#1818) · Scaffold removal (PR #1819)