Data Model

The platform stores data in PostgreSQL, accessed through Drizzle ORM. Schemas live in the @emofy/db package and are shared by the backend and the tooling.

Two databases

Identity is separated from business data (ADR-001):

A third logical store, the audit log, is a partitioned table set (ADR-018) — MongoDB was decommissioned in favor of partitioned Postgres.

Because the two databases are separate, cross-database joins do not exist — code that needs both (e.g. resolving a member's role while reading core data) reads from each and composes in the service layer. The JWT carries the org/role claims precisely so the hot path doesn't need an auth-DB round trip.

The org-scoped repository pattern

This is the single most important data rule. Tenant isolation is not left to individual queries — it is enforced by a base class (ADR-006):

// A tenant repository extends OrgScopedRepository.
// Reads and writes are automatically constrained to request.orgId,
// and soft-deleted rows (deletedAt) are filtered out.
class TasksRepository extends OrgScopedRepository<typeof tasks> {
  // findMany(), insert(), update() all carry orgId implicitly
}

Raw db.select() against a tenant table is forbidden and caught in review / CI guards. The effect: a developer cannot accidentally leak data across orgs, because the scoping isn't something they remember to add — it's something they'd have to actively bypass.

Prefixed IDs

Primary keys are prefixed nanoids (ADR-007), generated with generateId("entity"):

task_h4n2k9xq7p3m      org_8f2k1d9s0a7b      user_q3m8x...

The prefix makes IDs self-describing in logs, URLs, and error messages, and is validated at the API boundary by @IsPrefixedId(). No database migration is needed to introduce a new prefix — it's an application-level convention.

The dual ledger: org vs. passport

The product thesis shows up directly in the schema (ADR-020). Child activity is recorded in two ledgers:

• the org operational ledger — institution-owned, org-scoped, and removed if the child leaves the institution;
• the passport ledger — parent-owned, portable, and projected from operational events under consent.

Access to passport data is relationship-based (ADR-021): the effective permission is the intersection of role, active enrollment, and consent. This is why a teacher's access to a child evaporates when the child un-enrolls, while the parent's persists across institutions. See Family & passport.

Side effects: the transactional outbox

Durable side effects — sending email, dispatching webhooks, fanning out notifications — are not performed inline in the request. They are written to an outbox in the same transaction as the business change, then picked up by event handlers (ADR-010). This guarantees the side effect happens if and only if the data change committed, and prevents the duplicate-delivery problems that plague inline dispatch.

service.create()  ──┐  (one transaction)
                    ├─▶ INSERT row
                    └─▶ INSERT outbox event
                              │  (later, async)
                              ▼
                        event handler ──▶ webhook / email / notification

In practice a command handler writes the business row and the outbox row in the same transaction, and a separate worker drains the outbox. For example, the messaging conversation handler (modules/core/messaging/commands/handlers/create-conversation.handler.ts):

// 1. Business change + outbox row commit together.
await this.coreDb.db.transaction(async (tx) => {
  await this.conversationRepo.insert(tx, conversation);
  await this.outboxRepo.insert(tx, { type: "channel.created", payload });
});

// 2. A worker later claims pending rows and dispatches them.
const pending = await this.outboxRepo.claimPending(100);

If the transaction rolls back, the outbox row is gone too — so the side effect can never fire for a change that didn't happen.

Migrations & local data

Schema changes go through Drizzle:

pnpm db:generate   # author a migration from schema changes
pnpm db:migrate    # apply migrations
pnpm db:studio     # browse the data (auth :4983, core :4984)

For local seed data and reset recipes, see Local development setup.

Next