DATA_MODEL — reporting-service

Sibling: DOMAIN_MODEL · APPLICATION_LOGIC · SYNC_CONTRACT · SECURITY_MODEL

Strategic anchors: 06 Data Models · 02 §6 Storage · standards/NAMING §6

reporting-service persists to the platform-shared Cloud SQL Postgres instance under the schema reporting. Multi-tenancy uses the platform-default shared schema + tenant_id Row-Level Security pattern (ADR-0002). All writes go through the transactional outbox (04 §6). Generated artifacts live in GCS; analytics fact reads are served by BigQuery (owned by analytics-service) and per-service read-replicas (Cloud SQL).

---

1. Identity strategy

Every aggregate identifier is a ULID with a service-specific prefix per NAMING §6.

Aggregate	Prefix	Type alias
ReportTemplate	tpl_rep_	ReportTemplateId
TemplateVersion	tpv_	TemplateVersionId
Report	rep_	ReportId
ReportRun	run_	ReportRunId
ReportSchedule	sch_	ReportScheduleId
ReportSubscription	sub_	ReportSubscriptionId
ReportFilter	flt_	ReportFilterId
ExportArtifact	art_	ExportArtifactId
RegulatorySubmission	reg_	RegulatorySubmissionId

Branded TypeScript types are declared in DOMAIN_MODEL §1. The repository layer accepts only branded types; raw strings are rejected at the from* factory.

The new prefixes (tpl_rep_, tpv_, rep_, run_, sch_, sub_, flt_, art_, reg_) are added to the canonical registry in standards/NAMING §6 in the same PR.

---

2. Storage placement

Store	What lives here
Cloud SQL Postgres (reporting schema)	Templates, versions, reports, runs, schedules, subscriptions, filters, artifact metadata, regulatory submissions, outbox, inbox dedupe
GCS (gs://melmastoon-reports-<region>)	Rendered artifacts (PDF/XLSX/CSV), regulatory receipts
GCS (gs://melmastoon-reports-regulatory-<region>)	Regulatory artifacts with object-lock retention (10y)
Memorystore (Redis)	Hot read caches (rep:run:*, rep:runs:*:recent, rep:tenant:*:branding); short-TTL keys
SQLite (Electron desktop)	Subscribed reports + last 30 runs per template per device + cached small artifacts (≤ 5 MB)
Firestore (tenants/{tid}/devices/{did}/sync)	Per-device sync cursors, owned by sync-service
BigQuery (events_regulated, events_operational)	Event archive sink (we are a producer)
BigQuery (analytics_curated.*)	Read-only for fact queries; owned by analytics-service

We never embed PII directly in event payloads or in the artifact metadata table. Recipient identifiers are reduced to recipient_hash; raw values stay only in report_subscriptions (encrypted at field level when present).

---

3. Postgres schema (canonical DDL)

All tables live under the reporting schema; the connection middleware sets SET app.tenant_id = '…' per request, and RLS policies bind every row to that GUC.

3.1 reporting.report_templates

CREATE TABLE reporting.report_templates (
  id                   text PRIMARY KEY,                 -- tpl_rep_<ulid>
  tenant_id            text,                              -- nullable: NULL = platform-shared
  key                  text NOT NULL,                     -- 'reservation.daily_arrivals'
  category             text NOT NULL CHECK (category IN
                          ('operational','financial','compliance','regulatory','manager_dashboard')),
  archived             boolean NOT NULL DEFAULT false,
  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now(),
  version              int NOT NULL DEFAULT 0,            -- OCC

  UNIQUE (tenant_id, key)                                 -- per-tenant uniqueness; NULL tenant_id allows cross-tenant key 'arrivals' shared
);

CREATE INDEX ix_report_templates_tenant_category ON reporting.report_templates (tenant_id, category)
  WHERE archived = false;

ALTER TABLE reporting.report_templates ENABLE ROW LEVEL SECURITY;
CREATE POLICY report_templates_tenant_isolation ON reporting.report_templates
  USING (tenant_id IS NULL OR tenant_id = current_setting('app.tenant_id', true));

3.2 reporting.template_versions

CREATE TABLE reporting.template_versions (
  id                   text PRIMARY KEY,                 -- tpv_<ulid>
  template_id          text NOT NULL REFERENCES reporting.report_templates(id),
  tenant_id            text,                              -- mirrors parent
  version_number       int NOT NULL CHECK (version_number >= 1),

  columns              jsonb NOT NULL,                    -- ColumnSpec[]
  filters              jsonb NOT NULL,                    -- FilterSpec[]
  layout               jsonb NOT NULL,                    -- LayoutBlock[]
  supported_formats    text[] NOT NULL CHECK (
                          supported_formats <@ ARRAY['pdf','xlsx','csv']
                          AND array_length(supported_formats,1) >= 1),
  default_locale       text NOT NULL,
  locale_variants      text[] NOT NULL,
  regulatory           boolean NOT NULL DEFAULT false,
  jurisdiction_code    text,
  retention_class      text NOT NULL CHECK (retention_class IN
                          ('operational_2y','operational_7y','regulatory_10y_objectlock')),
  data_source_spec     jsonb NOT NULL,
  preferred_submission_format text CHECK (preferred_submission_format IN ('pdf','xlsx','csv')),
  row_cap              int NOT NULL DEFAULT 50000,

  published_at         timestamptz NOT NULL DEFAULT now(),
  published_by_type    text NOT NULL,
  published_by_id      text NOT NULL,
  changeset_summary    text,

  UNIQUE (template_id, version_number),
  CHECK (NOT regulatory OR (jurisdiction_code IS NOT NULL AND retention_class = 'regulatory_10y_objectlock'))
);

CREATE INDEX ix_template_versions_template_version ON reporting.template_versions (template_id, version_number DESC);
CREATE INDEX ix_template_versions_regulatory ON reporting.template_versions (regulatory, jurisdiction_code) WHERE regulatory;

ALTER TABLE reporting.template_versions ENABLE ROW LEVEL SECURITY;
CREATE POLICY template_versions_tenant_isolation ON reporting.template_versions
  USING (tenant_id IS NULL OR tenant_id = current_setting('app.tenant_id', true));

3.3 reporting.reports

CREATE TABLE reporting.reports (
  id                       text PRIMARY KEY,             -- rep_<ulid>
  tenant_id                text NOT NULL,
  template_id              text NOT NULL REFERENCES reporting.report_templates(id),
  template_version_pin     int,                          -- NULL = use latest published
  display_name             jsonb NOT NULL,                -- I18nString
  default_filters          jsonb NOT NULL DEFAULT '{}'::jsonb,
  recent_run_ids           text[] NOT NULL DEFAULT '{}', -- last 30, head=newest

  created_at               timestamptz NOT NULL DEFAULT now(),
  created_by_type          text NOT NULL,
  created_by_id            text NOT NULL,
  updated_at               timestamptz NOT NULL DEFAULT now(),
  version                  int NOT NULL DEFAULT 0
);

CREATE INDEX ix_reports_tenant_template ON reporting.reports (tenant_id, template_id);

ALTER TABLE reporting.reports ENABLE ROW LEVEL SECURITY;
CREATE POLICY reports_tenant_isolation ON reporting.reports
  USING (tenant_id = current_setting('app.tenant_id', true));

3.4 reporting.report_runs

CREATE TABLE reporting.report_runs (
  id                          text PRIMARY KEY,           -- run_<ulid>
  tenant_id                   text NOT NULL,
  report_id                   text NOT NULL REFERENCES reporting.reports(id),
  template_id                 text NOT NULL,
  template_version_id         text NOT NULL REFERENCES reporting.template_versions(id),
  template_version_number     int  NOT NULL,
  resolved_filters            jsonb NOT NULL,
  requested_formats           text[] NOT NULL,
  locale                      text NOT NULL,

  status                      text NOT NULL CHECK (status IN
                                ('queued','running','rendering','delivering','completed','failed','cancelled')),
  error_code                  text,
  error_detail                text,
  retriable                   boolean,
  retry_count                 int NOT NULL DEFAULT 0,
  max_retries                 int NOT NULL DEFAULT 3,
  next_attempt_at             timestamptz,

  requested_by_type           text NOT NULL,
  requested_by_id             text NOT NULL,
  correlation_id              text NOT NULL,
  idempotency_key             text NOT NULL,

  ai_provenance               jsonb,                      -- AIProvenance | null

  queued_at                   timestamptz NOT NULL DEFAULT now(),
  started_at                  timestamptz,
  rendered_at                 timestamptz,
  completed_at                timestamptz,
  failed_at                   timestamptz,
  cancelled_at                timestamptz,

  artifact_ids                text[] NOT NULL DEFAULT '{}',
  delivery_count_succeeded    int NOT NULL DEFAULT 0,
  delivery_count_failed       int NOT NULL DEFAULT 0,
  delivery_count_total        int NOT NULL DEFAULT 0,

  worker_instance             text,
  version                     int NOT NULL DEFAULT 0,

  UNIQUE (tenant_id, idempotency_key)
);

CREATE INDEX ix_report_runs_tenant_status_queuedat ON reporting.report_runs (tenant_id, status, queued_at DESC);
CREATE INDEX ix_report_runs_report_recent ON reporting.report_runs (report_id, queued_at DESC);
CREATE INDEX ix_report_runs_due_retries ON reporting.report_runs (next_attempt_at)
  WHERE status = 'failed' AND retriable = true AND retry_count < max_retries;
CREATE INDEX ix_report_runs_by_run_recent ON reporting.report_runs (tenant_id, queued_at DESC) INCLUDE (status);

ALTER TABLE reporting.report_runs ENABLE ROW LEVEL SECURITY;
CREATE POLICY report_runs_tenant_isolation ON reporting.report_runs
  USING (tenant_id = current_setting('app.tenant_id', true));

3.5 reporting.export_artifacts

CREATE TABLE reporting.export_artifacts (
  id                      text PRIMARY KEY,               -- art_<ulid>
  tenant_id               text NOT NULL,
  run_id                  text NOT NULL REFERENCES reporting.report_runs(id),
  format                  text NOT NULL CHECK (format IN ('pdf','xlsx','csv')),
  locale                  text NOT NULL,
  bucket                  text NOT NULL,
  object_path             text NOT NULL,                  -- 'tnt_…/run_…/<sha>.pdf'
  size_bytes              bigint NOT NULL CHECK (size_bytes >= 0),
  sha256                  char(64) NOT NULL,
  retention_class         text NOT NULL,
  object_locked_until     timestamptz,                     -- non-null for regulatory_10y_objectlock
  produced_at             timestamptz NOT NULL DEFAULT now(),
  signed_url_cached_url   text,
  signed_url_expires_at   timestamptz,

  CHECK (object_path LIKE tenant_id || '/%')              -- defense-in-depth tenant prefix
);

CREATE INDEX ix_export_artifacts_run ON reporting.export_artifacts (run_id);
CREATE INDEX ix_export_artifacts_tenant_format ON reporting.export_artifacts (tenant_id, format, produced_at DESC);

ALTER TABLE reporting.export_artifacts ENABLE ROW LEVEL SECURITY;
CREATE POLICY export_artifacts_tenant_isolation ON reporting.export_artifacts
  USING (tenant_id = current_setting('app.tenant_id', true));

3.6 reporting.report_schedules

CREATE TABLE reporting.report_schedules (
  id                                   text PRIMARY KEY,    -- sch_<ulid>
  tenant_id                            text NOT NULL,
  report_id                            text NOT NULL REFERENCES reporting.reports(id),
  cron_expr                            text NOT NULL,
  timezone                             text NOT NULL,
  template_version_pin                 int,
  filters                              jsonb NOT NULL DEFAULT '{}'::jsonb,
  subscription_ids                     text[] NOT NULL DEFAULT '{}',

  status                               text NOT NULL CHECK (status IN ('active','paused','disabled_after_failures')),
  consecutive_failures                 int  NOT NULL DEFAULT 0,
  disabled_after_failures_threshold    int  NOT NULL DEFAULT 5,
  last_fired_at                        timestamptz,
  last_fire_ms_drift                   int,
  next_fire_at_estimated               timestamptz,
  cloud_scheduler_job_name             text,                -- linked GCP scheduler job name

  created_at                           timestamptz NOT NULL DEFAULT now(),
  created_by_type                      text NOT NULL,
  created_by_id                        text NOT NULL,
  updated_at                           timestamptz NOT NULL DEFAULT now(),
  version                              int NOT NULL DEFAULT 0,

  CHECK (array_length(subscription_ids, 1) >= 1)
);

CREATE INDEX ix_report_schedules_tenant_status ON reporting.report_schedules (tenant_id, status);
CREATE INDEX ix_report_schedules_next_fire ON reporting.report_schedules (next_fire_at_estimated)
  WHERE status = 'active';

ALTER TABLE reporting.report_schedules ENABLE ROW LEVEL SECURITY;
CREATE POLICY report_schedules_tenant_isolation ON reporting.report_schedules
  USING (tenant_id = current_setting('app.tenant_id', true));

3.7 reporting.report_subscriptions

CREATE TABLE reporting.report_subscriptions (
  id                       text PRIMARY KEY,               -- sub_<ulid>
  tenant_id                text NOT NULL,
  report_id                text NOT NULL REFERENCES reporting.reports(id),
  recipient_kind           text NOT NULL CHECK (recipient_kind IN
                              ('user','email','desktop_device','webdav','sftp')),
  recipient_user_id        text,                            -- when recipient_kind='user'
  recipient_email_enc      bytea,                           -- AES-256-GCM, tenant-scoped DEK
  recipient_email_hash     char(64),                        -- HMAC-SHA256(tenantSalt, lower(trim(email)))
  recipient_device_id      text,
  recipient_endpoint       text,                            -- webdav/sftp (no secrets here)
  recipient_credentials_ref text,                           -- pointer to Secret Manager, never the secret itself

  channel                  text NOT NULL CHECK (channel IN ('email','in_app','desktop_sync','webdav','sftp')),
  format                   text NOT NULL CHECK (format IN ('pdf','xlsx','csv')),
  locale                   text NOT NULL,

  status                   text NOT NULL CHECK (status IN ('active','paused','cancelled')),
  last_delivered_at        timestamptz,
  last_delivery_status     text CHECK (last_delivery_status IN ('delivered','failed','unknown')),
  last_failure_code        text,

  created_at               timestamptz NOT NULL DEFAULT now(),
  created_by_type          text NOT NULL,
  created_by_id            text NOT NULL,
  version                  int NOT NULL DEFAULT 0,

  CHECK (
    (recipient_kind = 'user'           AND recipient_user_id IS NOT NULL)
 OR (recipient_kind = 'email'          AND recipient_email_hash IS NOT NULL)
 OR (recipient_kind = 'desktop_device' AND recipient_device_id IS NOT NULL)
 OR (recipient_kind IN ('webdav','sftp') AND recipient_endpoint IS NOT NULL)
  )
);

CREATE INDEX ix_report_subscriptions_tenant_report ON reporting.report_subscriptions (tenant_id, report_id, status);
CREATE INDEX ix_report_subscriptions_recipient_user ON reporting.report_subscriptions (tenant_id, recipient_user_id) WHERE recipient_user_id IS NOT NULL;
CREATE INDEX ix_report_subscriptions_recipient_device ON reporting.report_subscriptions (tenant_id, recipient_device_id) WHERE recipient_device_id IS NOT NULL;

ALTER TABLE reporting.report_subscriptions ENABLE ROW LEVEL SECURITY;
CREATE POLICY report_subscriptions_tenant_isolation ON reporting.report_subscriptions
  USING (tenant_id = current_setting('app.tenant_id', true));

3.8 reporting.report_filters

CREATE TABLE reporting.report_filters (
  id          text PRIMARY KEY,                            -- flt_<ulid>
  tenant_id   text NOT NULL,
  scope       text NOT NULL CHECK (scope IN ('user','tenant')),
  owner_id    text,                                         -- usr_… when scope='user'
  name        text NOT NULL,
  filters     jsonb NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  version     int NOT NULL DEFAULT 0,

  UNIQUE (tenant_id, scope, COALESCE(owner_id,''), name)
);

ALTER TABLE reporting.report_filters ENABLE ROW LEVEL SECURITY;
CREATE POLICY report_filters_tenant_isolation ON reporting.report_filters
  USING (tenant_id = current_setting('app.tenant_id', true));

3.9 reporting.regulatory_submissions

CREATE TABLE reporting.regulatory_submissions (
  id                    text PRIMARY KEY,                   -- reg_<ulid>
  tenant_id             text NOT NULL,
  run_id                text NOT NULL REFERENCES reporting.report_runs(id),
  artifact_id           text NOT NULL REFERENCES reporting.export_artifacts(id),
  jurisdiction_code     text NOT NULL,
  adapter_ref           text NOT NULL,
  status                text NOT NULL CHECK (status IN ('pending','submitting','succeeded','failed','manually_resolved')),
  attempts              int NOT NULL DEFAULT 0,
  max_attempts          int NOT NULL DEFAULT 5,
  next_attempt_at       timestamptz,
  last_error_code       text,
  last_error_detail     text,

  proof_receipt_kind    text,
  proof_receipt_hash    char(64),
  proof_received_at     timestamptz,
  proof_register_ref    text,
  proof_object_path     text,

  submitted_at          timestamptz,
  succeeded_at          timestamptz,
  failed_at             timestamptz,
  resolved_at           timestamptz,
  resolution_note       text,

  version               int NOT NULL DEFAULT 0
);

CREATE INDEX ix_regulatory_submissions_tenant_status ON reporting.regulatory_submissions (tenant_id, status, jurisdiction_code);
CREATE INDEX ix_regulatory_submissions_due ON reporting.regulatory_submissions (next_attempt_at)
  WHERE status IN ('pending','failed');

ALTER TABLE reporting.regulatory_submissions ENABLE ROW LEVEL SECURITY;
CREATE POLICY regulatory_submissions_tenant_isolation ON reporting.regulatory_submissions
  USING (tenant_id = current_setting('app.tenant_id', true));

3.10 reporting.outbox and reporting.inbox_processed

Standard platform shapes from 04 §6:

CREATE TABLE reporting.outbox (
  id              bigserial PRIMARY KEY,
  envelope_id     text NOT NULL UNIQUE,
  tenant_id       text NOT NULL,
  subject         text NOT NULL,
  ordering_key    text NOT NULL,
  payload         jsonb NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now(),
  published_at    timestamptz,
  published_attempts int NOT NULL DEFAULT 0
);
CREATE INDEX ix_outbox_unpublished ON reporting.outbox (created_at) WHERE published_at IS NULL;

CREATE TABLE reporting.inbox_processed (
  subject     text NOT NULL,
  message_id  text NOT NULL,
  processed_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (subject, message_id)
);

---

4. GCS bucket layout

gs://melmastoon-reports-<region>/
  <tenantId>/
    runs/
      <yyyy>/<mm>/<dd>/
        <runId>/
          <sha256>.pdf
          <sha256>.xlsx
          <sha256>.csv

gs://melmastoon-reports-regulatory-<region>/   (object lock enabled, 10y retention policy)
  <tenantId>/
    submissions/
      <yyyy>/<mm>/<dd>/
        <runId>/
          artifact-<sha256>.pdf
          receipt-<receiptHash>.<ext>

Lifecycle rules (Terraform-managed):

Bucket	Tier transition	Action
reports-<region>	30 d → Nearline, 365 d → Coldline, 730 d (operational_2y) → delete; 2555 d (operational_7y) → delete	(operational classes)
reports-regulatory-<region>	Coldline immediately; 3650 d → delete; object lock with default retention 3650 d	(regulatory_10y_objectlock)

CMEK on all buckets: projects/<p>/locations/<r>/keyRings/melmastoon-reporting/cryptoKeys/artifacts.

---

5. Indexes & query patterns

Hot query	Index used
Worker poll: due retries	ix_report_runs_due_retries
Run status poll	PK report_runs.id
Subscription list per report	ix_report_subscriptions_tenant_report
Recent runs for dashboard	ix_report_runs_report_recent
Regulatory due-for-retry sweep	ix_regulatory_submissions_due
Schedules due	ix_report_schedules_next_fire
Template lookup by category in admin UI	ix_report_templates_tenant_category

---

6. Foreign-data references

We do not declare foreign keys to other services' Postgres tables. Cross-service references are by ID + version pin only and validated at use-case entry through the corresponding read clients.

---

7. Backups & PITR

• Cloud SQL automated backups every 24 h; PITR enabled with 14 d window.
• GCS artifacts: bucket versioning enabled on regulatory bucket; non-regulatory bucket relies on object-lifecycle rules.
• Regulatory submissions table is included in daily Merkle-anchor digest produced by audit-service (07 §9) for tamper-evident archival.

---

8. Migrations

Migrations follow standards/NAMING §6: NNNN_<description>.sql per service. Initial set:

File	Adds
0001_init.sql	All tables in §3
0002_outbox_inbox.sql	Outbox + inbox dedupe
0003_rls_policies.sql	Every <table>_tenant_isolation policy
0004_indexes.sql	All §5 indexes
0005_seed_platform_templates.sql	Seed the 18 platform-shared default templates (operational + regulatory archetypes)

CI's Migration check asserts every new table has a tenant_id column and a <table>_tenant_isolation RLS policy.

---

9. Cross-references

• ID prefixes: standards/NAMING §6
• Outbox / inbox patterns: 04 §6
• Field-level encryption (recipient email): SECURITY_MODEL §4
• Sync rules for desktop SQLite: SYNC_CONTRACT
• Analytics fact tables we read: services/analytics-service/DATA_MODEL.md