# Solution Architecture > **Public release profile:** Version 0.13.0 produces a 62-indicator full review catalogue and a 61-indicator production catalogue across ten themes after release tests pass, with 41 governed zero-observation configuration dependencies. Utility costs remain the sole review-only catalogue item pending City authorization or an applicable open-data release. Property-tax rates, the City tourism-asset layer, four Market Activity & Development indicators and all other authorized-source records are production-eligible. ## Implemented MVP decision The complete MVP runs on an Ubuntu Contabo VPS with Docker Compose. It implements the required separation: ```text public sources -> ingestion -> raw storage -> validated Parquet/JSON/catalogue fragments -> PostgreSQL or reviewed static release -> FastAPI -> website ``` The website never calls Statistics Canada and never loads a bundled JavaScript data object. It requests the published catalogue and indicator payloads only from the versioned API. This prevents source availability, schema changes, or slow downloads from affecting public page requests. The catalogue uses one metadata-driven interface for income, affordability, population, housing, community-stability, labour, workforce, education, skills, infrastructure, transportation, connectivity, economy, business-climate, tourism and market-activity/development measures. Configuration declares dimensions, measures, units, numeric precision, geography coverage, periods, source, summary cards and drill-down structure; adding an indicator does not require another page template. The same generic payload contract serves all 62 review indicators. Repeated high-cardinality breakdown definitions use backwards-compatible compact metadata templates, while each observation stores only its numeric arrays; JavaScript and FastAPI resolve the same public payload. The public-source layer is intentionally heterogeneous. Statistics Canada supplies standardized CMA/CA series and CSD Census/projection snapshots. Utility tariffs and statutory tax rates are municipal or provincial, so they pass through source-specific adapters, retain source units/effective dates/scenario assumptions, and publish only their real service/jurisdiction coverage. Alberta property-tax records use source-qualified municipal tax-jurisdiction IDs rather than an unsupported Statistics Canada crosswalk. The application does not label a tariff scenario as observed average spending, apply a City rate to an entire CMA, or treat CSD, CMA, tax jurisdiction and custom trade area as interchangeable. Economy adapters follow the same boundary rule. Statistics Canada employer-business locations at CSD level are not firms, jobs or City licences. CMA GDP, experimental business openings/closures, wages and insolvency filings are published only as metropolitan context, not as City-boundary or City-defined trade-area values. The wage source publishes medians and tax-filer counts but no average, so the portal does not infer an average. Exact City, licensed-market and approved survey concepts remain zero-observation governed dependencies until their publication gates pass. Tourism follows an equally strict publication boundary. The production indicator retains all 19 points from the City Tourism open-data layer under the City Open Data Licence v2 and calculates Haversine distance from the official 2021 Lethbridge CSD representative point. It does not infer a City-approved major-asset ranking, Southern Alberta coverage, routed distance, travel time, resident access or current operating status. Exact local spend and hotel occupancy require authorized licensed extracts; four City/fee-for-service event totals require complete recurring registers, exact counting and attendance rules, privacy/reuse approval and revision controls. Those six requirements plus regional routed asset proximity remain seven zero-observation dependencies. See the [Tourism source register](CANADIAN_PUBLIC_DATA_SOURCES_TOURISM.md), [Attachment 1 response](ATTACHMENT_1_TOURISM_RESPONSE.md) and [KPI 079–085 index](TOURISM_KPI_INDEX.md). Market Activity & Development publishes four source-native indicators: `building-permit-value-by-sector`, `number-of-building-permits`, `land-title-transfers-count-value` and `major-project-values`. City permit records provide unique issued `FOLDERNUMBER` counts and estimated construction values by reviewed sector for complete years 2021–2025; incomplete 2026 records are excluded, and value is not actual investment. Alberta OGL municipality series provide annual land-title transfer count/value for source `CSDUID 4802012` from 2008–2025 and an approximate-December inventory of announced or under-construction projects above $5 million from 2012–2025. Transfers are broader than arms-length home sales, and major-project inventory value is neither exhaustive nor annual expenditure. `property-assessment-totals` and `assessment-distribution` remain two zero-observation dependencies until a City-approved roll extract reconciles mutually exclusive classes to the exact total. See the [source register](CANADIAN_PUBLIC_DATA_SOURCES_MARKET_ACTIVITY_DEVELOPMENT.md), [Attachment 1 response](ATTACHMENT_1_MARKET_ACTIVITY_DEVELOPMENT_RESPONSE.md) and [KPI 086–091 index](MARKET_ACTIVITY_DEVELOPMENT_KPI_INDEX.md). Stage 3 adds a build-time cartographic pipeline for official Statistics Canada CMA/CA boundary files. Simplified geometry and representative coordinates are self-hosted, filtered through the solution API, and rendered without third-party tiles, geocoding, tracking, or runtime map calls. PNG and PDF reports are generated in the browser from the same loaded public payload; the application does not upload report data to a conversion service. Stage 4 separates anonymous public reads from privileged administration. FastAPI validates Microsoft Entra v2 access tokens against tenant-specific OIDC discovery and JWKS, requires a delegated API scope and approved client, and evaluates one explicit permission for every administration route. The loopback preview uses fixed fictional personas and an isolated cookie/CSRF session so evaluators can exercise RBAC without presenting it as City authentication. Accessibility, browser-contract, load and session harnesses produce reviewable JSON evidence. ## System diagram ```mermaid flowchart LR SRC["Statistics Canada bulk/SDMX sources"] --> JOB["Python + Node.js ingestion and source adapters"] LOCAL["Provincial open data + reviewed municipal schedules"] --> JOB JOB --> RAW[("Private raw storage\nDocker volume now / S3-compatible target")] JOB --> DQ["Validation and normalization"] DQ --> PARQ[("Private processed storage\nParquet / JSON / catalogue fragments")] DQ --> PG[("PostgreSQL\ngeographies + gender values + metadata")] PARQ --> SEED["Reviewed static release seed"] PG --> API["FastAPI /api/v1"] SEED --> API API --> CADDY["Caddy TLS reverse proxy"] WEB["Static accessible website"] --> CADDY CADDY --> USER["Public browser"] subgraph VPS["Contabo Ubuntu VPS - Docker Compose"] JOB DQ PG API CADDY WEB end ``` Raw and processed objects are stored outside the VPS filesystem when `RAW_STORAGE_DRIVER=s3`. The currently deployed subpath profile deliberately sets `RAW_STORAGE_DRIVER=filesystem` and uses private Docker volumes. The full-stack profile supports private S3-compatible buckets, but no purchased Object Storage capacity is claimed without provisioning evidence. ## Component responsibilities | Component | MVP choice | Responsibility | |---|---|---| | Scheduler | APScheduler | Runs source-specific cron jobs in the `America/Edmonton` time zone and prevents overlapping runs | | Ingestion | Python + HTTPX; Node.js streaming builders | Streams official bulk/SDMX files over HTTPS and runs source-specific CMA/CA, CSD and municipal/provincial adapters with schema, licence and coverage controls | | Raw layer | Private Docker volume in the deployed MVP; S3-compatible object storage supported | Preserves each source response with retrieval metadata and SHA-256 checksum | | Processing | Python and Node.js streaming transforms | Normalizes indicator values and breakdowns, preserves source concepts and units, validates totals/components and rejects incomplete or semantically incompatible combinations | | Processed layer | Private Docker volume in the deployed MVP; S3-compatible object storage supported | Stores source-specific clean CMA/CA, CSD and authorized municipal datasets independently of the serving database | | Serving layer | PostgreSQL 16 | Stores geographies, published gender values, distributions, versions, source metadata, run history, quality results, and audit events | | API | FastAPI | Serves read-only versioned endpoints, ETags, cache headers, and health checks | | Edge/web | Caddy + static HTML/CSS/JS | Terminates TLS, adds security headers, serves the website, and proxies `/api/*` | ## Why PostgreSQL and DuckDB PostgreSQL is the serving database because the first KPI and the expected catalogue fit comfortably in a relational system and require reliable transactions, metadata, auditability, and predictable operations. The raw comprehensive file is never queried by the website; it is transformed into compact serving records first. ClickHouse is unnecessary for the MVP's volume and would add an operational service. Athena and Azure SQL would move the serving layer outside the requested Contabo deployment. They remain future options if measured query volume or organizational standards justify them. ## Publication transaction 1. A run record is created with a UUID and `running` state. 2. The Statistics Canada T1FF source ZIPs are written unchanged to raw storage. 3. Required observations, dimensions, suppression, arithmetic reconciliation, units, geography scope and publication licence status are checked. 4. A compact annual CMA/CA JSON dataset is written to processed storage; the current T1FF transform does not write Parquet. 5. The API payload receives a deterministic SHA-256 dataset version. 6. PostgreSQL can publish the new version in one transaction and retain prior versions. 7. Built-in public routes currently resolve the reviewed static seed before database-configured rows. Updating the public KPI therefore also requires catalogue rebuild, validation and redeployment. 8. A failed run records its error and never replaces the last reviewed public dataset. ## API contract | Endpoint | Purpose | |---|---| | `GET /api/health/live` | Process liveness | | `GET /api/health/ready` | Database readiness | | `GET /api/v1/indicators` | Published indicator catalogue | | `GET /api/v1/geographies?indicator={id}` | Indicator-specific geographies and available years | | `GET /api/v1/map?indicator={id}` | Simplified self-hosted CMA/CA boundary geometry filtered to the indicator's published coverage | | `GET /api/v1/indicators/{id}?geography={DGUID}&year={YYYY}&gender={value}&view={measure}` | Generic indicator payload for all supported controls | | `GET /api/v1/sources` | Source and refresh metadata | | `/api/admin/indicators` | Guarded draft inventory and configuration lifecycle | | `/api/admin/indicators/{id}/import` | Validated long-format CSV import into a private draft | | `/api/admin/indicators/{id}/preview` | Public-compatible rendering of an unpublished draft | | `/api/admin/indicators/{id}/publish` and `/retire` | Explicit public-snapshot transitions | | `GET /api/admin/audit` | Material administration events | | `GET /api/admin/session` | Validated principal, roles, effective permissions and token timing without returning a credential | The public response includes an ETag based on the dataset version and a five-minute cache policy. API documentation is exposed in non-production environments only. ## Scheduling policy Two Income & Affordability jobs are registered: municipal Census Profile individual income at 03:15 Mountain Time and annual T1FF individual income at 03:45, both monthly. Household income, person-level LIM-AT, Gini, utility costs and Alberta property-tax rates are not scheduler-registered. Weekly checks during property-tax adoption windows, monthly annual-workbook checks and weekly utility metadata checks are target production policies, not current automation claims. Source adapters require reviewer approval before publication when a layout, formula, concept or licence changes. The checked-in 62-indicator catalogue is a reproducible review seed; the 61-indicator production artifact applies the utility republication gate. The annual individual-income workflow has scheduled source ingestion, validated processed JSON and PostgreSQL persistence, but the public built-in payload still requires seed rebuild and redeployment. Population, demographic, housing, community-stability, labour, commuting, post-secondary, time-use, education, infrastructure, economy/business, Tourism, Market Activity & Development and Alberta property-tax builders likewise produce reviewed static snapshots. Production acceptance should move every system indicator to one transactional published-snapshot serving path so a successful scheduled refresh becomes visible without rebuilding an image. Infrastructure source adapters retain Census commuting, City street AADT, airport status/suppression and Alberta renewable-generation semantics; the ISED broadband adapter is validated but not serialized into the size-constrained release catalogue. Nine infrastructure requirements remain fail-closed dependencies pending exact municipal, carrier, routing or City-approved inputs. Economy retains 12 zero-observation dependencies pending exact City/trade-area definitions, City administrative data, licensed commercial-market inputs or an approved survey repository. Tourism publishes one open-data City-inventory/straight-line indicator and retains seven fail-closed dependencies. Market Activity & Development publishes four source-native indicators and retains two assessment dependencies under the boundaries above. The release contains 41 governed zero-observation configuration dependencies in total. Builders and validators run before each theme merger; Market Activity & Development merges after Tourism, the production filter runs last, and `npm run test:market-activity` precedes `npm run test:production` and the full release gate. The v0.13.0 full catalogue is 477,850,368 bytes, leaving 2,149,632 bytes (about 2.15 MB, 0.45%) below the 480,000,000-byte release ceiling; the production artifact is 477,835,391 bytes and leaves 2,164,609 bytes. Catalogue sharding or lazy per-indicator payload loading is the next architecture step before any further broad or high-cardinality payload. The current CSD indicators also require a separate CSD boundary adapter before they can claim polygon-map coverage; the existing self-hosted boundary map is CMA/CA only. Alberta tax jurisdictions deliberately have no CSD/CMA polygon until a validated crosswalk and matching boundary source exist. The education-proximity and Tourism-asset KPIs use official CSD representative points for their calculations but do not claim a CSD polygon layer, resident access, network distance or travel time. Source-qualified Market records likewise do not gain a map polygon from a name match. ## Security boundaries Only ports 80 and 443 are published by the application stack. PostgreSQL and the scheduler are connected to an internal Docker network and have no host port. Current raw and processed Docker volumes are private to the host; if S3-compatible storage is enabled, its buckets must be private and credentials must be injected through the deployment environment. Containers use `no-new-privileges`, the API filesystem is read-only, and Caddy supplies TLS and restrictive browser security headers. The public MVP contains public economic data and requires no public user account. The local administration review environment listens only on loopback, uses opaque `HttpOnly`/`SameSite=Strict` sessions, enforces idle and absolute expiry, requires CSRF for every mutation and logout, and applies five server-side persona roles. These are fictional identities, not City authentication. Production FastAPI rejects local authentication and fails closed unless `AUTH_MODE=entra` and canonical tenant, audience, client allow-list, scope and OIDC settings are present. It validates RS256 signature, signing key, issuer, audience, tenant, delegated scope, approved client, immutable object identifier and token timing before evaluating route permissions. Live City app registration, BFF session handling, Conditional Access/MFA, role assignment, revocation and tenant UAT remain deployment acceptance items. ## Stage 4 identity and authorization boundary ```mermaid flowchart LR PUBLIC["Public visitor"] -->|"Anonymous HTTPS GET /api/v1"| EDGE["Caddy / WAF"] STAFF["City staff browser"] -->|"OIDC authorization code + PKCE"| BFF["Production administration BFF\nplanned deployment component"] BFF -->|"Redirect and token exchange"| ENTRA["City Microsoft Entra tenant"] BFF -->|"Bearer access token for this API"| EDGE EDGE --> API["FastAPI"] API -->|"OIDC discovery and JWKS"| ENTRA API --> RBAC["App role -> permission policy"] RBAC --> PG[("PostgreSQL drafts, snapshots and audit")] ``` The recommended production BFF keeps Entra tokens server-side and gives the browser only a `Secure`, `HttpOnly`, appropriately scoped `SameSite` session cookie with CSRF protection, identifier rotation, idle expiry and absolute expiry. The implemented API is the bearer-token resource server; it is not represented as the BFF or as proof of an active City Conditional Access policy. ## Availability boundary One VPS is a single failure domain and is not high availability. Docker restart policies, health checks, off-host object storage, and database backups make the service recoverable, but they do not provide clustering or automatic host failover. A production SLA requiring high availability needs at least two application nodes, an external load balancer, and a replicated or managed database. ## Recommended production architecture ```mermaid flowchart TB USERS["Residents, investors and City staff"] --> FRONT["Canadian edge CDN / WAF / load balancer"] FRONT --> WEB1["Stateless web/API instance - zone 1"] FRONT --> WEB2["Stateless web/API instance - zone 2"] WEB1 --> DB[("Zone-redundant managed PostgreSQL")] WEB2 --> DB WEB1 --> OBJ[("Canadian object storage")] WEB2 --> OBJ DB --> DR[("Canadian-region recovery copy")] OBJ --> DR WEB1 --> OBS["Central logs, metrics, alerts and SIEM"] WEB2 --> OBS ``` This is the proposed contract-production evolution, not the topology of the one-VPS demonstration. Exact Azure services, regions, service tiers, availability commitments, recovery objectives and certification evidence require final City approval and pricing. See [Stage 3 Demonstration Outputs](DEMONSTRATION_OUTPUTS.md), [Stage 4 Security and Quality Acceptance](QUALITY_ACCEPTANCE_STAGE4.md), [Backend Security](../backend/SECURITY.md), [Contabo Deployment Architecture](CONTABO_DEPLOYMENT_ARCHITECTURE.md), and [VPS Runbook](VPS_RUNBOOK.md).