# NGBVD granular Power BI API

## Readable references update — contract 2026-10-31.2

Every feed now carries the scoped survivor's `survivor_code` and `crb_number`.
Lookup-backed fields expose names instead of raw lookup IDs. Employment status
and reporter type also use readable labels. Actions and action-services expose
`action_name`, derived from approved authority names, dates and service names:
the production actions table has no independent name column. Missing business
references are null and flagged; an operational ID is never substituted.

Display the business references and names. Keep `record_key`, `survivor_key` and
`action_key` only for unambiguous model relationships, and hide them in report
visuals. They are stable HMAC handles, not operational primary keys. Code/CRB and
action labels are not assumed unique. Snapshot/request UUIDs and encrypted
cursors are protocol controls, not case-editing identifiers. The feeds expose no
operational primary/foreign keys, staff IDs, or numeric lookup identifiers.

These paths accept only GET/HEAD. POST/PUT/PATCH/DELETE return 405. They contain no
create/edit/delete record actions; normal successful reads write only the
existing safe access audit. Existing partner-submission APIs are separate and
remain unchanged.

A new offline snapshot is required once for the added references. During the
build, a compatible old snapshot remains readable through the new ID-removing
allowlist; new references are null with an upgrade-pending flag. Check
`metadata.readable_fields_ready`. `schema_version` describes the public projected
contract; `snapshot_schema_version` describes the materialized stored version.
The updated Power Query function requires readable fields to be ready. A failed
build does not replace the last good snapshot. No source join is added to GET.

This patch adds seven granular, paginated feeds to the supplied 31 October 2026
application. Existing layouts, styles, Flexmonster/PivotJS views and report
implementations are preserved. The integrations pages use their current cards,
forms and logs; only endpoint data, a Power BI client option and explanatory text
are added. The overview controller now supplies the summary/recent-log variables
already expected by its existing view.

The supplied ZIP contains application folders but no root `artisan`, Composer
manifest/lock or `vendor`. Deploy this as an overlay to the existing complete
application. It is not a replacement Laravel distribution. Do not copy any test
harness vendor directory to production. The original SQL backup is unchanged.

## Endpoint contract

All URLs are relative to `https://ngbvd.mglsd.go.ug/api/v2/power-bi`.

| GET path | Grain | Required client scope |
| --- | --- | --- |
| `/` or `/manifest` | Published version and authorised feed list | `powerbi.manifest.read` |
| `/schema` | Field names, types and grains | `powerbi.schema.read` |
| `/datasets/survivors` | One survivor intake | `powerbi.survivors.read` |
| `/datasets/incidents` | One incident | `powerbi.incidents.read` |
| `/datasets/perpetrators` | One recorded perpetrator observation | `powerbi.perpetrators.read` |
| `/datasets/witnesses` | One recorded witness observation | `powerbi.witnesses.read` |
| `/datasets/actions` | One action/referral | `powerbi.actions.read` |
| `/datasets/action_services` | One distinct action/service association | `powerbi.action_services.read` |
| `/datasets/case_status_history` | One status history entry | `powerbi.case_status_history.read` |

Every data request requires one or more explicit data scopes in addition to the
feed permission. Wildcards grant no Power BI access. Authentication is mandatory
on metadata endpoints as well as data endpoints.

Dataset parameters are `snapshot` (manifest UUID), `page_size` (1-1000, default
500) and `cursor` (opaque next-page token). A cursor is bound to its client,
permissions, restrictions, dataset, snapshot and expiration. It cannot be shared
between clients or datasets. Snapshot publication during pagination does not
change that pagination's version. Use `metadata.has_more` and `next_cursor`;
short pages are valid and must not be treated as the end of the feed.

The API has no SQL/filter/order expressions, unlimited downloads, source-table
fallback or HTTP refresh operation. Empty authorised results are HTTP 200 with
`data: []`. Invalid arguments return 400; invalid credentials/expired credentials
401; missing grant/network policy 403; cursor/version conflicts 409; quota or
admission limits 429; disabled/uninitialised/stale/unavailable service 503.
Responses include a request ID; 429/503 provide `Retry-After`.

## Data model and indicators

The machine-readable `schema.json` and `indicator-coverage.csv` enumerate the
exposed fields and the source-column disposition. The feeds preserve individual
operational records; they do not group incidents or return summary totals.
Millions of repeated source rows remain repeated observations, with distinct
pseudonymous record keys. A recorded perpetrator observation is not proof of a
unique person. Investigate source anomalies separately before interpreting counts.

Use `survivors.record_key` as the one side of relationships to each child feed's
`survivor_key`. Link `actions.record_key` to `action_services.action_key`. Avoid
flattening all one-to-many feeds: doing so multiplies incident and person counts.
Do not sum distinct survivors across arbitrary slicers. Partner and geography
dimensions live on survivors; incident geography is also carried on incidents.

Analytical indicators cover intake/referral flags, geography, sex, nationality,
national status/categories, age, children, separation from children, disability,
religion, marital status, education, occupation, employment, previous reporting,
reporter type/relationship/sex, incident type/date/time, perpetrator indicators,
witness sex/age group/relationship, authorities, referrals, services, case statuses
and pending points. Label lookups accept approved codes/names only. Unresolved or
invalid values become null with a field-specific quality flag, not an invented
category or an arbitrary free-text value.

The field named `dob` is ambiguous: the current intake code writes a reported
age there. A numeric value 0-120 is exposed as age at intake; a valid birth date
can support a calculated age at intake or incident. Numeric intake age is never
back-projected into an incident age. A reported zero can represent the intake
default for missing age, so its child flag is null and flagged for review.
`separeted_children` is a yes/no separation indicator, not a child count.

FGM entries in `incident_data` are included like other individual incidents.
The separate `fgm_details`/`fgm_services` tables hold district/quarter counts;
those aggregated source records cannot yield individual FGM observations and
are excluded from this granular API. Legacy copies and `*_failed_swap_*` tables
are also excluded. No source record is deleted or modified by this patch.

Person names, identity-document numbers, telephone/email/contact fields, exact
birth dates, villages, case narratives and staff identities are excluded.
Survivor Code/CRB are intentionally included confidential business references,
subject to the client's existing row scope; the extract is not anonymous.
Relationship handles use a dedicated 256-bit secret and domain-separated,
128-bit HMAC pseudonyms. They remain stable across snapshots while that secret
remains unchanged. This is pseudonymisation, not anonymisation; granular GBV data
remains confidential even without names.

Action service associations union the normalised junction with valid legacy CSV
service codes. Each action/service pair appears once, with `link_source`
identifying junction, legacy CSV or both. Invalid service codes are counted in
private build diagnostics. A child row without a valid parent intake is excluded
and counted as an orphan; strict numeric parsing prevents coercion of malformed
legacy references into another survivor's records.

## Daily preparation and workload isolation

The Laravel scheduler starts `ngbvd:refresh-powerbi --if-due` at **00:00 EAT**
(`Africa/Kampala`, UTC+03). It runs outside HTTP and publishes only after every
feed succeeds. Midnight is the job start time, not a promise that a large build
is already available at midnight. `captured_at_utc`, `completed_at_utc` and the
EAT business date describe the actual build. A repeatable-read, read-only
transaction gives the core InnoDB records one consistent read view. Reference
labels, some of which are MyISAM, are copied once; avoid concurrent reference
maintenance during preparation. The code does not convert or lock those tables.

Production defaults require a read-only reporting replica, one healthy replica
channel and lag no greater than 300 seconds. The snapshot records lag at capture;
this is an operational lag measure, not an exact transaction commit timestamp.
A stopped/lagging replica fails the build. SELECT and REPLICATION CLIENT rights
are needed on the source. A separate snapshot database receives bounded inserts;
the API uses a separate SELECT-only reader account on that store.

HTTP requests query only published snapshot rows, plus a fresh client lookup and
one metadata-only audit insert in the application database. They never query live
victim, incident, perpetrator or witness tables. Snapshots store compressed,
allowlisted JSON; page reads decompress within a strict size limit. Keyset
pagination uses fixed-prefix indexes and merges bounded scope streams to avoid
large offset scans or broad geographic/partner filesorts. Read queries have an
execution-time budget; responses have a 4 MiB uncompressed data budget and may
therefore contain fewer rows than requested.

The API lowers an unlimited or legacy high PHP memory limit to 128 MiB for its
requests and never raises a lower server limit. It avoids the legacy base
controller that sets an 8 GiB limit. Other controllers remain unchanged.

There are at most two admitted Power BI data/metadata reads at once per shared
cache, client/IP quotas and a capped per-client rate. Use a shared, secured cache
on multiple app servers. For stronger web-tier isolation, serve this API through
dedicated, capped API workers/gateway infrastructure. Authentication, audit
inserts, CPU and network still consume resources; zero impact cannot be asserted
without a production capacity test.

The backup contains 88,773 intakes, 109,316 incidents, 10,373,831 perpetrator rows,
4,028,258 witnesses, 20,305 actions and 29,500 status entries. A complete refresh
of all granular feeds is a large extraction and cannot reasonably be promised
to finish in seconds. Select only required feeds/scopes and validate Power BI
refresh duration, memory and model capacity. Per-page latency and whole-model
refresh duration are separate acceptance measures.

A failed build leaves the previous published snapshot online. Snapshots older
than 36 hours are rejected. Inactive generations older than 48 hours are pruned
in small store-only batches; the active generation is never deleted by pruning.
Account for active, retained and in-progress generations when sizing disk space.

## Client security and permissions

Create a dedicated **Power BI Granular Analytics** client in
`/integrations/api-clients`. Require HTTPS, specify the gateway's approved egress
IPs/CIDRs, grant exact feed scopes and choose explicit data scopes. Leave
outbound sharing and application-payload encryption off: this is inbound JSON
over TLS. The API enforces HTTPS even if a database checkbox is changed.
Trust proxy headers only from explicitly configured proxies; raw
`X-Forwarded-Proto` cannot satisfy the API's HTTPS check.

For native Power BI, use **Basic** authentication with client key as username and
client secret as password. Store credentials in Power BI/gateway settings, never
M code, parameters, URLs or workbook text. The same API alternatively accepts
`X-Client-Key` plus `X-Client-Secret` headers. Query-string credentials and bearer
fallback are rejected. New Power BI secrets are hashed only; the plaintext is
shown once in the existing creation flow. Clients expire after 90 days; create a
replacement, update the approved credential store, then disable the old client.

`data_scope.district` requires assigned district IDs and restricts both the intake
district and any declared incident district. `data_scope.partner` restricts the
case creator's partner. Both `users.partnerid` and a valid legacy `users.partner`
organisation are supported; legacy value `1` is an administration flag and is
not treated as an alternate organisation. `data_scope.own_records` means cases
created by the user who registered that API client, not the Power BI viewer.
Multiple restrictions intersect. A national grant never overrides an explicitly
assigned district, partner or own-record restriction. Nonempty assigned district
or partner fields also narrow a national policy.

Human operators need the existing `api_integrations.manage_clients` and the new
`api_integrations.manage_powerbi_clients`. National sharing additionally requires
`api_integrations.grant_powerbi_national` and the operator's own national data
scope. A scoped operator cannot delegate broader districts/partners/ownership
than their own assigned restrictions. The permission migration registers the new
permissions for existing roles with `is_allowed=0`; it grants no access. Assign
the approved roles through the existing Roles and Permissions screen. Existing
view/API URL/log permissions continue controlling those screens.

Optional HMAC request signing is available for an approved connector/gateway
that can generate fresh signatures. A client requiring signatures must use that
connector rather than the supplied native Basic M function. Sign the exact
canonical string, with newline separators and no trailing newline:

```text
UPPERCASE_HTTP_METHOD
/api/v2/power-bi/datasets/FEED
SORTED_RFC3986_QUERY_STRING
UNIX_TIMESTAMP
NONCE
LOWERCASE_SHA256_OF_BODY
```

Use HMAC-SHA256 with the client secret and send lowercase hex in `X-Signature`,
the timestamp in `X-Timestamp` and a 16-120 character random nonce in `X-Nonce`.
The signature covers pagination and snapshot arguments. A nonce is consumed
only after signature verification; replay is rejected. This canonical form is
specific to this Power BI API and differs from the legacy integrations signer.

Every known API action and denial writes safe metadata to `/integrations/logs`
under `POWER_BI`. No response rows, secrets, cursor values, raw URLs/queries or
case payloads are written by the new logger. If auditing fails, no granular data
is returned. Client deactivation and scope changes are checked on every page;
policy changes invalidate old cursors. Already imported Power BI copies cannot
be remotely revoked through this API: apply workspace permissions, RLS, export
restrictions, device/gateway controls and retention policies there. Never enable
Publish to web for this dataset.

## Validation and references

See `VALIDATION.md`, `DEPLOYMENT.md`, `PERMISSIONS.md`, the OpenAPI specification
and the included tests. Runtime tests use synthetic records, not production
survivor data. Full MySQL replica/load testing and Power BI Service acceptance
must be completed on the target infrastructure before enabling the endpoint.

Primary design references checked 31 October 2026:

- https://csrc.nist.gov/pubs/sp/800/207/final
- https://laravel.com/docs/8.x/scheduling
- https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html
- https://dev.mysql.com/doc/refman/8.0/en/show-replica-status.html
- https://learn.microsoft.com/en-us/powerquery-m/web-contents
- https://learn.microsoft.com/en-us/power-query/connectors/web/web
