Technical Design Document

Orion Portal — Architecture Review

Data model review and tech debt inventory for the Orion field service portal. This is an exploratory document — options are surfaced for decision, not prescribed.

Status

Draft — exploring options

Owner

Engineering

Systems

Orion · EncoreTS · PostgreSQL

Version

v0.1 — 2026-07-08

Architecture ReviewData ModelTech Debt

01Purpose & Scope

Orion reached a functional demo state in April 2026. Before building further on top of it, this document reviews the data model and tech stack for issues that will limit scale, maintainability, or developer velocity. The goal is to surface concrete trade-offs and reach alignment on which items to address — and in what order — before committing to a direction.

In scope — Data model integrity and normalization; BFF tech stack (EncoreTS); AI service capability; event system wiring; repository-layer architecture.

Out of scope — Frontend UX changes; new product features; CI/CD hardening (covered separately in the backlog).

02Current State (AS-IS)

Full description at /orion/. Summary for reference:

Layer	Technology	Notes
Frontend	Next.js 16 App Router	Route handlers proxy to BFF — browser never calls backend directly
BFF	EncoreTS	api → service → repository layering; ~25 domains
AI Service	FastAPI (Python)	Deterministic heuristics only — no LLM
Database	PostgreSQL 16 (Cloud SQL)	27 models; Prisma for schema only, pg client at runtime
Events	GCP Pub/Sub (scaffolded)	Local dev uses EventLog table; Pub/Sub not wired in production
File storage	Google Drive	StorageProvider enum also has ONE_DRIVE — not implemented

03Data Model Issues

The following issues were identified from reading database/prisma/schema.prisma directly.

3.1 — Mixed primary key strategies

The schema uses two different ID strategies with no consistent rule:

Models	PK type	Problem
User, Job, Session, Partner, EventLog	String UUID (@db.Uuid)	Good — universally unique, safe to expose in URLs
Organization, Address, Contact, Billing, Market, Pricing, License, Certification, Document, Task, Notifications …	BigInt autoincrement + separate uuid field	Sequential IDs leak row counts and are enumerable; the duplicate uuid field adds redundancy

The BigInt-ID models all carry a redundant uuid field to work around the enumeration problem — but application code then has two different ways to reference the same entity. Decide: migrate BigInt models to UUID PKs, or enforce a rule that only uuid is ever surfaced externally.

3.2 — Flat customer fields on Job

Job embeds customer identity as flat strings: customerName, customerPhone, customerAddress, customerEmail, zipCode, city, state, latitude, longitude. There is no Customer entity.

A customer who has multiple jobs cannot be queried, deduplicated, or updated in one place. If the same customer appears on two jobs, the data is duplicated at the row level. A Customer entity with a FK from Job would fix this — but requires a migration and a decision about how to backfill.

3.3 — Job.subContractor is a plain string

Job.subContractor is a String? — a free-text name — while the authoritative subcontractor entity is Organization, linked via Job.organizationId. Both fields exist on the same model.

Two sources of truth for the same concept on the same row. If organizationId is set, subContractor is redundant and can drift. The string field should be dropped once all jobs have a proper organizationId.

3.4 — Two parallel note models

Notes exist in two separate, incompatible models:

Model	Used for	Issue
`Notes`	Job notes, user notes (NoteType enum: CUSTOMER, SUB_CONTRACTOR, METISS, PARTNER, SOW)	createdBy / updatedBy stored as VarChar name strings — not FK to User
`UserNote`	Org notes, pricing notes, license notes, certification notes, task notes, invoice notes (via join tables)	Used as a polymorphic hub via five separate XxxNote join tables

A single Note model with a polymorphic entity reference (or explicit nullable FKs) would replace both. The current split means note queries, pagination, and search must be implemented twice and can never be unified.

3.5 — InvoiceDetails.emailThreadIds stored as array

InvoiceDetails.emailThreadIds BigInt[] stores related thread IDs as a PostgreSQL array column. A proper join table already exists (EmailThread has an invoiceDetailsId FK), making the array column redundant and a potential source of inconsistency.

Array FK columns cannot be indexed efficiently, cannot enforce referential integrity, and will diverge from the FK-based relation. The array column should be dropped once confirmed unused in application code.

3.6 — Enum values stored as unconstrained strings

Several columns that represent a fixed set of values are typed as String or String? with no database constraint:

Field	Current type	Risk
User.role	String @db.VarChar(60)	Any string accepted — no validation at DB level
User.status	String @default('Active')	Silent drift if application code uses inconsistent casing
User.communicationPreference	String @db.VarChar(40)	Unknown valid values — not documented in schema
Job.jobStatus	String @db.VarChar(60)	ACTIVE_JOB_STATUSES array in code is the source of truth, not DB
Job.serviceType / serviceSubType	String?	Inconsistent values across jobs cause filter bugs
Partner.status	String @default('Active')	No constraint

PostgreSQL enums or CHECK constraints should enforce these at the DB level. Prisma native enums (like the existing Direction, NoteType, StorageProvider) are the right pattern to follow.

3.7 — Organization.crewCapacity default bug

crewCapacity String? @default("crew_capacity") — the default value is the string literal "crew_capacity", which appears to be the column name copied by mistake. Any org created without an explicit value gets this nonsense default.

3.8 — Billing stores bank credentials as plain text

Billing.routingNumber and Billing.accountNumber are stored as plain Text columns with no encryption. If Cloud SQL is compromised or a query is logged, these values are exposed in cleartext.

Bank account numbers are sensitive financial data. Options: encrypt at the application layer before writing, use PostgreSQL pgcrypto column encryption, or delegate to a payment vault (e.g. Stripe, Finix) and store only a token.

04Tech Debt Inventory

4.1 — jobs/repository.ts is a 1,373-line god file

All job query logic — list, filter, pagination, CSV export, notes, status updates — is in a single file. It uses dynamic SQL string concatenation with a growing number of filter flags, making it difficult to test in isolation and a frequent source of merge conflicts.

Candidate for extraction into focused query builders or domain-specific repository classes (JobListRepository, JobWriteRepository, JobExportRepository).

4.2 — Encore dependency leaked into repository layer

jobs/repository.ts imports APIError from encore.dev/api (line 12). The repository layer is supposed to be framework-agnostic; Encore types should only appear in the api.ts layer. This was flagged as a blocking issue in the April 2026 integration test branch.

Move APIError throws up to the service layer. Repository functions should return typed results or throw plain Error instances, letting the service map them to framework errors.

4.3 — Duplicate market / markets domains

subcontractor-management/ contains two separate API modules: market/ (57 lines) and markets/ (172 lines). Both appear to operate on the same Market model. This is likely an accidental duplication from a partial refactor.

Audit which routes are actively called by the frontend. Consolidate into one module and delete the other.

4.4 — GCP Pub/Sub event system not wired in production

The event publisher abstraction supports two drivers: EVENT_PUBLISHER_DRIVER=local (writes to the EventLog table) and gcp-pubsub. Local is the default and appears to be what runs in production. Pub/Sub is scaffolded but unused.

Either commit to Pub/Sub and wire it, or simplify by removing the abstraction and writing directly to EventLog. A dead abstraction that exists for a feature not yet used adds cognitive overhead without delivering value.

4.5 — AI service uses deterministic heuristics, not an LLM

The /jobs/insights endpoint in the FastAPI service uses rule-based heuristics. This limits the quality and adaptability of insights as the job data grows in volume and variety.

Gemini (already used in Utility Bill AI and Savings Insight) is the natural candidate for an LLM-backed insights service. The question is whether the current heuristics are good enough for the near term, or whether the upgrade should be prioritised now.

4.6 — No frontend test infrastructure

The frontend (frontend/web/) has no test infrastructure configured. Backend (Vitest) and AI service (pytest) are covered; the frontend has none. Given that Next.js route handlers proxy all backend calls, integration-level tests of those handlers would catch regressions at the BFF boundary.

05Design Options

Three non-mutually-exclusive directions are available. They can be sequenced independently.

Option A — Incremental data model cleanup Preferred starting point

Address the schema issues in priority order without changing the tech stack. Each item is a targeted migration:

Fix crewCapacity default (trivial, no data risk)
Drop InvoiceDetails.emailThreadIds array column
Convert unconstrained string enums to Prisma enums
Remove Job.subContractor string field (after backfill)
Unify Notes + UserNote into a single model
Introduce a Customer entity and migrate Job customer fields
Decide on UUID-only PKs and migrate BigInt models if agreed

Each step is independently releasable and reversible. Starting here gives the highest confidence return before tackling the larger architectural decisions below.

Option B — BFF tech stack review

EncoreTS introduces framework-specific types that have already leaked into the repository layer. Its compile step, opinionated service model, and vendor-specific tooling add friction. Options:

Option	Trade-off
Keep EncoreTS, fix the leakage	Lowest disruption. Fix APIError leak, enforce layer boundaries strictly. Encore's service model remains load-bearing.
Migrate BFF to plain Fastify or Hono	Removes vendor lock-in, simplifies local dev, improves testability. Significant migration effort (~25 domains).
Collapse BFF into Next.js route handlers	Eliminates a runtime hop. Only viable if job query complexity can be managed in Next.js; the 1,373-line repository argues against this.

Option C — LLM-backed AI insights

Replace the FastAPI heuristics service with a Gemini-backed service (consistent with Utility Bill AI and Savings Insight). The existing /jobs/insights API contract can be preserved — only the implementation changes.

Consideration	Detail
Cost	Per-request LLM cost vs. zero-cost heuristics — needs volume estimate
Latency	LLM adds 500ms–2s per job detail page load — consider async pre-computation
Quality	Heuristics are brittle as job data grows; LLM adapts without code changes
Consistency	Gemini already in platform — no new vendor relationship required

06Open Questions

Question	Decision needed from
Keep EncoreTS long-term, or plan a migration?	Engineering lead
Normalise Job customer fields into a Customer entity — worth the migration cost?	Product + Engineering
Migrate BigInt PKs to UUID across all models?	Engineering lead
Upgrade AI insights to Gemini — now or after data model cleanup?	Product + Engineering
Wire Pub/Sub in production — or simplify to EventLog only?	Engineering lead
Billing credentials — application-layer encryption or delegate to a payment vault?	Engineering + Legal/Compliance

07Success Criteria

To be defined once options are selected. Indicative criteria per option:

Option A: All schema enum fields use Prisma enums with DB constraints; Customer entity exists; no duplicate note models; no array FK columns; zero crewCapacity rows with the string literal default.
Option B (keep EncoreTS): No Encore types in repository layer; all repository functions return plain typed results; layer boundary enforced by lint rule.
Option C: Job detail page surfaces Gemini-generated insight; p95 latency on insight fetch is within acceptable threshold (TBD); heuristic service decommissioned.

Technical Design Document · v0.1 · 2026-07-08Owner: Engineering