Project Data Models

Project Data Models

How a project is modelled in each of the three systems that hold one: Track, Eagle and DEMI.

System	Store	Identity	Role
Track (epictrack-api)	Postgres, table projects	projects.id (int)	Master project registry
Eagle (eagle-api)	MongoDB, shared epic collection	_id (ObjectId)	EA process history
DEMI (eagle-demi)	Cosmos DB for NoSQL	id (Track id as string)	Central data store, merge of both

---

Track

Relational, epictrack-api/src/api/models/. Two tables carry a foreign key to projects:

PROJECTS
   ├── works                            (project_id, NOT NULL)
   │     ├── work_phases
   │     │     ├── phase_overage_responsibilities   ← only ON DELETE CASCADE in the graph
   │     │     ├── event_configurations
   │     │     │     ├── events
   │     │     │     └── work_calendar_events ── calendar_events
   │     │     └── task_events
   │     │           ├── task_event_assignees        → staffs
   │     │           └── task_event_responsibilities → responsibilities
   │     ├── events
   │     ├── work_issues → work_issue_updates
   │     ├── work_statuses
   │     ├── work_resources
   │     ├── staff_work_roles           → staffs, roles
   │     ├── indigenous_works           → indigenous_nations
   │     ├── linked_works
   │     └╌╌ work_phases via current_work_phase_id — plain int, NO FK
   │
   ├── inspections                      schema exists, feature unused
   │     ├── inspection_details → inspection_attachments
   │     └── inspection_attendees
   │
   ├╌╌ special_fields                   entity='PROJECT' + entity_id, no FK
   └╌╌ projects_history                 auto-created by the Versioned mixin

projects carries 28 columns: 22 of its own plus the six on BaseModelVersioned (created_by, created_at, updated_by, updated_at, is_active, is_deleted). Its own set is identity (name, description, abbreviation unique), location (latitude, longitude, address), EA certificate and dates, FTE counts, capital investment, epic_guid, and five foreign keys to code tables (types, sub_types, proponents, regions twice, project_states).

Gotchas

• Deletes are soft. is_deleted everywhere, filtered in find_all_projects(). One real ON DELETE CASCADE exists in the project graph.
• Three integer columns look like foreign keys and are not: works.current_work_phase_id, event_configurations.parent_id, work_calendar_events.source_event_id. Nothing stops them dangling.
• Geography is text. latitude and longitude are String and NOT NULL. No PostGIS, no validation.
• Audit columns are strings holding IDIR GUIDs, not foreign keys to staffs.
• History tables are generated. The Versioned mixin creates <table>_history at mapper-configure time, so they appear in no model file.
• epic_guid is nullable but is populated with '' rather than NULL. A query matching on null misses every unlinked project.
• Project has no inspections relationship, only works. Inspections reach the project, never the reverse.
• No document table, and no outbound sync. Nothing in the repository references DEMI.

---

Eagle

One MongoDB document per project in the shared epic collection, discriminated by _schemaName: 'Project'.

The defining feature: nearly every descriptive field lives inside three near-identical sub-documents, one per EA Act, and currentLegislationYear names the live one.

Project
  ├── currentLegislationYear   "legislation_2002" — points at one of the three
  ├── legislationYearList
  ├── legislation_1996 ┐
  ├── legislation_2002 ├─ 67 fields each, same shape (projectDataDefinition)
  ├── legislation_2018 ┘
  ├── envelope: trackProjectId · region · pins · links · featuredDocuments
  │             projectCAC · cacMembers · read/write/delete · _addedBy …
  └── read: []   the ACL — 'public' present = publicly visible

A $switch on currentLegislationYear in eagle-api/api/helpers/utils.js merges the active block to the top level at query time. The stored shape is not the API shape. Anything querying MongoDB directly must pick the block itself or it reads nothing. This is the single biggest trap in the model.

Gotchas

• Publication is the ACL. There is no isPublished on a project; visibility is 'public' ∈ read.
• Dates are inconsistently typed. decisionDate and dateUpdated are real Dates; dateAdded, dateCommentsOpen and dateCommentsClosed are String holding a stringified JS Date. duration and intake.investment are numbers stored as text.
• centroid is a bare [lng, lat] array, not GeoJSON. No swap on sync: Azure AI Search's Edm.GeographyPoint takes [longitude, latitude], which is what MongoDB already stores.
• Every model shares one collection. A query without _schemaName crosses types.
• People are denormalised. projectLead (string) sits beside projectLeadId (ObjectId) and can drift.
• trackProjectId is declared with a unique sparse index but is unpopulated in practice. The Track–Eagle link runs the other way, through Track's epic_guid.

---

DEMI

A DEMI project is a merge, computed in src/merge/project.js and written to Cosmos.

Track  src/data/track_projects_enriched.json — checked in, 382 records, 13 fields
   │                                            NOT an API call. A static file.
   │  join on track.epic_guid = eagle._id
Eagle  raw project document, from the seed or from the eagle-api push
   ▼
merged project
   ├── identity     id · trackProjectId · eagleId · sourceSystem · updatedAt
   ├── visibility   read[] resolved from Eagle's ACL
   │                isPublished = read.includes('public')
   ├── 9 fields     Track wins, but only where Track has a value
   ├── 31 fields    Eagle only — EA process, contacts, CAC
   ├── centroid     GeoJSON Point, from Track lat/lng or Eagle centroid
   └── sources{}    raw upstream payloads, not API surface

Track wins for name, description, projectType, projectSubType, proponentName, projectState, abbreviation, address and isActive (TRACK_PRECEDENCE). Written as an explicit map, not a spread — {...eagle, ...track} would let an empty Track field blank a populated Eagle one.

An Eagle project with no Track counterpart is kept, not dropped. It takes id of eagle-<eagleId>, a null trackProjectId and sourceSystem: 'eagle', which is what flags it for follow-up.

Gotchas

• isPublished mirrors the ACL. It is never an independent signal, in either merge path.
• A missing or empty read[] on an Eagle record resolves to the privileged role set, not to public. Absence of evidence is not evidence of consent.
• Nothing refreshes the Track snapshot. No cron, no webhook and no script regenerates track_projects_enriched.json; it moves only when someone commits a new one.
• The snapshot carries 13 of Track's 28 columns. capital_investment, ea_certificate, the EAC dates, FTE counts, both region ids and the audit columns were dropped at capture.
• sources holds the raw upstream payloads for re-merge traceability: track, eagle, and a wildfire aggregate patched in by the wildfire sync. Only allowlisted keys are served through the API. The eagle-api push preserves every block it does not itself rebuild.
• Field sets are ragged. Every Track-precedence and Eagle-only field is copied only when the source has a value, so two projects rarely carry the same key set.
• updatedAt is merge time. It records when DEMI last rebuilt or last accepted a push, not when the project changed upstream.
• Seven of the 382 Track records carry a positive longitude — a dropped minus sign, handled at merge time rather than fixed upstream.

---

Counts

What	Value	Source
Track projects in the snapshot	382	src/data/track_projects_enriched.json
Snapshot records with an empty epic_guid	28	same file
Public Eagle projects, prod	358	GET /api/public/search?dataset=Project, measured 2026-08-26
Snapshot epic_guid against prod Eagle	348 matched, 28 unlinked, 6 dangling	measured 2026-08-26

Related

• Eagle DEMI — the DEMI service and the eagle-api push contract
• API Architecture — eagle-api access control
• Architecture Overview