Reporting & Analytics API

What it is. Reporting Sync is WIP's cross-cutting reporting and analytics layer. A background sync worker consumes NATS events for document, template, terminology, term, and term-relation changes and materializes them as flattened PostgreSQL table views — one doc_<value> table per template per namespace, plus fixed terminologies / terms / templates / term_relations metadata tables — so consumers can query WIP data with SQL instead of the document stores' own APIs (reporting_sync/worker.py, reporting_sync/schema_manager.py). A separate batch-sync path backfills or rebuilds those tables from the REST APIs of Def-Store, Template-Store, and Document-Store (reporting_sync/batch_sync.py). On top of the table views the service exposes ad-hoc read-only SQL querying, unified cross-entity search (terminology/term/template/document/file), outgoing/incoming reference validation, an aggregated referential-integrity check, an activity feed, and CSV export (reporting_sync/main.py).

Reaching it. Base path /api/reporting-sync (APIRouter(prefix="/api/reporting-sync"), main.py). /health is additionally exposed unprefixed, for direct container probes rather than through the API prefix. The interactive schema is served unprefixed at /docs (FastAPI default), reachable only when addressing the service directly. The service declares an X-API-Key security requirement in its OpenAPI contract (declare_api_key_security(app), main.py) — every route in this doc expects that header. See also: Auth & Access; Routing & Ingress.

Conventions that apply.

• Namespace scoping, exercised throughout: every WIP namespace maps to its own PostgreSQL schema. Reporting tables live inside that schema under plain, unqualified names (doc_<template value> for documents; terminologies / terms / templates / term_relations for metadata) — the same template value in two namespaces produces two distinct tables, never one shared table disambiguated by a namespace column (schema_manager.py: SchemaManager docstring). Most endpoints below take namespace as an explicit, non-defaulted parameter rather than assuming one.
• Rate limiting: setup_rate_limiting(app) reads WIP_RATE_LIMIT, defaulting to 40000/minute (main.py).
• This store does not expose bulk-array write endpoints with per-item results — batch-sync triggers return a single job or a single per-table result object, not the platform's array-in/per-item-results bulk-first shape. See API Conventions for where that convention does apply.

Endpoints

Health & status

GET /health

• Purpose. Container-level liveness/readiness probe (unprefixed, for direct container health checks).
• Request. No parameters.
• Response. 200 HealthResponse: status ("healthy" when both NATS and Postgres are connected, "degraded" when exactly one is, "unhealthy" when neither), service, version, nats_connected, postgres_connected, details (nats_url, postgres_host, stream_name). The Postgres flag is re-probed live with a SELECT 1 on every call, not just read from a cached startup flag.
• Errors. None beyond the standard 5xx if the process itself is unhealthy.
• Example.

  curl http://reporting-sync:8005/health
  

  {"status": "healthy", "service": "wip-reporting-sync", "version": "0.1.0",
   "nats_connected": true, "postgres_connected": true,
   "details": {"nats_url": "nats://nats:4222", "postgres_host": "postgres", "stream_name": "WIP_EVENTS"}}
  

GET /api/reporting-sync/health

• Purpose. Same handler as GET /health above, reachable through the API prefix for callers going through the router.
• Request / Response / Errors. Identical to GET /health.
• Example. curl -H "X-API-Key: $KEY" https://.../api/reporting-sync/health

GET /api/reporting-sync/status

• Purpose. Current sync-worker status.
• Request. No parameters.
• Response. 200 SyncStatus: running, connected_to_nats, connected_to_postgres, last_event_processed, events_processed, events_failed, tables_managed. connected_to_nats/connected_to_postgres are refreshed from live state on every call (NATS via is_connected, Postgres via a 1-second-timeout SELECT 1) — this endpoint is the final authority, not a value latched at startup.
• Errors. None distinctive.
• Example.

  curl -H "X-API-Key: $KEY" https://.../api/reporting-sync/status
  

  {"running": true, "connected_to_nats": true, "connected_to_postgres": true,
   "last_event_processed": "2026-07-09T10:02:11Z", "events_processed": 4821,
   "events_failed": 3, "tables_managed": 27}
  

Monitoring & metrics

GET /api/reporting-sync/metrics

• Purpose. Comprehensive service metrics: uptime, connection status, event throughput, consumer lag, latency percentiles, per-template stats, error breakdown.
• Request. No parameters.
• Response. 200 MetricsResponse: started_at, uptime_seconds, nats_connected, postgres_connected, events_processed, events_failed, events_per_second, consumer_info (ConsumerInfo | null), processing_latency (LatencyStats: sample_count, min_ms, max_ms, avg_ms, p50_ms, p95_ms, p99_ms — computed from a sliding window of up to 1000 samples), template_stats (list of PerTemplateStats), errors_by_type (dict of error-type → count).
• Errors. None distinctive.
• Example. curl -H "X-API-Key: $KEY" https://.../api/reporting-sync/metrics

  {"started_at": "2026-07-08T09:00:00Z", "uptime_seconds": 91234.5,
   "nats_connected": true, "postgres_connected": true,
   "events_processed": 4821, "events_failed": 3, "events_per_second": 1.2,
   "consumer_info": {"stream_name": "WIP_EVENTS", "consumer_name": "reporting-sync-durable",
                      "pending_messages": 0, "pending_bytes": 0, "delivered_messages": 4824,
                      "ack_pending": 0, "redelivered": 1, "last_delivered": null},
   "processing_latency": {"sample_count": 1000, "min_ms": 1.2, "max_ms": 340.1, "avg_ms": 18.4,
                           "p50_ms": 12.0, "p95_ms": 55.3, "p99_ms": 120.7},
   "template_stats": [{"template_value": "patient", "table_name": "doc_patient",
                        "documents_synced": 512, "documents_failed": 0,
                        "last_sync_at": "2026-07-09T10:02:11Z", "last_error": null, "last_error_at": null}],
   "errors_by_type": {}}
  

GET /api/reporting-sync/metrics/consumer

• Purpose. NATS JetStream consumer detail only (pending/ack-pending/redelivered/delivered counts).
• Request. No parameters.
• Response. 200 ConsumerInfo | null — null when the service has no JetStream context (NATS not connected).
• Errors. None distinctive.
• Example. curl -H "X-API-Key: $KEY" https://.../api/reporting-sync/metrics/consumer

GET /api/reporting-sync/alerts

• Purpose. Current alert configuration plus active and recently resolved alerts.
• Request. No parameters.
• Response. 200 AlertsResponse: config (AlertConfig), active_alerts (list of Alert), resolved_alerts (list of Alert, capped at the last 100 resolved). AlertConfig: enabled, check_interval_seconds (default 30), thresholds (AlertThresholds: queue_lag_warning=100, queue_lag_critical=1000, error_rate_warning=5.0, error_rate_critical=20.0, stall_warning_seconds=300, stall_critical_seconds=600), webhook_url, webhook_headers. Alert: alert_id, alert_type (queue_lag | error_rate | processing_stalled | connection_lost), severity (info | warning | critical), message, triggered_at, resolved_at, details.
• Errors. None distinctive.
• Example. curl -H "X-API-Key: $KEY" https://.../api/reporting-sync/alerts

PUT /api/reporting-sync/alerts/config

• Purpose. Replace the alert configuration.
• Request. Body: a full AlertConfig object (not a partial patch — the handler assigns it wholesale).
• Response. 200 the AlertConfig as stored.
• Errors. 422 on a body that doesn't validate as AlertConfig. Configuration is held in memory only — it does not survive a process restart (metrics.py: MetricsCollector._alert_config).
• Example.

  curl -X PUT -H "X-API-Key: $KEY" -H "Content-Type: application/json" \
    -d '{"enabled": true, "check_interval_seconds": 60,
         "thresholds": {"queue_lag_warning": 200, "queue_lag_critical": 2000,
                         "error_rate_warning": 5.0, "error_rate_critical": 20.0,
                         "stall_warning_seconds": 300, "stall_critical_seconds": 600},
         "webhook_url": "https://hooks.example/alerts", "webhook_headers": {}}' \
    https://.../api/reporting-sync/alerts/config
  

POST /api/reporting-sync/alerts/test

• Purpose. Force an immediate alert check (rather than waiting for the periodic background loop) and return the result.
• Request. No parameters.
• Response. 200 {"checked": true, "nats_connected": bool, "postgres_connected": bool, "consumer_pending": int | null, "new_alerts": [...], "active_alerts": [...]}.
• Errors. None distinctive.
• Example. curl -X POST -H "X-API-Key: $KEY" https://.../api/reporting-sync/alerts/test

Table & schema introspection

GET /api/reporting-sync/table-name

• Purpose. Resolve the reporting table name for a (namespace, template_value) pair without constructing it by hand.
• Request. Query: namespace (required), template_value (required).
• Response. 200 {"namespace", "template_value", "schema", "table_name", "qualified_name", "exists"} — exists reports whether the table has actually been materialized (tables are created lazily on first sync).
• Errors. 503 if PostgreSQL isn't connected. 400 if namespace/template_value fails identifier validation (a " or NUL byte, or over PostgreSQL's 63-byte identifier limit).
• Note. A template's optional reporting.table_name cosmetic override is not reflected here yet — this always returns the default doc_<value> form (main.py: resolve_table_name docstring).
• Example.

  curl -H "X-API-Key: $KEY" "https://.../api/reporting-sync/table-name?namespace=clinicA&template_value=patient"
  

  {"namespace": "clinicA", "template_value": "patient", "schema": "clinicA",
   "table_name": "doc_patient", "qualified_name": "\"clinicA\".\"doc_patient\"", "exists": true}
  

GET /api/reporting-sync/schema/{template_value}

• Purpose. Column definitions and row count for a template's reporting table.
• Request. Path: template_value. Query: namespace (required — the table lives inside that namespace's schema, and the same template value can exist in several namespaces).
• Response. 200 {"namespace", "template_value", "schema", "table_name", "qualified_name", "columns": [{"name","type","nullable","default"}], "row_count"}.
• Errors. 503 if PostgreSQL isn't connected. 400 on an unsafe namespace identifier. 404 if the resolved table doesn't exist.
• Example.

  curl -H "X-API-Key: $KEY" "https://.../api/reporting-sync/schema/patient?namespace=clinicA"
  

Batch sync

POST /api/reporting-sync/sync/batch/terminologies

• Purpose. Batch-sync all terminologies from Def-Store into the reporting terminologies table.
• Request. Query: namespace (required, no default), page_size (default 100).
• Response. 200 {"status": "completed", "table": "terminologies", "synced": int, "failed": int, "total": int}.
• Errors. 503 if the batch-sync service isn't available (Postgres not connected at startup).
• Example. curl -X POST -H "X-API-Key: $KEY" "https://.../api/reporting-sync/sync/batch/terminologies?namespace=clinicA"

POST /api/reporting-sync/sync/batch/terms

• Purpose. Batch-sync all terms (iterating every terminology) into the reporting terms table.
• Request / Response / Errors. Same shape as the terminologies sync above ("table": "terms").

POST /api/reporting-sync/sync/batch/term_relations

• Purpose. Batch-sync all active term-relations from Def-Store's ontology API into the reporting term_relations table.
• Request. Query: namespace (required, no default), page_size (default 100).
• Response. 200 {"status": "completed", "table": "term_relations", "synced", "failed", "total"}.
• Errors. 503 if the batch-sync service isn't available. Term-relations only otherwise sync from live NATS events, so a rebuilt reporting database loses all historical relations until this endpoint (or the startup backfill) runs.

POST /api/reporting-sync/sync/batch/templates

• Purpose. Batch-sync template metadata (status, version, extends) from Template-Store into the reporting templates table — the rebuild/backfill path, since the live sync path only writes on template events.
• Request / Response / Errors. Same shape ("table": "templates").

POST /api/reporting-sync/sync/batch

• Purpose. Trigger a document batch-sync job for every template with sync_enabled true.
• Request. Query: force (default false), page_size (default 100).
• Response. 200 list[BatchSyncResponse], one per template: {"job_id", "template_value", "status", "message"}. Terminologies and terms are synced first as reference data, then a job is started per template with a small delay between starts.
• Errors. 503 if the batch-sync service isn't available.
• Example. curl -X POST -H "X-API-Key: $KEY" "https://.../api/reporting-sync/sync/batch?force=false"

GET /api/reporting-sync/sync/batch/jobs

• Purpose. List all tracked batch-sync jobs.
• Request. No parameters.
• Response. 200 list[BatchSyncJob]: job_id, template_value, status (pending|running|completed|failed|cancelled), started_at, completed_at, total_documents, documents_synced, documents_failed, current_page, error_message.
• Errors. 503 if the batch-sync service isn't available.

GET /api/reporting-sync/sync/batch/jobs/{job_id}

• Purpose. Get one batch-sync job by ID.
• Request. Path: job_id.
• Response. 200 BatchSyncJob.
• Errors. 404 if the job isn't found. 503 if the batch-sync service isn't available.

POST /api/reporting-sync/sync/batch/{template_value}

• Purpose. Trigger a batch-sync job for one template — fetches every document for that template from Document Store and syncs it to PostgreSQL.
• Request. Path: template_value. Query: force (default false), page_size (default 100, must be 10–1000).
• Response. 200 BatchSyncResponse.
• Errors. 400 if page_size is outside 10–1000. 503 if the batch-sync service isn't available.
• Example. curl -X POST -H "X-API-Key: $KEY" "https://.../api/reporting-sync/sync/batch/patient?force=true&page_size=200"

DELETE /api/reporting-sync/sync/batch/jobs/{job_id}

• Purpose. Cancel a running batch-sync job.
• Request. Path: job_id.
• Response. 200 {"status": "cancelled", "job_id"} if it was running, {"status": "not_running", "job_id"} otherwise — cancelling an unknown or already-finished job is not an error.
• Errors. 503 if the batch-sync service isn't available.

DELETE /api/reporting-sync/sync/batch/jobs

• Purpose. Clear completed/failed/cancelled jobs from memory.
• Request. No parameters.
• Response. 200 {"cleared": <count>}.
• Errors. 503 if the batch-sync service isn't available.

Integrity check

GET /api/reporting-sync/health/integrity

• Purpose. Aggregate referential-integrity results from Template Store's and Document Store's own /health/integrity endpoints into one report.
• Request. Query: template_status (optional), document_status (optional filter: active/inactive/archived), template_limit (default 0 in the actual signature — see the code-findings note below), document_limit (default 0, same note), check_term_refs (default true), recent_first (default false).
• Response. 200 AggregatedIntegrityResult: status (healthy|warning|error|partial), checked_at, services_checked, services_unavailable, summary (IntegritySummary: total_templates, total_documents, documents_checked, templates_with_issues, documents_with_issues, orphaned_terminology_refs, orphaned_template_refs, orphaned_term_refs, inactive_refs), issues (list of IntegrityIssue: type, severity, source, entity_id, entity_value, field_path, reference, message). status is partial when some services were unreachable but at least one responded, error when none responded or any issue has severity="error", warning if any issue is a warning, else healthy.
• Errors. No hard failure when a peer service is unreachable — that service is added to services_unavailable instead and the aggregate status degrades to partial/error. The Document Store call's timeout scales with document_limit (up to 30 minutes at document_limit=0, i.e. "all").
• Example. curl -H "X-API-Key: $KEY" "https://.../api/reporting-sync/health/integrity?document_limit=5000"

Search & activity

POST /api/reporting-sync/search

• Purpose. Unified search across terminologies, terms, templates, documents, and files, with independent per-type pagination.
• Request. Body SearchRequest (rejects unknown fields): query (required, min length 1), types (optional list, default all five types), namespace, status, template (restricts document search to one template by value), mode ("auto" default | "fts" | "substring"), include_inactive (default false), snippet_format ("html" default | "text"), page (default 1), page_size (1–100, default 50), limit (deprecated alias for page_size when page=1). Query params namespace, status, template are also accepted and, when present, override the same field in the body.
• Response. 200 SearchResponse: query, mode, results (dict keyed by entity type — only types actually visited appear — each a SearchTypeResults: items (list of SearchResult), total, page, page_size, pages), total (sum across types). SearchResult: type, id, value, label, status, description, updated_at, score (FTS hits only), snippet (FTS hits only).
• Errors. 503 if the search service isn't available. 422 on a body with an unrecognized field (strict request model) or query shorter than 1 character. Per-type result sets are capped at 1000 items in memory; beyond that a query needs refining rather than paginating further.
• Example.

  curl -X POST -H "X-API-Key: $KEY" -H "Content-Type: application/json" \
    -d '{"query": "diabetes", "types": ["document"], "mode": "auto"}' \
    "https://.../api/reporting-sync/search?namespace=clinicA"
  

  {"query": "diabetes", "mode": "auto",
   "results": {"document": {"items": [{"type": "document", "id": "doc-123", "value": "PATIENT",
                                        "label": "PATIENT document", "status": "active",
                                        "description": "Matched in doc_patient", "updated_at": "2026-07-01T00:00:00Z",
                                        "score": 0.42, "snippet": "history of <b>diabetes</b>"}],
                             "total": 1, "page": 1, "page_size": 50, "pages": 1}},
   "total": 1}
  

GET /api/reporting-sync/activity/recent

• Purpose. Recent-activity timeline aggregated across entity types.
• Request. Query: types (comma-separated, optional — valid values terminology, term, template, document; the code does not fetch recent files here despite SearchService supporting a file type), limit (default 50).
• Response. 200 ActivityResponse: activities (list of ActivityItem: type, action (created|updated|deleted|deprecated), entity_id, entity_value, entity_label, timestamp, user, version, details), total.
• Errors. 503 if the search service isn't available. limit outside 1–200 is not rejected — it is silently replaced with the default 50 rather than returning 422.
• Example. curl -H "X-API-Key: $KEY" "https://.../api/reporting-sync/activity/recent?types=document,template&limit=20"

GET /api/reporting-sync/references/term/{term_id}/documents

• Purpose. Find documents whose term_references include the given term.
• Request. Path: term_id. Query: limit (default 100). Requires PostgreSQL — walks every doc_* table across every namespace schema for a term_references_json column and does a JSON text search for the term ID.
• Response. 200 TermDocumentsResponse: term_id, documents (list of DocumentReference: document_id, template_id, template_value, field_path, status, created_at), total.
• Errors. No error when PostgreSQL is unavailable — returns an empty result set (documents: [], total: 0) rather than 503. limit outside 1–1000 is silently replaced with 100 rather than rejected.
• Example. curl -H "X-API-Key: $KEY" "https://.../api/reporting-sync/references/term/trm-abc/documents"

GET /api/reporting-sync/entity/{entity_type}/{entity_id}/references

• Purpose. Fetch an entity and validate every reference it makes outward (template → terminology/parent template, document → template/terms, term → terminology).
• Request. Path: entity_type (one of document, template, terminology, term — file is accepted by the sibling "referenced-by" endpoint but not here), entity_id.
• Response. 200 EntityReferencesResponse: entity (EntityDetails — entity_type, entity_id, entity_value, entity_label, entity_status, version, created_at, updated_at, data, references (list of EntityReference: ref_type, ref_id, ref_value, ref_label, field_path, status (valid|broken|inactive), error), valid_refs, broken_refs, inactive_refs) or null, error (set instead of entity when the lookup itself fails, e.g. entity not found upstream).
• Errors. 400 if entity_type isn't one of the four valid values. 503 if the search service isn't available. An upstream 404 from the owning store is surfaced as entity: null, error: "<Type> not found: <id>" with an overall 200, not propagated as a 404.
• Example. curl -H "X-API-Key: $KEY" "https://.../api/reporting-sync/entity/document/doc-123/references"

GET /api/reporting-sync/entity/{entity_type}/{entity_id}/referenced-by

• Purpose. Find entities that reference the given entity (the reverse of the endpoint above).
• Request. Path: entity_type (document, template, terminology, term — accepted, though document always returns an empty list since documents aren't referenced by other entities in this model), entity_id. Query: limit (ge=1, le=500, default 100 — validated by FastAPI, unlike the silent-clamp endpoints above).
• Response. 200 ReferencedByResponse: entity_type, entity_id, entity_value, entity_label, referenced_by (list of IncomingReference: entity_type, entity_id, entity_value, entity_label, entity_status, field_path, reference_type — one of uses_template, extends, template_ref, terminology_ref, term_ref, file_ref), total, error.
• Errors. 400 if entity_type isn't one of the four valid values. 422 if limit is outside 1–500. 503 if the search service isn't available.
• Example. curl -H "X-API-Key: $KEY" "https://.../api/reporting-sync/entity/template/tpl-patient/referenced-by?limit=50"

Query (cross-template joins)

GET /api/reporting-sync/tables

• Purpose. List available reporting tables across every namespace schema, optionally filtered to one namespace or one bare table name.
• Request. Query: namespace (optional, restricts to one schema), table_name (optional, returns full column detail for that bare table name instead of a summary).
• Response. 200 {"tables": [...]}. Each entry: namespace, name, template_value (parsed from the table name for doc_* tables, null otherwise), qualified_name, row_count, and either column_count (summary mode) or columns ([{"name","type","nullable"}], when table_name is given). Only doc_* tables and the four fixed metadata tables (terminologies, terms, term_relations, templates) are listed — other PostgreSQL tables in a namespace schema are filtered out, and public/pg_catalog/information_schema/pg_toast are never treated as namespaces.
• Errors. 503 if PostgreSQL isn't connected. 404 if table_name is given and matches nothing.
• Example. curl -H "X-API-Key: $KEY" "https://.../api/reporting-sync/tables?namespace=clinicA"

POST /api/reporting-sync/query

• Purpose. Execute an arbitrary read-only SQL query against the reporting database — the ad-hoc SQL/cross-template-join surface.
• Request. Body ReportQuery: sql (required), params (positional bind values, default []), timeout_seconds (1–300, default 30), max_rows (1–50000, default 1000), namespace (optional — when set, the connection's search_path is set to that namespace's schema for the duration of the query, so unqualified table names like doc_patient resolve there; omit and schema-qualify instead for cross-namespace queries).
• Response. 200 {"columns": [...], "rows": [...], "row_count": int, "truncated": bool}. The query is wrapped as SELECT * FROM (<sql>) _q LIMIT <max_rows + 1> and run with default_transaction_read_only = on; truncated is true when the result was cut at max_rows.
• Errors. 400 if the SQL contains INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, GRANT, or REVOKE at a word boundary (case-insensitive keyword blocklist, not a full parser). 400 on an unsafe namespace identifier or a PostgreSQL syntax/other query error. 408 if the query exceeds timeout_seconds. 503 if PostgreSQL isn't connected.
• Example.

  curl -X POST -H "X-API-Key: $KEY" -H "Content-Type: application/json" \
    -d '{"sql": "SELECT document_id, status FROM doc_patient", "namespace": "clinicA", "max_rows": 100}' \
    https://.../api/reporting-sync/query
  

  {"columns": ["document_id", "status"], "rows": [{"document_id": "doc-123", "status": "active"}],
   "row_count": 1, "truncated": false}
  

CSV export

GET /api/reporting-sync/export/csv

• Purpose. Stream a full reporting table as CSV.
• Request. Query: namespace (required), table (required — bare table name, e.g. doc_patient), timeout_seconds (1–600, default 120), filename (optional download name; defaults to <namespace>_<table>.csv).
• Response. 200 text/csv, streamed with Content-Disposition: attachment. Header row first, then data rows fetched via a server-side cursor in batches to bound memory.
• Errors. 503 if PostgreSQL isn't connected. 400 if table isn't a doc_* table or one of terminologies/terms/term_relations/templates (same whitelist as GET /tables), or if namespace/table fails identifier validation.
• Example. curl -H "X-API-Key: $KEY" "https://.../api/reporting-sync/export/csv?namespace=clinicA&table=doc_patient" -o patients.csv

POST /api/reporting-sync/export/csv

• Purpose. Stream the result of an arbitrary read-only SQL query as CSV.
• Request. Body CsvExportQuery: sql (required), params (default []), timeout_seconds (1–600, default 120), filename (must match ^[\w\-. ]+\.csv$, default "export.csv").
• Response. 200 text/csv, streamed with Content-Disposition set from filename.
• Errors. 400 on the same dangerous-SQL keyword blocklist as POST /query (no max_rows cap here — the whole query result streams). 503 if PostgreSQL isn't connected.
• Example.

  curl -X POST -H "X-API-Key: $KEY" -H "Content-Type: application/json" \
    -d '{"sql": "SELECT document_id FROM doc_patient WHERE status = $1", "params": ["active"]}' \
    https://.../api/reporting-sync/export/csv -o active_patients.csv
  

Namespace deletion

DELETE /api/reporting-sync/namespace/{prefix}

• Purpose. Remove all reporting data for a namespace — called by Registry as part of namespace deletion.
• Request. Path: prefix (the namespace).
• Response. 200 {"namespace", "dropped_schema", "total_deleted"}. Because the namespace maps to its own schema, deletion is a single DROP SCHEMA "<prefix>" CASCADE — every doc_* and metadata table inside it goes atomically — plus the namespace's rows in the two shared bookkeeping tables (_wip_schema_migrations, _wip_sync_status). total_deleted is always a real integer (rows are counted before the schema is dropped), never null.
• Errors. 503 if PostgreSQL isn't connected. 400 if prefix is empty/whitespace or fails identifier validation.
• Example. curl -X DELETE -H "X-API-Key: $KEY" https://.../api/reporting-sync/namespace/clinicA

Root

GET /api/reporting-sync/

• Purpose. Service identity and a map of key endpoints.
• Request. No parameters.
• Response. 200 {"service", "version", "build": {sha, built_at, image_tag, ...}, "docs": "/docs", "health": "/api/reporting-sync/health", "status": "/api/reporting-sync/status", "integrity": "/api/reporting-sync/health/integrity", "search": "/api/reporting-sync/search", "activity": "/api/reporting-sync/activity/recent"}.
• Errors. None distinctive.
• Example. curl -H "X-API-Key: $KEY" https://.../api/reporting-sync/

Store-specific gotchas

• Namespace = PostgreSQL schema, not a column. Every table-scoped endpoint (/table-name, /schema/{template_value}, /sync/batch/*, /export/csv, POST /query with namespace set) resolves namespace to a schema name and interpolates it into identifiers after validating it — a value containing " or a NUL byte, or over 63 bytes, is rejected with 400 rather than silently truncated or escaped (schema_manager.py: SchemaManager._safe_ident).
• Append-only templates skip the identity uniqueness guard. A template with an empty (or absent) identity_fields list gets identity_hash="" on every synced row; the schema manager deliberately omits the partial-unique index on (namespace, identity_hash) for such tables, so every document lands as its own active row instead of colliding on the first insert. This is WIP's append-only identity mode — see PoNIFs (schema_manager.py: generate_create_table_ddl).
• /table-name ignores a template's cosmetic table-name override. It always computes and returns the default doc_<value> form, even if the template's reporting.table_name is set to something else (main.py: resolve_table_name docstring).
• /query and POST /export/csv are read-only by a keyword blocklist, not a SQL parser. INSERT/UPDATE/DELETE/DROP/ALTER/CREATE/TRUNCATE/GRANT/REVOKE are rejected at word boundaries (case-insensitive regex); the session also sets default_transaction_read_only = on as a second layer. POST /query additionally wraps every query in SELECT * FROM (...) LIMIT <max_rows+1> — GET /export/csv (table export) and POST /export/csv (query export) do not cap rows the same way, since the point is to stream the full result.
• Full-text search is opt-in per field. Only string-typed template fields with full_text_indexed=true get the <field>_search / <field>_tsv (GENERATED, GIN-indexed) column pair; mode=fts silently skips any doc_* table that has no _tsv column at all rather than erroring, and mode=auto (the default) falls back to a substring ILIKE scan on that table instead (search_service.py: _search_documents, schema_manager.py: _full_text_columns).
• Some limit query params validate, others silently clamp. GET /entity/{type}/{id}/referenced-by uses FastAPI's Query(ge=1, le=500) and returns 422 outside that range. GET /activity/recent (1–200) and GET /references/term/{term_id}/documents (1–1000) instead silently substitute their default rather than rejecting the request — the same "limit" concept behaves differently depending on which endpoint you call.
• Batch-sync jobs live in memory only. BatchSyncService._jobs is a plain dict — job history, running/pending status, and progress are all lost on a service restart; there is no persisted job table (batch_sync.py).
• Initial metadata sync at startup only backfills metadata, not documents, and only fires after Def-Store's /health responds. If Def-Store never becomes healthy within the retry window, the sync is skipped and the service logs the exact POST /sync/batch/terminologies|terms|term_relations|templates calls to run manually once it's reachable (main.py: _initial_metadata_sync).

See also

• API Conventions, PoNIFs, Auth & Access, Routing & Ingress — cross-cutting platform behavior this API's callers should already know.
• Data Model Reference, Identity & the Registry — the field types, identity_fields, and identity-hash semantics that schema_manager.py mirrors into PostgreSQL DDL.
• Document-Store API, Template-Store API, Def-Store API, Registry API — the sibling stores this service reads from (via their REST APIs for batch sync and reference lookups) and whose events it consumes over NATS.