M03 — Multi-Tenant Data Layer

One-line goal: the complete data model — building, floor, zone, camera, edge gateway, sensor, analysis event, alert, alert action, audit log — is in Postgres with row-level security enforcing tenant boundaries and a hash-chained audit trail capturing every mutation.

After M03, any service can write multi-tenant data correctly without re-implementing isolation logic, and any read is guaranteed to be tenant-scoped by the database itself.

Tracks involved

• Backend — primary. Schema migrations, RLS policies, service classes for each entity, audit interception.
• Cloud Infra — verifies CMEK on Cloud SQL, ensures backups + PITR, sets up read replica for analytics if M03 is when we want it.
• Frontend — building / camera / sensor CRUD screens for admin users (read-only views for operators).

Dependencies

• M01 complete (Postgres instance up; PostGIS installed; packages/contracts/ has DTOs).
• M02 complete (auth + RBAC; the audit-log interceptor pattern; the app.tenant_id setting on connections).

Deliverables

1. Schema — building / floor / zone / camera / edge gateway / sensor

Migrations create the full set:

• building — UUID PK, name, address, city, region, building_type, location (PostGIS GEOGRAPHY Point, SRID 4326), total_floors, total_cameras (denormalized for fast dashboards), status (active/onboarding/offboarded), metadata JSONB, created_at, updated_at, deleted_at.
• floor — UUID PK, building_id FK, floor_number, name, elevation_m, area_sqm.
• zone — UUID PK, floor_id FK, name, zone_type (enum: lobby, corridor, stairwell, prayer_hall, kitchen, restricted, public, dorm, mechanical, etc.), max_occupancy, is_restricted, boundary (PostGIS GEOMETRY Polygon), metadata JSONB.
• camera — UUID PK, zone_id FK, edge_gateway_id FK, name, rtsp_url, protocol (enum: onvif, rtsp, proprietary), resolution_w, resolution_h, fps, position (PostGIS GEOGRAPHY Point), orientation_deg, fov_deg, is_ptz, status (online/offline/degraded), last_seen_at, metadata JSONB.
• edge_gateway — UUID PK, building_id FK, hostname, ip_address, hardware_model, gpu_model (nullable), last_heartbeat, status, software_version, certificate_fingerprint.
• sensor — UUID PK, zone_id FK, sensor_type (enum: fire_panel, smoke_detector, heat_detector, sprinkler_flow, access_control, pa_zone, environmental, other), protocol (enum: bacnet, modbus, mqtt, rest_api, contact_closure, proprietary), model, manufacturer, last_reading JSONB, last_reading_at, status (online/offline/fault), metadata JSONB.
• floor_plan — UUID PK, floor_id FK (unique), image_url, width_px, height_px, bounds (PostGIS GEOMETRY Polygon), rotation_deg.
• zone_geometry — UUID PK, zone_id FK (unique), boundary (Polygon SRID 4326), centroid (Point), area_sqm. GIST index on boundary.
• camera_calibration — UUID PK, camera_id FK, status (calibrating/active/paused), frames_collected, cluster_count, anomaly_threshold, sensitivity_mult, started_at, completed_at, is_active. Only one active per camera enforced by a partial unique index.
• calibration_cluster — UUID PK, calibration_id FK, cluster_index, centroid (FLOAT[128]).

Indexes per the digest’s schema (e.g. idx_building_status WHERE deleted_at IS NULL, idx_zone_boundary USING GIST(boundary), idx_camera_zone, idx_camera_gateway, etc.).

2. Schema — AI events and alerts

• analysis_event — partitioned by analyzed_at (monthly). Columns per the contracts package’s AnalysisEvent. PK (id, analyzed_at). Indexes: (camera_id, analyzed_at DESC), (event_type, analyzed_at DESC), partial (anomaly_score DESC, analyzed_at DESC) WHERE anomaly_score > 0.3.
• escalation_record — UUID PK, analysis_event_id FK, prompt_template_id, llm_model, input_tokens, output_tokens, llm_response JSONB, threat_detected, false_alarm_likelihood, reasoning, processing_ms, escalated_at.
• frame_snapshot — UUID PK, analysis_event_id FK, storage_url (GCS or MinIO key), size_bytes, width_px, height_px, captured_at, expires_at. References blob in object storage; auto-deletes after retention.
• alert — UUID PK, analysis_event_id FK (nullable; alarms can create alerts without an AI event), alarm_event_id FK (nullable), building_id FK, zone_id FK, alert_type, severity, status (new/acknowledged/investigating/escalated/resolved/false_alarm), confidence, title, description, frame_snapshot_url, resolved_by FK to user, created_at, acknowledged_at, resolved_at. Index (building_id, status, created_at DESC), (zone_id, created_at DESC), partial active.
• alert_action — UUID PK, alert_id FK, user_id FK, action_type (acknowledge / dismiss / confirm / escalate / add_note / reassign / resolve / reopen), notes, performed_at.
• alarm_event — UUID PK, source_system_id (which adapter), alarm_type, zone_id FK (after mapping), severity, state, occurred_at, protocol_attributes JSONB.

3. Schema — model registry

• model_version — UUID PK, model_name, version, model_type (enum: object_detection, anomaly_autoencoder, crowd_density, fire_smoke, llm, other), framework, file_hash, file_size_mb, is_active, performance_notes, deployed_at, retired_at.
• prompt_template — UUID PK, template_id, version, scenario_type (general / smoke_verify / crowd_assess / restricted_zone / night_anomaly), system_prompt, instructions, response_schema JSONB, is_active, created_at, retired_at.

4. Schema — alert rules and system config

• alert_rule — UUID PK, building_id FK (nullable for global), zone_id FK (nullable), rule_type, condition JSONB (the threshold logic), severity, is_enabled, cooldown_seconds.
• system_config — key VARCHAR PK, value JSONB, description, updated_at, updated_by FK to user.

5. Row-level security policies

Applied to every multi-tenant table. The pattern:

ALTER TABLE building ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON building
  USING (
    id IN (
      SELECT building_id FROM user_building 
      WHERE user_id = current_setting('app.user_id', true)::uuid
    )
    OR current_setting('app.role', true) = 'super_admin'
  );

Equivalent policies on every descendant table — floor, zone, camera, sensor, edge_gateway, floor_plan, zone_geometry, camera_calibration, calibration_cluster, analysis_event (via camera_id → zone_id → floor_id → building_id), alert, alert_action, alarm_event, escalation_record, frame_snapshot, alert_rule.

The application sets SET LOCAL app.user_id = '<uuid>' and SET LOCAL app.role = '<role>' on every transaction. A dedicated wiqaia_app Postgres role respects RLS; a separate wiqaia_admin role can bypass it (used only by migrations and audit jobs, never application code).

6. Repository / service classes in NestJS

A consistent shape: one *.module.ts, one *.service.ts, one *.controller.ts, one set of DTOs in *.dto.ts (validated by zod from packages/contracts/). For each entity:

• BuildingService / BuildingController — CRUD + queries.
• FloorService / FloorController — scoped to a building.
• ZoneService / ZoneController.
• CameraService / CameraController.
• EdgeGatewayService / EdgeGatewayController.
• SensorService / SensorController.
• AlertService / AlertController (active alerts feed, alert details, alert actions).
• AuditService (read-only; for the audit-log UI).

Each service uses TypeORM or Prisma (decision at M02; default Prisma for type safety) with a tenant-scoped database session that sets app.user_id / app.role before any query.

7. Migration pipeline

• All schema changes go through migrations in apps/api/migrations/.
• Schema diffs reviewed in PRs.
• CI runs migrations against a throwaway Postgres in a Docker container with PostGIS extension.
• Production migrations gated behind manual approval.
• A migration that adds a new table must include the corresponding RLS policy. Linter / pre-commit hook to enforce this.

8. Backup & DR posture

• Cloud SQL automated backups daily, 30-day retention.
• Point-in-time recovery (PITR) enabled — granularity to the second within the last 7 days.
• HA standby replica in another zone within Dammam.
• Async read replica for analytics queries (used by BigQuery export and dashboard read-heavy queries).
• Backup-restore drill scheduled monthly; first one in M10.

9. Audit hook integration

The AuditInterceptor from M02 captures every mutation. With the multi-tenant data layer now real, each captured row’s tenant_id is set correctly, and the audit log itself is tenant-scoped for queries (a building manager sees only their building’s audit entries; auditor sees all).

10. Admin UI screens for building / camera management

• List buildings.
• Create a building (with location-on-map picker — links to M07 for the 3D twin).
• Edit a building’s floors + zones.
• Register cameras (one-by-one or CSV import).
• Register sensors (fire panels, etc.).
• Assign cameras to edge gateways.
• See online/offline status across all entities.

These exist to make M04+ deliverable; they’re admin tools, not the operator surface.

Verification

1. Schema migrates clean. A fresh Postgres instance, post-migration, has every table, index, RLS policy, and seed data documented.
2. Cross-tenant query returns empty. A query as user U (assigned to Building A) executed with app.user_id = U.id cannot see Building B’s records, even by joining through analysis_event → camera → zone → floor → building. Confirmed via a dedicated integration test that explicitly tries to escape the tenant boundary.
3. PostGIS spatial queries work. ST_DWithin(zone.boundary, camera.position, 50) returns expected cameras for a given zone (used by M07’s spatial correlation). Test with seeded data.
4. Audit log captures every CRUD. Creating a building, updating a camera, deleting a zone — each produces an AuditLogEntry with the correct entity_type, entity_id, and changes. Test seeds 50 mutations and verifies the chain.
5. Soft delete behaves correctly. Deleting a building sets deleted_at; subsequent reads (without an explicit “include deleted” flag) skip the row; the row is still queryable by a special admin endpoint for compliance.
6. Partitioning works. Inserting an analysis_event for next month creates (or uses) the appropriate monthly partition. A partition-management job exists and is tested.
7. CMEK is verified on Cloud SQL. The instance’s encryption shows our KMS key, not Google’s default key.
8. Backups restore. Restore the latest backup to a sibling database; query for known seed data; confirm correctness.
9. PITR works. Take a snapshot of the database state at T; insert garbage; restore to T-1 minute; confirm the garbage is gone.
10. Read replica reflects data. Insert a building on the primary; query the replica after replication lag (typically <5 s); see the row.

Risks

Open questions

• Prisma vs TypeORM. Decide M02 → M03 boundary. Default Prisma.
• Soft-delete cascade rules. When a building is soft-deleted, do its cameras / zones / alerts also get marked? Default: yes, all descendants soft-deleted; admin endpoint can resurrect.
• Audit-log retention beyond 12 months — where. GCS Coldline with object retention locks for 7 years.
• Building offboarding procedure. Deleting all tenant data including key revocation. Detailed runbook in M10.

Exit criteria

All 10 verification items pass. Cross-tenant isolation test specifically signed off by a second engineer. M03 sign-off entry in docs/plan/COMPLETION_LOG.md.