Database architecture

Scope

Sprint 2 uses PostgreSQL 17 for local development and Prisma ORM 7 as the schema, migration, and type-safe client layer. PostgreSQL runs locally through Docker Compose; no hosted database or deployment environment is configured.

Boundaries

• packages/database/prisma/schema.prisma is the model source of truth.
• packages/database/prisma/migrations is immutable migration history.
• packages/database/prisma/seed.ts creates deterministic system authorization data.
• packages/database/src/prisma.ts owns the PostgreSQL pool and Prisma singleton.
• packages/database/src/activity-log.ts is the append-oriented event-writing foundation.
• Applications consume @submicron/database; domain packages should not issue ad hoc database queries.

Prisma 7 uses prisma.config.ts for its connection URL, migration path, and seed command. Prisma Client is generated into ignored source output and recreated during installation, build, or explicit generation.

Data model

Better Auth owns the semantics of User, Session, Account, and Verification. The internal authorization model adds Role, Permission, UserRole, and RolePermission. ActivityLog captures structured security and future business events. ApprovalRequest is only a generic envelope; no domain approval policy is implemented.

Sprint 3 adds Company, Contact, Lead, Opportunity, FollowUp, and CrmActivity. Leads and opportunities share CrmPipelineStage; the CRM models do not implement quote generation.

Sprint 4 adds Audit, AuditLoadItem, AuditFinding, and AuditCalculationSnapshot. Audits can link optionally to CRM records. Load assessment inputs and calculated totals are stored for current views, while snapshots preserve calculation history. The audit schema does not implement quotes, BOQs, integrations, AI, or production deployment.

All primary identifiers are UUIDs. Join-table constraints prevent duplicate grants. Foreign-key deletion behaviour is explicit. Activity records preserve history when an actor is removed by setting the actor reference to null.

Migration rules

Create migrations with pnpm db:migrate -- --name <name> against a disposable local database. Review generated SQL. Never rewrite a migration after it has been shared. Apply committed migrations in non-development environments with pnpm db:migrate:deploy when deployment work is authorized.

Security and operations

Connection strings belong only in untracked environment files or an approved secret manager. Activity metadata must not contain passwords, session tokens, full sensitive records, or uncontrolled request bodies. Backup, restore, retention, connection pooling, and production database access remain undecided.