Orion Data Model — Execution Plan

01Task summary

02DM-01 — Fix crewCapacity default value Low risk

Problem

Organization.crewCapacity has @default("crew_capacity") in database/prisma/schema.prisma. The default is the column name string, not a meaningful value. Any organization created without an explicit value gets the nonsense string "crew_capacity" stored in the database.

The application treats crewCapacity as a nullable number converted via Number(row.crewCapacity ?? 0) in backend/api/subcontractor-management/organization/repository.ts:98. The bad default will produce NaN when parsed.

Schema change

File: database/prisma/schema.prisma — model Organization

// BEFORE
crewCapacity  String?  @default("crew_capacity")

// AFTER
crewCapacity  String?

Migration SQL

Add to the Prisma migration file generated by npx prisma migrate dev --name fix-crew-capacity-default:

-- Null out any rows that have the bad default
UPDATE "organization"
SET "crewCapacity" = NULL
WHERE "crewCapacity" = 'crew_capacity';

-- Remove column default
ALTER TABLE "organization" ALTER COLUMN "crewCapacity" DROP DEFAULT;

Files to verify (no code changes expected)

• backend/api/subcontractor-management/organization/repository.ts — confirm Number(row.crewCapacity ?? 0) handles null correctly (it does via ?? 0)
• backend/api/subcontractor-management/organization/service.ts — confirm Zod schema z.coerce.number().optional() at line 48 is unaffected

Acceptance criteria

• No rows in organization where "crewCapacity" = 'crew_capacity'
• schema.prisma has no @default on crewCapacity
• npm run verify passes in the orion repo

03DM-02 — Add UserRole, UserStatus, CommunicationPreference enums Low risk

Problem

User.role, User.status, and User.communicationPreference are stored as unconstrained VarChar columns. The valid values are already defined as TypeScript types in packages/shared-types/src/index.ts but are not enforced at the database level. Any string can be written to these columns without error.

Valid values (from shared-types)

Schema change

File: database/prisma/schema.prisma

// ADD these enum definitions (alongside existing Direction, NoteType, StorageProvider)

enum UserRole {
  EXECUTIVE            @map("Executive")
  ADMIN                @map("Admin")
  PROJECT_COORDINATOR  @map("Project Coordinator")

  @@schema("public")
}

enum UserStatus {
  ACTIVE     @map("Active")
  INACTIVE   @map("Inactive")
  SUSPENDED  @map("Suspended")

  @@schema("public")
}

enum CommunicationPreference {
  EMAIL        @map("Email")
  TEXT_MESSAGE @map("Text Message")

  @@schema("public")
}

// UPDATE model User fields:
// BEFORE
role                    String                @db.VarChar(60)
status                  String                @default("Active") @db.VarChar(20)
communicationPreference String                @db.VarChar(40)

// AFTER
role                    UserRole
status                  UserStatus            @default(ACTIVE)
communicationPreference CommunicationPreference

Migration SQL

Generate with npx prisma migrate dev --name add-user-enums. The migration must:

-- 1. Create the enum types
CREATE TYPE "UserRole" AS ENUM ('Executive', 'Admin', 'Project Coordinator');
CREATE TYPE "UserStatus" AS ENUM ('Active', 'Inactive', 'Suspended');
CREATE TYPE "CommunicationPreference" AS ENUM ('Email', 'Text Message');

-- 2. Cast columns to enum types (will fail if any invalid value exists)
ALTER TABLE "User"
  ALTER COLUMN "role" TYPE "UserRole" USING "role"::"UserRole",
  ALTER COLUMN "status" TYPE "UserStatus" USING "status"::"UserStatus",
  ALTER COLUMN "communicationPreference" TYPE "CommunicationPreference"
    USING "communicationPreference"::"CommunicationPreference";

-- 3. Update default
ALTER TABLE "User" ALTER COLUMN "status" SET DEFAULT 'Active'::"UserStatus";

SELECT DISTINCT "role" FROM "User";
SELECT DISTINCT "status" FROM "User";
SELECT DISTINCT "communicationPreference" FROM "User";

Files to update after schema change

Prisma re-generates database/generated/prisma/index.d.ts automatically via npx prisma generate. The TypeScript types in packages/shared-types/src/index.ts remain as-is — they are still the application-layer source of truth. No application code changes are needed because the Prisma client will now return the enum values as the same string literals.

• Run npx prisma generate after migration to regenerate the client
• Run npm run typecheck to confirm no type errors introduced

Acceptance criteria

• PostgreSQL has UserRole, UserStatus, CommunicationPreference enum types
• Inserting an invalid value (e.g. 'SuperAdmin') into User.role raises a DB error
• npm run verify passes

04DM-03 — Add JobStatus enum Medium risk

Problem

Job.jobStatus is a VarChar(60). The valid values are defined in packages/shared-types/src/index.ts as JobStatus. Because there is no DB constraint, jobs with invalid or misspelled statuses can be written silently. The filter logic in backend/api/jobs/repository.ts relies on exact string matching — a bad value causes a job to disappear from all views.

Valid values (from shared-types)

"New" | "Lead" | "Assigned" | "Scheduled" | "Rescheduled"
| "In Partner Review" | "Completed" | "Cancelled"
| "On Hold" | "Rejected" | "Revisit Needed" | "Closed"

Schema change

// ADD enum
enum JobStatus {
  NEW                @map("New")
  LEAD               @map("Lead")
  ASSIGNED           @map("Assigned")
  SCHEDULED          @map("Scheduled")
  RESCHEDULED        @map("Rescheduled")
  IN_PARTNER_REVIEW  @map("In Partner Review")
  COMPLETED          @map("Completed")
  CANCELLED          @map("Cancelled")
  ON_HOLD            @map("On Hold")
  REJECTED           @map("Rejected")
  REVISIT_NEEDED     @map("Revisit Needed")
  CLOSED             @map("Closed")

  @@schema("public")
}

// UPDATE model Job:
// BEFORE
jobStatus  String  @db.VarChar(60)

// AFTER
jobStatus  JobStatus

Migration SQL

-- 1. Verify no unknown values exist before migrating
SELECT DISTINCT "jobStatus" FROM "Job"
WHERE "jobStatus" NOT IN (
  'New','Lead','Assigned','Scheduled','Rescheduled',
  'In Partner Review','Completed','Cancelled',
  'On Hold','Rejected','Revisit Needed','Closed'
);
-- Must return 0 rows. If not, fix data before proceeding.

-- 2. Create enum
CREATE TYPE "JobStatus" AS ENUM (
  'New','Lead','Assigned','Scheduled','Rescheduled',
  'In Partner Review','Completed','Cancelled',
  'On Hold','Rejected','Revisit Needed','Closed'
);

-- 3. Cast column
ALTER TABLE "Job"
  ALTER COLUMN "jobStatus" TYPE "JobStatus"
  USING "jobStatus"::"JobStatus";

Application code to update

The raw-SQL repository at backend/api/jobs/repository.ts passes status values as string parameters to PostgreSQL queries. PostgreSQL will accept string literals that match enum values, so existing query code should continue to work. However, verify these specific patterns:

• Any ANY($n::text[]) casts on jobStatus — change to ANY($n::"JobStatus"[])
• backend/api/lib/utils/job-status.ts — ACTIVE_JOB_STATUSES array remains in TypeScript; no change needed

Acceptance criteria

• PostgreSQL has JobStatus enum type with all 12 values
• Writing an invalid status to a job raises a DB error
• All existing jobs have a valid status (verified before migration)
• npm run verify passes

05DM-04 — Add Status enum (Partner, Organization) Low risk

Problem

Partner.status and Organization.status are unconstrained strings. The shared-types package defines Status = "Active" | "Inactive".

Schema change

// ADD enum (reuse across Partner and Organization)
enum EntityStatus {
  ACTIVE    @map("Active")
  INACTIVE  @map("Inactive")

  @@schema("public")
}

// UPDATE model Partner:
// BEFORE
status  String  @default("Active") @db.VarChar(20)
// AFTER
status  EntityStatus  @default(ACTIVE)

// UPDATE model Organization:
// BEFORE
status  String?
// AFTER
status  EntityStatus?

Migration SQL

CREATE TYPE "EntityStatus" AS ENUM ('Active', 'Inactive');

ALTER TABLE "Partner"
  ALTER COLUMN "status" TYPE "EntityStatus"
  USING "status"::"EntityStatus";

-- Organization.status may be NULL; cast only non-null values
ALTER TABLE "organization"
  ALTER COLUMN "status" TYPE "EntityStatus"
  USING "status"::"EntityStatus";

Acceptance criteria

• EntityStatus enum exists in PostgreSQL
• Partner.status and Organization.status are enum-typed
• npm run verify passes

06DM-05 — Remove APIError from jobs/repository.ts Low risk

Problem

backend/api/jobs/repository.ts line 12 imports APIError from encore.dev/api. The repository layer must be framework-agnostic; Encore types must only appear in api.ts handler files. This coupling was identified as a blocker in the April 2026 integration test branch (test/repository-integration-tier1).

Change

File: backend/api/jobs/repository.ts

1. Remove the import { APIError } from "encore.dev/api" import.
2. Find every throw new APIError(...) in jobs/repository.ts and replace with throw new Error(...). The service layer (jobs/service.ts) must catch these and re-throw as APIError where needed.
3. Update backend/api/jobs/service.ts to wrap repository calls and map plain Error to APIError with appropriate HTTP status codes.

Acceptance criteria

• grep -rn "encore.dev" backend/api/*/repository.ts returns no results
• grep -rn "encore.dev" backend/api/*/repository.ts returns no results
• All existing repository-layer tests pass
• npm run verify passes

07DM-06 — Migrate emailThreadIds array to FK-based join Medium risk

Problem

InvoiceDetails.emailThreadIds is a BigInt[] array column that stores IDs of related EmailThread rows. A proper FK already exists in the other direction: EmailThread.invoiceDetailsId. The array column is actively used by backend/api/email-threads/repository.ts (lines 141, 301–303) with ANY(d.email_thread_ids) and array_append(email_thread_ids, ...).

The array column cannot enforce referential integrity, cannot be efficiently indexed for range queries, and can diverge from the FK relation on EmailThread.invoiceDetailsId.

Migration plan (two-phase)

Phase 1 — Backfill and dual-write (one PR):

1. Backfill EmailThread.invoiceDetailsId from the array. For every row in invoice_details, expand email_thread_ids and set the FK:

   UPDATE "email_threads" t
   SET "invoice_details_id" = d.id
   FROM "invoice_details" d
   WHERE t.id = ANY(d.email_thread_ids)
     AND t."invoice_details_id" IS NULL;

2. Update backend/api/email-threads/repository.ts to use the FK for reads instead of the array. The write path (lines 301–303) should set both the FK and the array during dual-write, so rollback is safe.
3. Verify FK-based queries return identical results as array-based queries across all records.

Phase 2 — Drop the array column (separate PR after Phase 1 is in production):

1. Remove dual-write — write only to EmailThread.invoiceDetailsId.
2. Schema change: remove emailThreadIds from model InvoiceDetails in schema.prisma.
3. Migration SQL:

   ALTER TABLE "invoice_details" DROP COLUMN "email_thread_ids";

Files to update

• backend/api/email-threads/repository.ts — lines 141 (read) and 301–303 (write)
• backend/api/tests/email-threads/repository.test.ts — line 92 (test assertion on array SQL)
• database/prisma/schema.prisma — InvoiceDetails model (Phase 2 only)

Acceptance criteria

• After Phase 1: all email threads linked to invoice details have a non-null invoiceDetailsId FK
• After Phase 2: email_thread_ids column does not exist in invoice_details
• All email thread tests pass at each phase

08DM-07 — Deprecate Job.subContractor string field Medium risk

Problem

Job.subContractor is a String? free-text name for the subcontractor. The authoritative subcontractor entity is Organization, linked via Job.organizationId. Both exist on the same row. All queries in backend/api/jobs/repository.ts use COALESCE(o."name", j."subContractor"), meaning the string is the fallback when no organizationId is set.

Migration plan (two-phase)

Phase 1 — Data audit (prerequisite, not a code PR):

1. Run against production to understand current state:

   -- Jobs with a string subContractor but no organizationId FK
   SELECT COUNT(*) FROM "Job"
   WHERE "subContractor" IS NOT NULL
     AND "subContractor" != ''
     AND "organization_id" IS NULL;
   
   -- Jobs with both set — check for mismatches
   SELECT j."jobId", j."subContractor", o."name"
   FROM "Job" j
   JOIN "organization" o ON o.id = j."organization_id"
   WHERE j."subContractor" IS NOT NULL
     AND j."subContractor" != ''
     AND j."subContractor" != o."name"
   LIMIT 50;

2. If the first query returns 0 rows: all jobs with a subcontractor already have an organizationId — proceed to Phase 2 directly.
3. If it returns rows: create or match Organization records for those jobs, set organizationId, then proceed to Phase 2.

Phase 2 — Remove the string field:

1. Schema change: remove subContractor String? from model Job in schema.prisma.
2. Update backend/api/jobs/repository.ts: replace all COALESCE(o."name", j."subContractor") with o."name". Remove the "subContractor" write at line 1040.
3. Update packages/shared-types/src/index.ts: remove subContractor from JobSortField union type and jobSortFields array (lines 153, 173).
4. Migration SQL:

   ALTER TABLE "Job" DROP COLUMN "subContractor";

Acceptance criteria

• Phase 1 audit query returns 0 rows before Phase 2 begins
• grep -rn "subContractor" backend/api/jobs/ returns only references to the Organization join, not the column
• Job list and sort by subcontractor works correctly using o."name"
• npm run verify passes

09DM-08 — Encrypt Billing credentials Blocked — decision needed

Problem

Billing.routingNumber and Billing.accountNumber are stored as plain Text in PostgreSQL. If Cloud SQL is compromised or query logs are exposed, bank account numbers appear in cleartext.

Options — choose one before executing

10DM-09 — Add @unique to EmailThread.uuid and EmailMessage.uuid Low risk

Problem

Every model that carries a uuid field with @default(uuid()) should mark it @unique. Two models are missing the constraint:

Schema change

// File: database/prisma/schema.prisma

// model EmailThread — BEFORE
uuid  String  @default(uuid()) @db.Uuid
// AFTER
uuid  String  @unique @default(uuid()) @db.Uuid

// model EmailMessage — BEFORE
uuid  String  @default(uuid()) @db.Uuid
// AFTER
uuid  String  @unique @default(uuid()) @db.Uuid

Migration SQL

-- Verify no duplicates exist before adding constraints
SELECT uuid, COUNT(*) FROM "email_threads" GROUP BY uuid HAVING COUNT(*) > 1;
SELECT uuid, COUNT(*) FROM "email_messages" GROUP BY uuid HAVING COUNT(*) > 1;
-- Both must return 0 rows.

ALTER TABLE "email_threads"
  ADD CONSTRAINT "email_threads_uuid_key" UNIQUE ("uuid");

ALTER TABLE "email_messages"
  ADD CONSTRAINT "email_messages_uuid_key" UNIQUE ("uuid");

Acceptance criteria

• email_threads_uuid_key unique constraint exists in PostgreSQL
• email_messages_uuid_key unique constraint exists in PostgreSQL
• Inserting a duplicate UUID into either table raises a DB error
• npm run verify passes

11DM-10 — Fix crewCapacity column type (String? → Int?) Low risk

Problem

Organization.crewCapacity is typed String? in the schema but is always treated as a number in application code: Number(row.crewCapacity ?? 0) in backend/api/subcontractor-management/organization/repository.ts:98. The mismatch means invalid values (e.g. "ten") are accepted at the DB level and silently produce NaN at runtime. The Zod schema in service.ts:48 uses z.coerce.number().optional() — the coercion exists precisely because the column is incorrectly typed.

Schema change

// File: database/prisma/schema.prisma — model Organization

// BEFORE
crewCapacity  String?

// AFTER
crewCapacity  Int?

Migration SQL

-- Verify all remaining values are numeric before casting
SELECT "crewCapacity" FROM "organization"
WHERE "crewCapacity" IS NOT NULL
  AND "crewCapacity" !~ '^[0-9]+$';
-- Must return 0 rows.

ALTER TABLE "organization"
  ALTER COLUMN "crewCapacity" TYPE INTEGER USING "crewCapacity"::INTEGER;

Application code to update

• backend/api/subcontractor-management/organization/repository.ts:98 — remove Number() coercion; value will already be number | null
• backend/api/subcontractor-management/organization/repository.ts:36 — change DbOrganization.crewCapacity: string | null to number | null
• backend/api/lib/db/types.ts:262 — same type update on crewCapacity: string | null
• frontend/web/components/subcontractors/subcontractor-tabs.tsx:83 —crewCapacity?: number | undefined already correct; verify String(organization.crewCapacity) at line 327 still works
• backend/api/subcontractor-management/organization/service.ts:48 —z.coerce.number().optional() can be simplified to z.number().int().nonnegative().optional()

Acceptance criteria

• crewCapacity column is integer type in PostgreSQL
• Inserting a non-integer string raises a DB error
• All existing organization tests pass
• npm run verify passes

12DM-11 — Store financial amounts as Decimal, not String Medium risk

Problem

Three monetary amount fields are stored as VarChar/String:

Storing money as strings prevents correct sorting (alphabetic vs. numeric), range queries (WHERE amount > 1000), aggregation (SUM, AVG), and makes it possible to store invalid values like "TBD" or "$1,200".

Schema change

// File: database/prisma/schema.prisma

// model InvoiceDetails — BEFORE
invoiceAmount          String?  @map("invoice_amount") @db.VarChar(120)
customerInvoiceAmount  String?  @map("customer_invoice_amount") @db.VarChar(120)

// model InvoiceDetails — AFTER
invoiceAmount          Decimal? @map("invoice_amount") @db.Decimal(12, 2)
customerInvoiceAmount  Decimal? @map("customer_invoice_amount") @db.Decimal(12, 2)

// model Pricing — BEFORE
subContractorPrice  String?  @map("sub_contractor_price")

// model Pricing — AFTER
subContractorPrice  Decimal? @map("sub_contractor_price") @db.Decimal(12, 2)

Migration SQL

-- 1. Audit for non-numeric values (fix data before migrating)
SELECT id, "invoice_amount", "customer_invoice_amount"
FROM "invoice_details"
WHERE "invoice_amount" IS NOT NULL
  AND "invoice_amount" !~ '^[0-9]+(.[0-9]+)?$';

SELECT id, "sub_contractor_price" FROM "pricing"
WHERE "sub_contractor_price" IS NOT NULL
  AND "sub_contractor_price" !~ '^[0-9]+(.[0-9]+)?$';
-- Both must return 0 rows before proceeding.

-- 2. Cast columns
ALTER TABLE "invoice_details"
  ALTER COLUMN "invoice_amount" TYPE DECIMAL(12,2)
    USING "invoice_amount"::DECIMAL(12,2),
  ALTER COLUMN "customer_invoice_amount" TYPE DECIMAL(12,2)
    USING "customer_invoice_amount"::DECIMAL(12,2);

ALTER TABLE "pricing"
  ALTER COLUMN "sub_contractor_price" TYPE DECIMAL(12,2)
    USING "sub_contractor_price"::DECIMAL(12,2);

Acceptance criteria

• All three columns are DECIMAL(12,2) in PostgreSQL
• Inserting "TBD" raises a DB error
• Invoice list sort by amount sorts numerically, not alphabetically
• npm run verify passes

13DM-12 — Store JSON payloads as Jsonb, not Text/String Low risk

Problem

Two fields store serialised JSON as plain text columns:

PostgreSQL's native Jsonb type validates that the value is well-formed JSON at write time, enables GIN indexing on specific keys, and allows in-DB querying with ->> / @> operators. Plain Text accepts any string, including malformed JSON.

Schema change

// File: database/prisma/schema.prisma

// model EventLog — BEFORE
payloadJson  String

// model EventLog — AFTER
payloadJson  Json

// model SavedSearch — BEFORE
searchCriteria  String  @map("search_criteria") @db.Text

// model SavedSearch — AFTER
searchCriteria  Json  @map("search_criteria")

Migration SQL

-- Validate existing values are well-formed JSON before casting
-- (will throw if any row contains invalid JSON)
ALTER TABLE "EventLog"
  ALTER COLUMN "payloadJson" TYPE JSONB USING "payloadJson"::JSONB;

ALTER TABLE "saved_searches"
  ALTER COLUMN "search_criteria" TYPE JSONB USING "search_criteria"::JSONB;

Application code to update

• backend/api/lib/events/publisher.ts — confirm the payload is passed as a JS object, not a pre-serialised string. With Json type, Prisma handles serialisation automatically; remove any manual JSON.stringify() before write.
• backend/api/saved-searches/repository.ts — same: remove manual JSON.stringify on write and JSON.parse on read if present.

Acceptance criteria

• payloadJson and search_criteria columns are jsonb type in PostgreSQL
• Writing a non-JSON string to either column raises a DB error
• npm run verify passes

14DM-13 — Add missing indexes on Job Low risk

Problem

The Job table has indexes only on jobStatus and dateAssigned. Every job list query in backend/api/jobs/repository.ts also filters and sorts by partnerId, userId, and organizationId. The most common query pattern — partner-scoped active jobs — has no covering index. As job volume grows, these will produce sequential scans.

Schema change

// File: database/prisma/schema.prisma — model Job

// ADD to existing @@index block:
@@index([partnerId])
@@index([userId])
@@index([organizationId])
@@index([partnerId, jobStatus])   // covers the most common list query

Migration SQL

-- All are CONCURRENTLY so they don't lock the table in production
CREATE INDEX CONCURRENTLY IF NOT EXISTS "Job_partnerId_idx"
  ON "Job"("partnerId");

CREATE INDEX CONCURRENTLY IF NOT EXISTS "Job_userId_idx"
  ON "Job"("userId");

CREATE INDEX CONCURRENTLY IF NOT EXISTS "Job_organizationId_idx"
  ON "Job"("organization_id");

CREATE INDEX CONCURRENTLY IF NOT EXISTS "Job_partnerId_jobStatus_idx"
  ON "Job"("partnerId", "jobStatus");

Acceptance criteria

• All four indexes exist in PostgreSQL (\d "Job" confirms)
• EXPLAIN ANALYZE on a partner-filtered job list query shows an index scan, not a sequential scan
• npm run verify passes

15Out of scope (future documents)