# System-Table Read/Write Permission Contracts

This document records the binding contracts and any deliberate NexusCRM deltas
for `requiredReadPermission` and `requiredWritePermission` fields in
`backend/internal/bootstrap/system_tables.json`.

Layout note: frontend path references in this contract are implementation
anchors from the document's review window. Verify current frontend source
ownership against `frontend-l0-l1-ownership-inventory.md` before using them as
paths on `main`.

---

## Permission value format

Both fields accept:

- An empty string — meaning "no permission required; any authenticated user may
  access this table."
- `"BLOCKED"` — meaning "no user may access this table via generic CRUD/query,
  even system admin; dedicated management APIs must be used."
- A named system permission key, e.g. `"ViewSetup"`, `"ViewAllData"`,
  `"ManageUsers"`. The key must exist in the `_System_Permission` catalog.
- A comma-separated list of named permission keys, e.g.
  `"ViewAllData,ModifyAllData"`. Any one matching permission grants access
  (OR semantics). The permissions service splits on `,` at the read gate
  (`features/permissions/service.go`).

---

## `_System_FlowAuditLog` — read gate: `ViewAllData,ModifyAllData`

### Salesforce v63 reference

Salesforce's "Flow Run Time Logs" (the closest analogue) require
`ViewAllData` to access via standard Query/QueryAll APIs [verify v63 doc
citation needed — search: "Flow Run Time Logs" in Salesforce documentation
for v63 permissions]. NexusCRM's `_System_FlowAuditLog` is an L0 platform
observability table with no direct Salesforce wire-shape; however, the
data sensitivity is equivalent.

### Why `ViewAllData,ModifyAllData` instead of `ViewSetup`

**Pre-#503 value:** `ViewSetup`.

**Problem:** `_System_FlowAuditLog` audit rows carry L3 record values in
payload columns (`InputValues`, `OutputValues`, `ErrorMessage`). Any user
holding `ViewSetup` (e.g. a setup-only admin who cannot read business
records) would have been able to read raw field values from those columns,
including values from records they lack access to.

**Fix (issue #503, Option C-extended):** Pin the read gate to
`"ViewAllData,ModifyAllData"`.

### Deliberate NexusCRM delta from Salesforce

NexusCRM accepts `ModifyAllData` as an equivalent to `ViewAllData` for the
audit read gate. This is a documented NexusCRM delta because:

1. `record_access.go` (lines 37–42) already treats `ViewAllData` and
   `ModifyAllData` as the equivalent privileged-data tier: a user with MAD
   but not VAD still sees all records in the record-access path.
2. Restricting to `ViewAllData`-only would create a surprising asymmetry
   where a user with `ModifyAllData` (and therefore full record write access)
   cannot read the audit log for their own flow runs.
3. This matches the Salesforce intuition that the data-admin tier (VAD or MAD)
   is the correct gate for any table whose rows may contain L3 field values.

This delta is **stricter-than-default** (narrower audience than `ViewSetup`)
and does not expose data that would not otherwise be accessible to users
holding either VAD or MAD.

### Enforcement

- **Parse-time (no DB):** `bootstrap.assertFlowAuditLogReadPermissionPinned`
  runs on every `RunAssertions` call. This assertion is reached by
  `controlplane.Verify` (`verify.go:60`) and the `VerifyL0Invariants` path
  (`verify_l0.go:195`), which is called inside the `ApplyAll` lock
  (`operations.go`, `GET_LOCK('nexuscrm_controlplane_apply', 600)`).

- **Spec test:** `TestFlowAuditLogRequiredReadPermissionPinned` in
  `system_tables_spec_test.go` reads the live `system_tables.json` and
  asserts the literal `"ViewAllData,ModifyAllData"` value.

- **Assertion unit tests:** `assertions_audit_readgate_test.go` covers the
  assertion function in isolation: correct value passes, `ViewSetup` fails,
  empty string fails, missing table fails.

### Deferred follow-ups (tracked in issue #503)

- Column-granularity gating: `InputValues`/`OutputValues` redaction for users
  who hold VAD/MAD but whose profile restricts individual field access.
- Hard-immutability guard for this single row (prevent direct-DB overwrites
  without going through `ApplyAll`).
- Structured-error refactor and opt-in debug-log feature (separate from this
  read-gate fix).

---

## `_System_AuditLog` — read gate: `ViewAllData,ModifyAllData`

### Why `ViewAllData,ModifyAllData`

**Pre-#600 value:** `""` (empty — open to any authenticated user via generic
system-table read).

**Problem:** `_System_AuditLog` rows carry L3 record values in payload columns
(`OldValue`, `NewValue`, `ChangesJSON`) as well as session forensics data
(`ClientIp`, `SessionId`, `ActualUserId`). Any authenticated user could issue
SOQL `SELECT OldValue FROM _System_AuditLog` and receive raw field-change
history for records they may not otherwise be able to read.

**Fix (issue #600, Option C-extended — sibling to #503/#564):** Pin the read
gate to `"ViewAllData,ModifyAllData"`.

The same `ViewAllData,ModifyAllData` rationale from `_System_FlowAuditLog`
applies here: the data-admin tier (VAD or MAD) is the correct gate for any
table whose rows may contain L3 field values or session forensics. The delta
from Salesforce is the same documented delta (accepting MAD as equivalent to
VAD for audit-payload tables).

### Impact on legitimate read paths

- **`/admin/audit/log` route** — unaffected. This route is protected by
  `RequirePermission(PermViewFieldAuditTrail)` at the router level and uses
  raw SQL that bypasses `CheckObjectPermissionWithUser`. The RRP pin closes
  the generic SOQL path only.
- **`<Object>History` SOQL** (e.g. `FROM AccountHistory`) — unaffected. The
  History query path gates on the parent object (`"Account"`) not on
  `_System_AuditLog` schema. The Salesforce dialect history dispatcher only
  handles the `<Object>History` virtual-object shape; direct `_System_AuditLog`
  queries stay on the normal SOQL path.
- **Direct SOQL `FROM _System_AuditLog`** — now requires VAD or MAD.

### Enforcement

- **Parse-time (no DB):** `bootstrap.assertAuditLogReadPermissionPinned`
  runs on every `RunAssertions` call (wired at line 60, immediately after
  `assertFlowAuditLogReadPermissionPinned`).

- **Live-DB:** `bootstrap.assertAuditLogReadPermissionLiveDB` verifies the
  `_System_Object` row at runtime to catch drift from pre-existing deployments
  where the DB row may still carry `""`. Skip-on-ErrNoRows prevents bootstrap
  deadlock on fresh installs (wired at line 65).

- **Spec test:** `TestAuditLogRequiredReadPermissionPinned` in
  `system_tables_spec_test.go` reads the live `system_tables.json` and
  asserts the literal `"ViewAllData,ModifyAllData"` value.

### Deferred follow-ups (tracked in issue #565)

- Column-granularity gating: `OldValue`/`NewValue`/`ChangesJSON` redaction
  for VAD/MAD users whose profile restricts individual field access.
- `requiredWritePermission: "BLOCKED"` asymmetry: write path has no
  analogous immutability guard beyond `immutableObjectFields` at the
  persistence layer.

---

## `_System_SetupAuditTrail` — read gate: `ViewSetupAuditTrail,ViewAllData,ModifyAllData`

### Why `ViewSetup` was insufficient

**Pre-#604 value:** `ViewSetup`.

**Problem:** `_System_SetupAuditTrail` rows describe admin actions on private
metadata (e.g. profile changes, permission set assignments, field-level security
edits). A user holding `ViewSetup` — a setup-only admin with no broader data
access — could read the full audit trail of admin operations, potentially
revealing which users were assigned privileged permissions or which fields were
restricted. This is a stronger information-disclosure concern than the
`_System_FlowAuditLog` case because the data is about the security model itself,
not just incidental L3 payload.

### Why `ViewSetupAuditTrail,ViewAllData,ModifyAllData`

The three-token OR-set was chosen to support two distinct access patterns:

1. **`ViewSetupAuditTrail`** — a dedicated permission for audit-trail access.
   This allows orgs to grant audit visibility to a compliance/security role
   without granting full `ViewAllData`. This matches the Salesforce `SetupAuditTrail`
   model where a specific permission gates setup-trail access.
2. **`ViewAllData`** and **`ModifyAllData`** — the privileged-data tier already
   used by `_System_FlowAuditLog`. These grant access to users who already
   have broad data admin rights, preserving the same access semantics as
   the FlowAuditLog gate.

The `ViewSetup` permission is explicitly excluded: setup-only admins are not
automatically entitled to the full audit trail of other admins' configuration
changes.

### Enforcement

- **Parse-time (no DB):** `bootstrap.assertSetupAuditTrailReadPermissionPinned`
  runs on every `RunAssertions` call alongside the FlowAuditLog check.

- **Live-DB:** `bootstrap.assertSetupAuditTrailReadPermissionLiveDB` checks the
  actual `_System_Object` row at every server startup in strict mode.

- **Spec test:** `TestSetupAuditTrailRequiredReadPermissionPinned` in
  `system_tables_spec_test.go` reads the live `system_tables.json`.

- **Assertion unit tests:** `assertions_audit_readgate_test.go` — 8 tests
  covering the pinned-value, ViewSetup-denied, empty-denied, missing-table,
  live-spec, and live-DB cases.

- **Split test:** `TestSetupAuditTrailReadGate_ThreeTokenSplit` and
  `TestSetupAuditTrailReadGate_GrantDenyMatrix` in
  `service_readgate_split_test.go` verify the OR-semantic gate end-to-end.

### References

Issue #604 (this fix), #565 (sibling-table tracking), #503 (Option C-extended
precedent for FlowAuditLog), #564 (splitRequiredReadPermission multi-token
split implementation).


---

## Sibling-table scope — status

| Table | RequiredReadPermission | Status |
|---|---|---|
| `_System_FlowAuditLog` | `"ViewAllData,ModifyAllData"` | Fixed in #503/#564 |
| `_System_AuditLog` | `"ViewAllData,ModifyAllData"` | Fixed in #600 |
| `_System_SetupAuditTrail` | `"ViewSetupAuditTrail,ViewAllData,ModifyAllData"` | Fixed in #604 |
| `_System_RequestCapture` | `""` (empty) | Deferred — tracked in #601 |

The fix pattern established by #503/#564 (`_System_FlowAuditLog`) and
extended by #600 (`_System_AuditLog`) / #604 (`_System_SetupAuditTrail`)
serves as the template for the remaining sibling-table fixes. Each table is
triaged independently for its actual exposure cadence and blast radius.
Tracked at issue #565.


## `_System_RequestCapture` — read gate: `ViewAllData,ModifyAllData`, write gate: `BLOCKED`

### Why `ViewAllData,ModifyAllData` for reads

`_System_RequestCapture` rows carry HTTP request/response payload in `Body`,
`ResponseBody`, and `Headers` columns. These columns may contain:

- Auth headers (`Authorization: Bearer <token>`, `X-API-Key: <secret>`)
- API keys embedded in request/response bodies
- L3 record values in request/response payloads (PII, business data)

Using an open or `ViewSetup` read gate would expose both L3 record values and
credential material to users who lack `ViewAllData`/`ModifyAllData`.

**Chosen value:** `"ViewAllData,ModifyAllData"` — identical rationale to
`_System_FlowAuditLog`. The same privileged-data tier (VAD or MAD) gates
access; a user with MAD but not VAD still sees all records and therefore should
see the corresponding capture audit trail. Stricter alternatives:
- `"ViewSetup"` — actually weaker (wrong direction) for secrets exposure
- `"ViewAllData"` only (without MAD) — asymmetric: MAD users have full record
  write access but cannot read the audit trail for their own requests

### Why `BLOCKED` for writes

The capture middleware writes directly to `_System_RequestCapture` via a
dedicated `sqlStore` that bypasses the generic CRUD layer entirely. No user
or flow should be able to inject, overwrite, or delete capture rows via generic
API — doing so could corrupt the audit trail or inject misleading entries.
`"BLOCKED"` prevents any generic CRUD write regardless of permission level.

### Enforcement

- **Parse-time (no DB):** `bootstrap.assertRequestCaptureReadPermissionPinned` and
  `bootstrap.assertRequestCaptureWritePermissionPinned` run on every
  `RunAssertions` call.

- **Live-DB:** `bootstrap.assertRequestCaptureReadPermissionLiveDB` and
  `bootstrap.assertRequestCaptureWritePermissionLiveDB` check the actual
  `_System_Object` row at every server startup.

- **Spec tests:** `TestRequestCaptureRequiredReadPermissionPinned` and
  `TestRequestCaptureRequiredWritePermissionPinned` in
  `system_tables_spec_test.go` read the live `system_tables.json`.

- **Assertion unit tests:** `assertions_audit_readgate_test.go` (T1–T8) and
  `assertions_audit_writegate_test.go` (T10–T14) cover the assertion functions
  in isolation.

- **Bypass pin:** `TestRequestCaptureStore_BypassesPermissionService` in
  `request_capture_store_bypass_test.go` pins that `InsertRequestCapture`
  writes directly to `*sql.DB` without a PermissionService indirection.

- **Immutable field guard:** `RequiredWritePermission` is already in
  `immutableObjectFields` (persistence_service.go:1624), blocking generic CRUD
  writes to this field on `_System_Object`. Covered by
  `TestRejectImmutableFieldUpdates_RequiredWritePermission_Blocked`.

### References

Issues #601, #565. Mirrors the FlowAuditLog pattern from #564.

### Frontend follow-up

The frontend `effectivePermissions` resolver at
`frontend/src/infrastructure/api/effectivePermissions.ts:53–57` treats the
comma-list `"ViewAllData,ModifyAllData"` as a single permission key, causing
both `_System_FlowAuditLog` and `_System_RequestCapture` to appear unreadable
in the UI even to VAD/MAD holders. This is a UI-quality (over-restriction)
issue only — the server-side gate is authoritative. Tracked separately (see
issue filed by #601).

---