Project Data Models

Project Data Models

How a "project" is modelled in each of the three systems that hold one: Track (source of record), Eagle (EA process history), DEMI (the merge of both).

Everything here was read from the models and verified against live data on 2026-08-07. Row counts and examples come from the environments named under Provenance.

System	Store	Projects	Identity
Track	Postgres (patroni-epictrack-db16, db app)	382	projects.id (int)
Eagle	MongoDB, shared epic collection	358	_id (ObjectId)
DEMI	Azure Cosmos (NoSQL API)	382	id (Track id as string)

Rendered versions with full field tables:

• Track project ERD
• Eagle project model
• DEMI project model

---

Track

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

PROJECTS  382
   ├── works  208                      (project_id, NOT NULL)
   │     ├── work_phases  1,541
   │     │     ├── phase_overage_responsibilities   ← only ON DELETE CASCADE in the graph
   │     │     ├── event_configurations  35,042
   │     │     │     ├── events
   │     │     │     └── work_calendar_events  3,523 ── calendar_events
   │     │     └── task_events  451
   │     │           ├── task_event_assignees        → staffs
   │     │           └── task_event_responsibilities → responsibilities
   │     ├── events  5,328
   │     ├── work_issues  146 → work_issue_updates
   │     ├── work_statuses  1,498
   │     ├── work_resources  4
   │     ├── staff_work_roles  715      → staffs, roles
   │     ├── indigenous_works  515      → indigenous_nations
   │     ├── linked_works  16
   │     └╌╌ work_phases via current_work_phase_id — plain int, NO FK
   │
   ├── inspections  0 rows — schema exists, feature unused
   │     ├── inspection_details → inspection_attachments
   │     └── inspection_attendees
   │
   ├╌╌ special_fields  2,926            entity='PROJECT' + entity_id, no FK
   └╌╌ projects_history  766            auto-created by the Versioned mixin

projects carries 26 columns: identity (name, abbreviation unique, description), location (latitude/longitude, address), EA certificate and dates, FTE counts, capital investment, five FKs to code tables (types, sub_types, proponents, regions ×2, project_states), and the audit set.

Gotchas

• Deletes are soft. is_deleted everywhere; find_all() filters on it. Only one real ON DELETE CASCADE exists in the project graph.
• Three integer columns look like FKs and aren't: works.current_work_phase_id, event_configurations.parent_id, work_calendar_events.source_event_id. No constraint, nothing stops them dangling.
• Geography is text. latitude/longitude are String and NOT NULL. No PostGIS, no validation.
• Audit columns are strings holding IDIR GUIDs (a1222c2a…@idir), not FKs to staffs.
• History tables are generated. The Versioned mixin creates <table>_history at mapper-configure time (history.py:51); they appear in no model file.
• epic_guid is never NULL — it is ''. 354 populated, 28 empty string, 0 null. Anything matching on null misses all 28.
• Project has no inspections relationship, only works. Inspections reach the project, never the reverse.
• No document table, and no outbound sync. Grepping the whole repo for demi returns nothing.

---

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 (eagle-api/api/helpers/utils.js:209) merges the active block to the top level at query time. The stored shape is not the API shape — the search API returns 68 flat fields. Anything querying Mongo 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, 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. Typesense needs [lat, lng], so the order is swapped on sync.
• Every model shares one collection. A query without _schemaName crosses types.
• People are denormalised — projectLead (string) sits beside projectLeadId (ObjectId) and can drift.
• trackProjectId exists in the schema but is unpopulated in practice. The Track→Eagle link works in one direction only, via Track's epic_guid.

---

DEMI

DEMI stores no project of its own. Each is Track ⊕ Eagle, computed at seed time and written to Cosmos.

Track  src/data/track_projects_enriched.json — checked in, 382 records, 14 fields
   │                                            NOT an API call. A static file.
   │  join on track.epic_guid = eagle._id
Eagle  GET /api/public/search?dataset=Project — live, 358 projects
   ▼
merged project
   ├── identity     id · trackProjectId · eagleId · sourceSystem · updatedAt
   ├── visibility   read[] copied 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, unindexed

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

Coverage over 382: 348 matched, 28 with an empty epic_guid, 6 with a dangling one (an epic_guid that resolves to no Eagle project; the merge counts this as trackOnlyDanglingGuid).

Gotchas

• isPublished mirrors the ACL, deliberately. It was once track.is_active !== false, which conflated a Track-internal record flag with publication and made 23 projects read isPublished: false while Eagle's ACL made them public. setDocumentPublished 409s against an unpublished parent, so no document could be published under any of them.
• Nothing refreshes the Track snapshot. No cron, no webhook, no script regenerates track_projects_enriched.json; it moves only when someone commits a new one.
• The snapshot carries 14 of Track's 26 columns. capital_investment, ea_certificate, EAC dates, FTE counts, both region ids and the audit columns were dropped at capture — DEMI has never had them.
• sources holds up to four upstreams, not the two the repo writes: wildfire on 381 of 382, nrpti on 189. The deployed seed is ahead of src/merge/project.js.
• Field sets are ragged, 18 to 43. Every Eagle-derived field is optional on roughly a quarter of the corpus.
• updatedAt is seed time, identical across all 382. It records when DEMI last rebuilt, not when a project changed.
• 7 of 382 Track records carry a positive longitude — a dropped minus sign, handled at merge time rather than fixed upstream.

---

Provenance

What	Where	When
Track rows and counts	psql on pod patroni-epictrack-db16-0, db app, namespace c72cba-test	2026-08-07
Eagle rows	GET https://projects.eao.gov.bc.ca/api/public/search?dataset=Project (prod)	2026-08-07
DEMI rows	GET https://demi-api-dev.azurewebsites.net/api/projects	2026-08-07

Note on Track environments: patroni-epictrack-db and patroni-epictrack-db16 exist in c72cba-dev, -test and -prod, but only test was running (1/1); dev and prod are scaled to 0/0. The deployed epictrack-api routes in c8b80a-* returned HTTP 500 on every path including /.