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 near-identical sub-documents, one per Act, and currentLegislationYear names the live one.

Project
  ├── currentLegislationYear   "legislation_2002" — points at one of the blocks
  ├── legislationYearList
  ├── legislation_1996 ┐
  ├── legislation_2002 │
  ├── legislation_2018 ├─ 67 fields each, same shape (projectDataDefinition)
  ├── legislation_2025 ┘
  ├── 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.

Legislation registry

LEGISLATIONS in eagle-api/api/helpers/constants.js lists every Act, keyed by year. The project schema, the $switch, the centroid check on save and the POST and PUT year handling all read it. LEGISLATION_KEYS, DEFAULT_LEGISLATION_YEAR and LOCKED_KEYS are derived from it.

Year	Block	label	Flags
1996	legislation_1996	1996 Environmental Assessment Act
2002	legislation_2002	2002 Environmental Assessment Act	isDefault
2018	legislation_2018	2018 Environmental Assessment Act
2025	legislation_2025	Building Canada Act	locked

Property	Effect
label	Written to the block's legislation field on POST and PUT. eagle-public matches on this string
isDefault	The year a POST without legislationYear uses, and the $switch fallback. Exactly one entry has it
locked	PUT and publish never move a project off this Act

Locked Acts

The admin forms know only the B.C. Acts, so saving a project from them would move a project off a locked Act. eagle-api refuses and writes nothing:

Call	Refused when	Status
PUT /api/project/{id}	The project's current Act is locked and legislationYear names another year	409
PUT /api/project/{id}/publish	The project's current Act is locked and legislationYear names another year	409
PUT /api/project/{id}/publish	legislationYear is not the current year and the project has no content under it (year missing from legislationYearList, or the block has no name)	409
PUT /api/project/{id}	legislationYear is not in the registry	404
PUT /api/project/{id}/publish	legislationYear is not in the registry	404

A request that omits legislationYear, or names the current year, is not refused. The check runs again at write time, so a project that moves under a locked Act between the read and the write is also refused. POST with an unknown legislationYear answers 400.

Adding an Act

1. eagle-api: add an entry to LEGISLATIONS in api/helpers/constants.js.

2. eagle-api: name the year in the project POST and publish descriptions in api/swagger/swagger.yaml. A test fails until the year appears there.

3. eagle-public: add an entry to ACTS in src/app/utils/legislation.ts:

   • year and label. The label is identical to the eagle-api one.
   • href: the Act's full text.
   • stages: detailed draws the ten to-scale 2018 stages, phases draws the List phase rows, none draws no assessment progress.
   • phaseRowsYear, optional: the year of the List phase rows to use when the Act has none of its own.

   detailed is built for the 2018 stages only: DETAILED_STAGES and DETAILED_PHASES in src/app/pages/project/assessment-stages.ts are the 2018 model. Another B.C. Act that needs a to-scale view needs its own stage model there. Without one, use phases.

4. eagle-demi: nothing. The merge finds blocks by the pattern legislation_YYYY. See eagle-demi wiki Sync-Architecture.

5. Check the index count on the shared collection. Each Act block adds 5 indexes and MongoDB allows 64 per collection.

6. If the Act needs List rows, seed them separately. The registry does not create them.

eagle-admin has per-year forms and is not covered by the registry.

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.
• projectLeadId and responsibleEPDId are optional on POST /project and PUT /project/{projId}. A missing, null or empty value is stored as null. A value that is not a valid id answers 400 and nothing is written. Clearing an id on update also clears its name string (projectLead or responsibleEPD). proponent is still required on create. eagle-admin cannot open a project where exactly one of the two ids is null; both null works.
• 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.
• An empty or blank-only read[] on an Eagle record is stored as ['sysadmin']. A missing one is stored at level 2 (staff). Neither becomes public. See Eagle DEMI for the ladder.
• 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