Phase C — Data Architecture

Mutable document: list and column definitions change with the provisioning script. Keep in step with infra/provisioning.

1. Entity model

erDiagram
  CONTRACT ||--o{ CALENDAR_ENTRY : "has"
  CONTRACT }o--|| USER : "Contractor"
  CONTRACT }o--|| USER : "ContractManager"
  HOLIDAY }o..o{ CONTRACT : "applies by Country or *"

  CONTRACT {
    text Title "e.g. 001185_2026"
    text ContractCode "indexed, e.g. 1185-Jane-Doe"
    text FrameworkContract "e.g. FWC/2021/907"
    choice Modality "On-site | Off-site | Hybrid"
    person Contractor
    person ContractManager
    date StartDate
    date LatestExecutionDate ">= StartDate"
    number OnSiteTargetDays "0.5 step"
    number OffSiteTargetDays "0.5 step"
    number HoursPerDay "default 8"
    text Country "ISO 3166-1 alpha-2"
    choice Status "Draft | Active | Closed"
    number OnSiteUsedDays "kept by service"
    number OffSiteUsedDays "kept by service"
    number RemainingDays "field customizer target"
    text ConsultantPosition "optional, template column"
    text Vendor "optional, template and agent"
    text Lot "optional, template column"
    text ApproverName "optional, template column"
    text TemplateFields "optional, key-value lines"
  }
  CALENDAR_ENTRY {
    text Title "ContractCode yyyy-MM-dd"
    lookup Contract "indexed"
    date EntryDate "indexed; unique per contractor"
    choice EntryType "OnSiteFull | OnSiteHalf | OffSiteFull | OffSiteHalf"
    text Note "optional"
  }
  HOLIDAY {
    text Title "holiday name"
    date HolidayDate "indexed"
    text Country "ISO alpha-2 or *"
    choice Source "Manual | Agent"
  }

2. SharePoint lists (design §6.2)

All three lists carry a dedicated content type so the web part's list picker can offer only matching lists. All three stay at site-default permissions this iteration (ADR-005); contractors therefore hold Contribute on Contracts too, which is what lets the service keep the used-days columns current.

2.1 Contracts — content type Orpheus Contract

Optional columns (HTML template and contract agent)

Five optional columns feed the HTML timesheet template (ADR-010); one of them, Vendor, is also read by the contract agent (ADR-013). They are not in the main site template (template.xml). Two separate idempotent scripts add them: infra/provisioning/Add-TimesheetTemplateColumns.ps1 (all five) and packages/mcp-contract-calendar/setup/Setup-AgentDemo.ps1 (Vendor only). All are site columns in the group Orpheus Systems with fixed IDs, added to the Orpheus Contract content type with -UpdateChildren.

Both scripts create Vendor with the same ID, so whichever runs first creates it and the other reports it unchanged. Each script stops before any write if a name or ID clashes with a different column. The web part reads the columns tolerantly: on a site without them the values are empty and everything else works. Column details and the template syntax are in the timesheet template guide.

Vendor is free text, so spelling variants are separate vendors; the agent matches a vendor filter case-insensitively. A Vendors list with a lookup stays a later option (ADR-013).

2.2 CalendarEntries — content type Orpheus Calendar Entry

2.3 Holidays — content type Orpheus Holiday

Weekends are not stored; the calendar treats Saturday/Sunday as blocked by default (configurable per web part instance).

⚠️ Open: RemainingDays is described as "a calculated/number column"; the design does not fix which, nor its formula if calculated (the field customizer reads the target and used columns itself, so the column may only need to exist as an anchor).

2.4 Security groups

Contractor and manager roles are not groups; they are derived from the Contractor and ContractManager columns (ADR-005).

3. Derived values

4. Data flows

flowchart LR
  subgraph Browser
    WP[Calendar web part]
    FC[Field customizer]
    FM[Form customizer]
    PDF[PDF builder]
    DS[IDataService]
  end
  subgraph SPO[SharePoint Online lists]
    C[(Contracts)]
    E[(CalendarEntries)]
    H[(Holidays)]
  end
  WP --> DS
  DS -- "getMyContracts (Contractor eq me / ContractManager eq me)" --> C
  DS -- "getEntries / upsertEntry / deleteEntry (batched)" --> E
  DS -- "update OnSiteUsedDays / OffSiteUsedDays" --> C
  DS -- "getHolidays(country, from, to)" --> H
  WP --> PDF
  PDF -- "template columns of one contract; template HTML file" --> SPO
  FC -- "reads item columns only, no extra query" --> C
  FM -- "overlap query: same contractor, intersecting period" --> C
  FM -- "save; recomputed used days when the period changed" --> C
  FM -- "entries of the contract, to recompute used days and count days outside a new period" --> E
  subgraph Server[MCP server, contract agent]
    TOOLS[Six MCP tools]
    DIR[Contract directory]
    SDS[SharePointDataService, same shared-core build]
  end
  TOOLS --> DIR
  TOOLS --> SDS
  DIR -- "contracts by viewer role, with Vendor" --> C
  SDS -- "getEntries / upsertEntry / deleteEntry as the user" --> E
  SDS -- "getHolidays" --> H

The PDF builder reads the five optional template columns of the selected contract and, when the web part has a template path, the template HTML file from a site library, both with the viewer's permissions (ADR-010). The MCP server reaches the same lists with a delegated on-behalf-of token for the calling user (ADR-011, ADR-012); all agent writes go through upsertEntry / deleteEntry, so the ADR-008 invariants and the used-days counters hold for agent writes too.

5. Local fixtures (LocalDataService)

Bundled JSON used in the workbench or when useLocalData is on. Minimum content (design §6.3):

• one active contract;
• one bridge pair with a 2-week overlap;
• half days;
• a holiday set for BG and for *;
• two users (a contractor and a manager) selectable in the property pane.

Writes are in-memory. Fixture content beyond this minimum is left to the builder.

The MCP server uses LocalDataService with the same fixtures in its unit tests and in DATA_MODE=local, with demo vendors attached.

5.1 Seed and demo series on a tenant site

Demo people are passed to the agent setup script as UPN parameters and never committed; vendor names in demo-data.json are fictional.

6. Data classification

⚠️ Open: Retention, deletion and backup requirements are not stated; the design relies on SharePoint Online platform behaviour. GDPR handling of contractor names in a public demo tenant is not addressed.

7. Authoritative sources and keys

8. Query patterns

• Reads use CAML/OData filters on indexed columns (ContractCode, Contract, EntryDate, HolidayDate; on Contracts also Contractor, ContractManager and Status, which the provisioning script indexes).
• Writes are batched (PnPjs v4 batching).
• The field customizer uses the list item's own columns only; no extra query per row.
• The timesheet PDF reads the five template columns of one contract by id (getContractTemplateData, TEMPLATE_DATA_SELECT in shared-core); a 400 response (columns not provisioned) gives empty values.

Contract agent queries (packages/mcp-contract-calendar):

Indexes: Title and Vendor are not indexed. The setup script's startswith filter on Title, and vendor filtering if it moves into OData, will need an index once Contracts grows past the list view threshold.

Known risk: SharePointDataService.upsertEntry deletes the sibling entry (ADR-008) and adds the new one in one batch, but the batch is not transactional. The delete can succeed while the add fails, which leaves the day empty. This affects the web part and the agent; book_days reports such a day as failed. The fix belongs in shared-core (add first, or verify and restore). Recorded as R-AGT-1 in ADR-011.