# Threadline domain reference
This guide defines the vocabulary Threadline uses across capture triggers, Ecto schemas, and the public API. If you want the architecture/persona/JTBD map first, start with [How Threadline works](how-threadline-works.md). It complements the [README](../README.md) and module documentation on [HexDocs](https://hexdocs.pm/threadline).
## Ubiquitous language
| Term | One sentence | Tier |
|------|----------------|------|
| AuditAction | A semantic “who did what and why” event your application records explicitly. | persisted row |
| AuditTransaction | A database transaction bucket produced by capture, grouping row-level changes and optional actor context. | persisted row |
| AuditChange | One captured INSERT/UPDATE/DELETE on an audited table, tied to a transaction. | persisted row |
| AuditContext | Request-scoped metadata (actor, request/correlation IDs, IP) carried on the connection before it reaches the database. | concept only |
| ActorRef | Structured identifier for who performed an action or triggered writes, stored as JSON-compatible data. | field on row |
| Correlation | Cross-cutting identifier linking work across processes or services (headers, job args), not a first-class DB entity in Threadline. | concept only |
## Relationships
```text
AuditAction AuditTransaction
| |
| optional link |
+--------------------------------+
| |
| v
| AuditChange
| (one row op)
v
(semantic intent) (physical capture)
```
Invariant: every `AuditChange` belongs to exactly one `AuditTransaction`; an `AuditTransaction` may link to zero or one `AuditAction` when you correlate semantic intent with physical changes.
## AuditTransaction
An `AuditTransaction` is the capture substrate’s grouping record for a single database transaction. PostgreSQL assigns `txid`; Threadline stores it with `occurred_at`, optional `source`/`meta`, and optional `actor_ref` populated from a transaction-local GUC set in the same database transaction as your writes. It may reference an `AuditAction` when you connect semantic events to captured rows.
## AuditChange
An `AuditChange` is one row-level mutation on an audited table: schema/name, primary key map, operation (`op`), optional `data_after`, changed field list, and `captured_at`. Multiple changes in one DB transaction share the same `transaction_id`.
## Redaction at capture
Threadline can **exclude** or **mask** configured columns when PL/pgSQL capture functions are generated (`mix threadline.gen.triggers`), so JSON written to `audit_changes` never contains raw values for those keys. **`exclude`** removes keys from `data_after` (and from change lists where the generator applies the same filter). **`mask`** keeps the key but persists only a stable placeholder (default `"[REDACTED]"`) for both `data_after` and sparse **`changed_from`** when that mode is enabled. Overlap between exclude and mask is a hard error at codegen. **json/jsonb** columns use whole-value masking only. Configuration lives under **`config :threadline, :trigger_capture`** (see README). Path B is preserved: redaction is static SQL and trigger paths do not introduce new session writes.
### Configured versus deployed redaction drift
Phase 67 adds a second operator question on top of capture-time redaction: does the deployed trigger SQL still match the configured policy? Threadline answers that through the operator-surface page at `/audit/policy/redaction` and the parity Mix task `mix threadline.policy.show`.
Both surfaces use the same three-state taxonomy:
- **Config matches deployed** — configured redaction matches deployed trigger redaction.
- **Drift detected** — configured redaction does not match deployed trigger SQL. Rerun `mix threadline.gen.triggers` and apply the migration.
- **Could not introspect** — Threadline could not safely parse the deployed trigger SQL. Rerun `mix threadline.gen.triggers`; do not assume capture is aligned.
This is a viewer, not a mutator. Policy edits still happen in `config :threadline, :trigger_capture`, then through regenerated trigger migrations. Both surfaces stay read-only and never show sample values; they expose only column names plus `mask_placeholder` metadata so operators can verify policy shape safely.
## Retention
Operators cap table growth with a **global retention window** under **`config :threadline, :retention`**, validated by `Threadline.Retention.Policy` before purge runs.
- **Primary clock:** eligibility uses each row’s **`AuditChange.captured_at`** (`timestamptz`, microsecond precision), not `AuditTransaction.occurred_at`. This matches `Threadline.Query.timeline/2`, which applies **`captured_at >= :from`** (inclusive lower bound) and **`captured_at <= :to`** (inclusive upper bound) when those filters are set. Retention purge deletes changes with **`captured_at` strictly less than** the computed cutoff (`now` minus the configured window), so the boundary is **exclusive on the “keep” side** at the cutoff instant — slightly stricter than the inclusive `:to` filter in timeline queries; operators should treat the cutoff as “anything older than this instant is eligible.”
- **Global window:** v1.3 is one documented interval for all captured changes (same relation Threadline owns). Per-table retention is an extension point for later releases.
- **Long transactions:** multiple `AuditChange` rows under one `audit_transactions` row can carry **different** `captured_at` values; retention is evaluated **per change row**, not “whole transaction expires as one timestamp.”
- **Empty parents:** after eligible changes are removed, the default purge path deletes **`audit_transactions`** rows that have **no** remaining child changes (optional `delete_empty_transactions: false` for transitional installs). See `Threadline.Retention` / `mix threadline.retention.purge`.
## Export
Read-only exports for operator playbooks (“export then purge”, cross-checks, ad-hoc analysis).
- **Filter vocabulary:** identical to `Threadline.Query.timeline/2` — `:repo`, `:table`, `:actor_ref`, `:from`, `:to`, `:correlation_id`. Bounds apply to **`AuditChange.captured_at`** (inclusive). **`AuditTransaction.occurred_at`** appears inside exported transaction context and can differ from `captured_at` on the same change row.
- **APIs:** `Threadline.Export` (`to_csv_iodata/2`, `to_json_document/2`, `count_matching/2`, `stream_changes/2`), `Threadline.export_csv/2`, `Threadline.export_json/2`, and **`mix threadline.export`** (see task `@moduledoc`).
- **Formats:** CSV uses a fixed hybrid column layout (JSON blobs for nested maps, single `transaction_json` column). JSON uses **`format_version: 1`** on the wrapped document; **`ndjson`** omits the outer wrapper.
- **Safety:** default **`max_rows`** caps in-memory materialization; results report **`truncated`** when the cap is hit. Streaming ignores that cap — compose with `Stream.take/2` when needed.
## Evidence write boundary (host-written)
<!-- EVIDENCE-HOST-WRITE-BOUNDARY -->
Evidence rows in **`threadline_evidence_records`** are **host-written attestations**, not background jobs Threadline runs for you. Host apps create them explicitly via `Threadline.Evidence` `record_*` entrypoints for six closed subjects:
- `record_redaction_policy/3`
- `record_trigger_coverage/3`
- `record_retention_run/3`
- `record_retention_policy/3`
- `record_export_delivery/3`
- `record_support_scope_posture/3`
Read surfaces — `Threadline.Proof`, `mix threadline.evidence.show`, and mounted `/audit/evidence` — **interpret rows already written**. They are viewers, not writers.
Threadline does not auto-populate evidence from retention, health, or export paths. Running `Threadline.Retention`, `Threadline.Health`, or export APIs may write ops metadata (for example rows in **`threadline_retention_runs`** for operator retention history), but that metadata does not auto-create `threadline_evidence_records` rows. **`threadline_retention_runs`** (ops metadata) ≠ **`threadline_evidence_records`** (host attestations).
An empty evidence view means the host has not yet recorded an attestation for that subject — not that a missing background job failed.
## Evidence proof contract
`mix threadline.evidence.show` is the canonical no-Phoenix viewer for
Threadline-owned evidence records. It is a viewer, not a compliance gate, and
successful reads remain valid viewer results even when the claim outcome is
`unsupported`.
`--json` emits one wrapped proof document with stable top-level keys:
- `format_version`
- `generated_at`
- `proof_type`
- `subject`
- `mode`
- `filters`
- `summary`
- `claim_assessment`
- `records`
`claim_assessment` uses one exact verdict vocabulary for successful proof
payloads:
- `proven` — Threadline-owned facts or deterministic derivations over owned
evidence directly support the claim. This can include negative facts such as
uncovered trigger coverage or denied export delivery when the evidence row
itself proves that outcome.
- `inferred_posture` — Threadline is naming posture or configuration synthesis
from owned evidence without overclaiming host-owned authorization, tenancy,
or other ambient guarantees.
- `unsupported` — Threadline does not claim authority for that guarantee. This
remains valid output in both JSON and human-readable views; it is not a
runtime failure.
Operational errors stay outside that verdict vocabulary. `invalid_request` and
`runtime_failure` are error outcomes, not proof verdicts. Downstream readers
should treat the wrapped document as a stable additive contract and rely on
`claim_assessment` rather than inventing host-policy meaning from `records`.
## Audit indexing (integrator-owned)
Physical PostgreSQL indexes on **`audit_transactions`**, **`audit_changes`**, and **`audit_actions`** are **integrator-owned**: Threadline ships a safe baseline via migrations, but workload-specific btree/GIN choices stay with the team operating the database. For baseline inventory, join shapes (timeline vs export vs correlation), retention delete patterns, and **optional** additive DDL framed as non-mandatory, read the **[Audit table indexing cookbook](audit-indexing.md)**—do not duplicate full DDL matrices here; link to the cookbook when operators need tuning guidance.
<span id="operating-at-scale-v19"></span>
## Operating at scale (v1.9+)
v1.9 adds the **telemetry operator narrative**, the **audit table indexing cookbook**, and **production checklist** guidance on **volume, retention cadence, and purge monitoring** — this heading is a **map** to those homes, not a second copy of their tables or matrices.
- **[Telemetry (operator reference)](#telemetry-operator-reference)** — `:telemetry` events operators should chart.
- **[Trigger coverage (operational)](#trigger-coverage-operational)** — how `Threadline.Health.trigger_coverage/1` tuples relate to `mix threadline.verify_coverage` and on-call triage.
- **[Audit table indexing cookbook](audit-indexing.md)** — baseline vs optional indexes and join semantics for timeline, export, and correlation workloads.
- **[Production checklist — retention and volume](production-checklist.md#4-retention-and-purge)** — purge cadence, growth signals, and CLI/API gates (see **`### Volume, growth, and purge cadence`** under §4).
## Brownfield continuity
Tables with **pre-existing rows** still use **T0** semantics: `Threadline.history/3` may return `[]` until the first trigger-backed mutation after capture is installed. Operators should follow [`guides/brownfield-continuity.md`](brownfield-continuity.md) for checklists, `mix threadline.verify_coverage`, and `mix threadline.continuity` (including `--dry-run`).
## AuditAction
`AuditAction` rows represent application-level audit events you insert via `Threadline.record_action/2`. They are independent of trigger capture until you associate them with transactions through `action_id`.
## AuditContext
`AuditContext` is built by `Threadline.Plug` (or your own code) and stored on `conn.assigns`. It is not persisted until you bridge actor identity into the database inside a transaction (see `Threadline.Plug`).
## ActorRef
`ActorRef` is the structured actor representation serialized to JSON for `audit_transactions.actor_ref` and `audit_actions.actor_ref`. Use `Threadline.Semantics.ActorRef.to_map/1` with `Jason.encode!()` when setting the GUC.
## Telemetry (operator reference)
Threadline emits **`:telemetry.execute/3`** events (no attached handler is required for correctness). Attach handlers in your application `Application.start/2` (or equivalent) for metrics and logs.
| Event | When | Measurements | Metadata |
|-------|------|--------------|----------|
| `[:threadline, :transaction, :committed]` | After capture commits work, or as a proxy when `Threadline.record_action/2` succeeds without an explicit post-commit hook | `table_count` (non‑neg integer; accurate only if you call `Threadline.Telemetry.transaction_committed/2` after the transaction) | `%{}` |
| `[:threadline, :action, :recorded]` | After `Threadline.record_action/2` finishes (success or failure) | `status` (`:ok` or `:error`) | `%{}` |
| `[:threadline, :health, :checked]` | After `Threadline.Health.trigger_coverage/1` returns | `covered`, `uncovered` (counts of tables in each bucket) | `%{}` |
### `[:threadline, :transaction, :committed]`
**When it fires.** Threadline emits this event after capture-associated transactions commit their work, and also emits it as a **proxy** with `table_count: 0` when `Threadline.record_action/2` succeeds without an explicit post-commit hook that supplies real per-transaction counts.
**What to measure.** Use `table_count` when you need fidelity to “how many distinct audited tables produced rows in this transaction.” Compare week-over-week after deploys or schema changes.
**Metadata.** Handlers receive an empty map (`%{}`) today; keep dashboards tolerant if metadata keys are added later.
**Misleading or degraded signals.** `table_count` is often **0** on the `record_action` proxy path even when semantic capture succeeded — that is not proof that triggers failed. A generic smell: steady **zero** `table_count` while application traces show audited-table writes for hours → confirm whether events are dominated by the proxy vs missing `Threadline.Telemetry.transaction_committed/2` after `Repo.transaction/1`.
**Where to look next.** [`production-checklist.md` §1 — Capture and triggers](production-checklist.md#1-capture-and-triggers) for install / `mix threadline.gen.triggers` / coverage cadence; [`production-checklist.md` §6 — Observability](production-checklist.md#6-observability) for handler wiring.
### `[:threadline, :action, :recorded]`
**When it fires.** Immediately after `Threadline.record_action/2` completes, success or failure.
**What to measure.** Emit rate split by `status` (`:ok` vs `:error`). Error spikes often track validation failures, missing `ActorRef`, or repo outages — chart both absolute errors and error ratio.
**Metadata.** Empty map (`%{}`).
**Misleading or degraded signals.** High `:ok` traffic does **not** imply every domain table row was captured; this event tracks the semantics helper, not each physical mutation.
**Where to look next.** [`production-checklist.md` §1 — Capture and triggers](production-checklist.md#1-capture-and-triggers) for trigger coverage cadence; [`production-checklist.md` §2 — Actor bridge and semantics](production-checklist.md#2-actor-bridge-and-semantics) for GUC / `record_action` pairing.
<span id="threadline-health-checked"></span>
### `[:threadline, :health, :checked]`
**When it fires.** After `Threadline.Health.trigger_coverage/1` returns from its catalog pass.
**What to measure.** `covered` and `uncovered` are **aggregate counts** of tables in each bucket across the public user tables `Health` enumerates — telemetry does not stream per-table tuples here.
**Metadata.** Empty map (`%{}`).
**Misleading or degraded signals.** A rising `uncovered` count is inventory drift, not automatically a CI failure: `mix threadline.verify_coverage` enforces only the configured `expected_tables` intersection (see [`## Trigger coverage (operational)`](#trigger-coverage-operational)).
**Where to look next.** Tuple-level results and Mix policy live under [`## Trigger coverage (operational)`](#trigger-coverage-operational); operational cadence in [`production-checklist.md` §1 — Capture and triggers](production-checklist.md#1-capture-and-triggers).
**Weekly / post-deploy / “metrics look wrong” triage**
1. Confirm `:telemetry` handlers for Threadline events are attached in the running release ([`production-checklist.md` §6 — Observability](production-checklist.md#6-observability)).
2. For `[:threadline, :transaction, :committed]`, sample `table_count`: persistent zeros during known writes usually mean the proxy path or missing `Threadline.Telemetry.transaction_committed/2` — revisit the subsection above and `Threadline.Telemetry` on HexDocs.
3. For `[:threadline, :action, :recorded]`, compare `:ok` vs `:error` trends against deploys and auth incidents ([`production-checklist.md` §2 — Actor bridge and semantics](production-checklist.md#2-actor-bridge-and-semantics)).
4. For `[:threadline, :health, :checked]`, reconcile `uncovered` with [`## Trigger coverage (operational)`](#trigger-coverage-operational) before tuning alerts.
5. After schema or trigger changes, rerun the §1 checklist items for `mix threadline.gen.triggers` and `mix threadline.verify_coverage` ([`production-checklist.md` §1 — Capture and triggers](production-checklist.md#1-capture-and-triggers)).
6. Remember **retention purge** does not emit these events — use purge batch logs, not this triage list, when investigating purge-only windows.
7. If correlation-scoped investigations spike, verify whether `record_action` volume alone explains `transaction_committed` traffic (proxy vs counted commits).
8. After material Plug/Phoenix changes, re-check actor GUC wiring and telemetry boot order together (§1 + §6).
**Retention purge** does not emit these events today; use application logs from `mix threadline.retention.purge` / `Threadline.Retention.purge/1` (see task `@moduledoc`) or wrap purge calls with your own telemetry.
See also: `Threadline.Telemetry` on HexDocs for copy-paste attach examples.
## Trigger coverage (operational)
`Threadline.Health.trigger_coverage/1` takes **`repo:`** (required `Ecto.Repo` module) and an optional **`:schema`** keyword (default `"public"`) and returns a list of tagged tuples:
`[{:covered, String.t()} | {:uncovered, String.t()} | {:expected_uncovered, String.t()}]`
Each tuple names a user table the catalog query sees in the requested schema. `{:covered, name}` means Threadline’s `threadline_audit_*` trigger was found on that relation; `{:uncovered, name}` means it was not; `{:expected_uncovered, name}` means the table is intentionally not audited (`schema_migrations` baseline plus adopter-configured `:expected_uncovered_tables`). The third tuple variant is **additive** — existing callsites that pattern-match only `{:covered, _}` / `{:uncovered, _}` keep working unchanged.
**Schema scope.** Pass `:schema` to query a non-`public` schema (e.g. `Threadline.Health.trigger_coverage(repo: MyApp.Repo, schema: "tenant_42")`). The lib does **NOT** validate `:schema` against `pg_namespace` — programmatic callers are responsible for sanitizing or trusting their own input. Surfaces that take untrusted input (the Operator-Surface coverage LV and the Mix tasks) validate at the edge with a regex + `pg_namespace` lookup.
**Audit catalog tables.** `audit_transactions`, `audit_changes`, and `audit_actions` are **excluded** from the per-table list — they are not expected to carry capture triggers (CAP-10 / `Threadline.Health` `@moduledoc`). Do not expect them in `Health` output.
**`mix threadline.health.coverage`.** Viewer-only Mix-task parity for capture-only adopters: prints a three-section `TABLE / STATUS / SOURCE` table by default, or a JSON object via `--json` (`covered`, `uncovered`, `expected_uncovered`, `schema` keys; `expected_uncovered` entries are `{"table", "source"}` objects with `source ∈ {"baseline", "config"}`). Always exits 0 — viewer, not gate. Pass `--schema=NAME` for multi-schema adopters. Cross-link: see `guides/operator-surface.md` §"Coverage dashboard" for the LV companion.
**`mix threadline.policy.show`.** Viewer-only parity for redaction drift: prints one summary line, one aligned `TABLE / STATUS / CONFIG / DEPLOYED / HINT` table, and detail blocks only for actionable rows. `--json` emits additive top-level counts plus `tables`, with stable status values `config_matches_deployed`, `drift_detected`, and `could_not_introspect`. `/audit/policy/redaction` is the LiveView companion for Phoenix adopters; both surfaces use the same rerun guidance and never show sample values.
**`mix threadline.verify_coverage`.** Hosts configure `config :threadline, :verify_coverage, expected_tables: [...]` with the audited tables CI must protect. The task calls `Threadline.Health.trigger_coverage/1`, then `Threadline.Verify.CoveragePolicy.violations/2`, which applies **intersection semantics:** only names in `expected_tables` can fail the Mix task. A `{:uncovered, table}` tuple for a table **not** listed in `expected_tables` is informative output, not a Mix failure by itself. `{:expected_uncovered, _}` tuples are treated as covered-equivalent for the CI gate. It also supports a `--schema=NAME` flag with the same edge-validation contract as the new Mix task; default behavior is unchanged.
**Telemetry link.** When you need how those aggregate counts surface in metrics, see the [`[:threadline, :health, :checked]`](#threadline-health-checked) subsection under [`## Telemetry (operator reference)`](#telemetry-operator-reference).
## Correlation
**Correlation is not a database table** in Threadline. Correlation identifiers flow through headers (`x-correlation-id`), assigns, and optional fields on `AuditAction`. Treat them like trace context: they stitch logs and actions across boundaries without implying a `correlations` schema.
<span id="exploration-api-routing-v110"></span>
## Exploration API routing (v1.10+)
This block answers **“which public API first?”** for common exploration tasks. **SQL, golden queries, and subsection detail** live under **[Support incident queries](#support-incident-queries)** — use that section when you need copy-paste SQL or full filter vocabulary.
Contract marker for automated doc checks: **XPLO-03-API-ROUTING**
| Intent | Primary API | Notes / pointer |
|--------|---------------|-----------------|
| Single domain row over time | `Threadline.history/3` or `Threadline.timeline/2` | `history/3` lists changes for one PK; use `timeline/2` when you need the shared filter map (`:table`, `:from`, `:to`, …). **T0 / brownfield:** rows that existed before capture may look empty until the first audited write — see [`brownfield-continuity.md`](brownfield-continuity.md) and **[Brownfield continuity](#brownfield-continuity)** in this guide. |
| Incident / time window across rows | `Threadline.timeline/2` or `Threadline.timeline_page/2` | Use eager `timeline/2` for smaller bounded windows. Switch to `timeline_page/2` for large investigations where stable traversal across pages matters; bounds still apply to `AuditChange.captured_at` (see [subsection 1](#1-row-history-pk-changes-in-a-time-window)). |
| Correlation-scoped slice | `Threadline.timeline/2`, `Threadline.timeline_page/2`, `Threadline.Export`, `mix threadline.export` | Pass **`:correlation_id`**; timeline/export return only changes whose transaction **inner-joins** an `audit_actions` row with that correlation — see [subsection 3](#3-correlation-bundle-shared-correlation_id). |
| Everything in one DB transaction | `Threadline.incident_bundle/2` | Default transaction drill-down when you want linked transaction/action context plus ordered changes with packaged diffs. |
| Field-level diff for one `%AuditChange{}` | `Threadline.change_diff/2`, `Threadline.ChangeDiff` | Advanced building block for custom projections on top of `audit_changes_for_transaction/2` or `incident_bundle/2`; INSERT/UPDATE/DELETE semantics still live in the module docs. |
| Actor-scoped window (optional) | `Threadline.actor_history/2`, `Threadline.timeline/2` or `Threadline.timeline_page/2` with **`:actor_ref`** | Pairs with support table row 2; use the paged path when the actor window is too large for one eager list; SQL in [subsection 2](#2-actor-window-one-actor-across-tables). |
<span id="time-travel-as-of-v120"></span>
## Time Travel (As-of)
This hub maps the single-row **`as_of/4`** contract for operators who need one historical snapshot fast.
Contract marker for automated doc checks: **ASOF-06**
| Behavior | Result |
|----------|--------|
| Default call | Returns the stored snapshot as a **map**. |
| Deleted record | Returns an explicit deleted-record error instead of a fake struct. |
| Genesis gap | Returns an explicit genesis gap error when no historical row exists yet. |
| `cast: true` | Reifies into the current schema via `Ecto.embedded_load/3`; unknown keys are ignored and cast failures return `{:error, {:cast_error, message}}`. |
Use this when you need a one-row reconstruction by primary key. For a copy-paste walkthrough, see [the Phoenix example README](../examples/threadline_phoenix/README.md#historical-reconstruction-walkthrough).
<span id="example-incident-json-v111"></span>
### Reference example: incident JSON (v1.11+)
Contract marker for automated doc checks: **COMP-EXAMPLE-INCIDENT-JSON**
The path-dependent Phoenix app under **`examples/threadline_phoenix/`** shows the
canonical bundled incident path on top of the table above:
1. **`POST /api/posts`** returns **`audit_transaction_id`** — the UUID of the **`audit_transactions`** row for that HTTP request’s database transaction (after **`Threadline.Audit.transaction/3`** with **`:action`** records semantics and links **`audit_transactions.action_id`** in the same transaction).
2. **`GET /api/audit_transactions/:id/changes`** renders **`Threadline.incident_bundle/2`** for that transaction, returning linked transaction/action context plus ordered change rows with packaged **`change_diff`** payloads suitable for JSON incident tools.
If you need a custom projection instead of the bundled default, the lower-level
building blocks remain public: **`Threadline.audit_changes_for_transaction/2`**
preserves the ordering contract, **`Threadline.transaction_context/2`** exposes
the linked context directly, and **`Threadline.change_diff/2`** lets you shape
per-row diffs yourself.
CI covers the round-trip in **`ThreadlinePhoenixWeb.PostsIncidentJsonPathTest`**.
The reference app requires an authenticated actor before it serves the
drill-down endpoint. Production hosts still own tenancy scoping and any richer
authorization policy beyond that baseline.
## Support incident queries
SQL-native operator playbooks for the five canonical evidence-driving support questions. Run against a **read-only** session or **replica** when possible. Example SQL uses placeholder schema **`your_schema`** — replace it (and any `your_table` / PK literals) with your install’s names before executing.
**Replace before run:** `your_schema` → audited schema (often `public`); `your_table` / PK values → the row under investigation; time literals → bounded window; `your_correlation_id` → trace string from logs.
Contract marker for automated doc checks: **LOOP-04-SUPPORT-INCIDENT-QUERIES**
| # | Question | Primary path |
|---|----------|--------------|
| 1 | Row history — what changed for this domain row (PK) in the last N days? | `Threadline.history/3` or `Threadline.Query.timeline/2` — SQL: [subsection 1](#1-row-history-pk-changes-in-a-time-window) |
| 2 | Actor window — what did this actor drive across tables in a time window? | `Threadline.actor_history/2` or `Threadline.timeline/2` / `Threadline.timeline_page/2` with `:actor_ref` — SQL: [subsection 2](#2-actor-window-one-actor-across-tables) |
| 3 | Correlation bundle — row-level changes and semantic actions sharing a correlation id | `Threadline.timeline/2` / `Threadline.timeline_page/2` / export with `:correlation_id` — SQL: [subsection 3](#3-correlation-bundle-shared-correlation_id) |
| 4 | Export parity — same slice for review and export | `Threadline.Export`, `mix threadline.export` — details: [subsection 4](#4-export-parity-timeline-and-export-filters-agree) |
| 5 | Action ↔ capture — tie semantic actions to captured mutations | Join `audit_actions` ↔ `audit_transactions` — SQL: [subsection 5](#5-action-and-capture-link-semantic-actions-to-changes) |
### 1. Row history - PK changes in a time window
| Path | When to use it |
|------|----------------|
| **API** | `Threadline.history(MyApp.Schema, id, repo: MyApp.Repo)` returns `AuditChange` structs for one PK; use `Threadline.timeline/2` when you need the shared filter map (`:table`, `:from`, `:to`, …). |
| **SQL** | Ad-hoc psql / BI — join `audit_changes` to `audit_transactions`, constrain `table_name`, JSON containment on `table_pk`, and **bounded** `captured_at`. |
When **`:from`** / **`:to`** are set on `timeline/2` or `timeline_page/2`, bounds apply to **`AuditChange.captured_at`** (inclusive). Prefer **`LIMIT`** in raw SQL during exploration.
**Replace before run:** `your_schema`, `your_table`, PK map, timestamps.
```sql
SELECT ac.id,
ac.transaction_id,
ac.table_schema,
ac.table_name,
ac.op,
ac.captured_at,
ac.table_pk,
ac.changed_fields
FROM your_schema.audit_changes ac
JOIN your_schema.audit_transactions at ON at.id = ac.transaction_id
WHERE ac.table_name = 'your_table'
AND ac.table_pk @> '{"id": 123}'::jsonb
AND ac.captured_at >= '2026-04-01T00:00:00Z'::timestamptz
AND ac.captured_at <= '2026-04-24T23:59:59Z'::timestamptz
ORDER BY ac.captured_at DESC, ac.id DESC
LIMIT 500;
```
### 2. Actor window - one actor across tables
| Path | When to use it |
|------|----------------|
| **API** | `Threadline.actor_history/2` lists transactions for one `ActorRef`; combine with `Threadline.timeline/2` for smaller windows and `Threadline.timeline_page/2` for large windows when you need change rows across tables. |
| **SQL** | Filter `audit_transactions.actor_ref` (JSON) or join through capture rows — keep a **time bound** on `at.occurred_at` or `ac.captured_at`. |
**Replace before run:** `your_schema`, actor JSON literal, window bounds.
```sql
SELECT ac.id,
ac.table_name,
ac.op,
ac.captured_at,
ac.table_pk
FROM your_schema.audit_changes ac
JOIN your_schema.audit_transactions at ON at.id = ac.transaction_id
WHERE at.actor_ref @> '{"kind": "user", "id": "user-uuid-here"}'::jsonb
AND ac.captured_at >= '2026-04-20T00:00:00Z'::timestamptz
AND ac.captured_at <= '2026-04-24T23:59:59Z'::timestamptz
ORDER BY ac.captured_at DESC
LIMIT 500;
```
### 3. Correlation bundle - shared correlation_id
| Path | When to use it |
|------|----------------|
| **API** | `Threadline.timeline/2`, `Threadline.timeline_page/2`, `Threadline.Export` / `mix threadline.export` with **`:correlation_id`** in the filter list (same key as timeline). |
| **SQL** | Mirror library semantics with an **inner join** to `audit_actions` on the transaction’s `action_id`. |
**Strict semantics:** when **`:correlation_id`** is set to a non-empty string, **timeline** and **export** return only `audit_changes` whose **`audit_transactions`** row links to an **`audit_actions`** row with that **`correlation_id`** (via `action_id`). Capture rows for transactions **without** that action link **do not** appear — there is no “include orphan capture” mode for this filter. Omit `:correlation_id` entirely to leave correlation out of the filter (export may still `LEFT JOIN` actions for metadata without changing which changes match).
**Replace before run:** `your_schema`, `your_correlation_id`.
```sql
SELECT ac.id,
ac.table_name,
ac.op,
ac.captured_at,
ac.table_pk,
aa.id AS audit_action_id,
aa.correlation_id
FROM your_schema.audit_changes ac
JOIN your_schema.audit_transactions at ON at.id = ac.transaction_id
JOIN your_schema.audit_actions aa
ON aa.id = at.action_id
AND aa.correlation_id = 'your_correlation_id'
ORDER BY ac.captured_at DESC, ac.id DESC
LIMIT 500;
```
### 4. Export parity - timeline and export filters agree
| Path | When to use it |
|------|----------------|
| **Mix / API** | **`mix threadline.export`** (see task `@moduledoc`) and `Threadline.Export.to_csv_iodata/2`, `to_json_document/2`, `stream_changes/2` — same allowed keys as `Threadline.Query.timeline/2`. |
| **SQL** | Use when validating parity in the database; **replicate the same predicates** you pass to `timeline/2` (table, actor, time bounds, correlation inner join when filtering by correlation). |
Unknown filter keys raise **`ArgumentError`** in both code paths — see `Threadline.Query` moduledoc.
### 5. Action and capture - link semantic actions to changes
| Path | When to use it |
|------|----------------|
| **API** | `Threadline.record_action/2` sets semantic intent; capture links when the transaction’s `action_id` points at the `audit_actions` row driving that transaction. |
| **SQL** | Start from `audit_actions`, join `audit_transactions`, then `audit_changes`. |
**Replace before run:** `your_schema`, `your_action_id`.
```sql
SELECT aa.id,
aa.name,
aa.correlation_id,
at.id AS transaction_id,
ac.id AS change_id,
ac.table_name,
ac.op,
ac.captured_at
FROM your_schema.audit_actions aa
JOIN your_schema.audit_transactions at ON at.action_id = aa.id
JOIN your_schema.audit_changes ac ON ac.transaction_id = at.id
WHERE aa.id = 999001
ORDER BY ac.captured_at DESC
LIMIT 500;
```
red_at DESC
LIMIT 500;
```
ORDER BY ac.captured_at DESC
LIMIT 500;
```
red_at DESC
LIMIT 500;
```