mix threadline.gen.triggers (Threadline v0.12.0)

Copy Markdown View Source

Generates an Ecto migration that installs Threadline audit triggers on the specified tables.

Usage

mix threadline.gen.triggers --tables users
mix threadline.gen.triggers --tables users,posts,comments

Each invocation writes one migration whose statements create or replace the audit trigger on each listed table (CREATE OR REPLACE TRIGGER, PostgreSQL 14 or later). Run mix ecto.migrate to apply.

The trigger calls threadline_capture_changes(), which must already be installed via mix threadline.install.

With before-values capture for opted-in tables:

mix threadline.gen.triggers --tables posts --store-changed-from
mix threadline.gen.triggers --tables posts --store-changed-from --except-columns secret_token,internal_score

That gives each listed table its own capture function and wires its trigger to it. The function name is derived from the table's schema and name: a table in the public schema whose name is at most 36 bytes gets threadline_capture_changes_ followed by the table name, and any other table gets a shortened name ending in a hash of its schema and table, so no two tables share a function. Migrations generated without --store-changed-from keep the default global threadline_capture_changes() trigger body unless the table has :exclude / :mask rules under config :threadline, :trigger_capture (see README).

Primary keys

When the generated migration runs, it reads the table's real primary key from the database and passes its columns to the capture function as trigger arguments, so audit rows record the real key rather than always assuming a column named id. This covers a single primary-key column of any supported name, and a composite primary key of more than one column. A primary key with INCLUDE columns only passes its actual key columns; INCLUDE columns are never part of the trigger arguments.

Supported primary-key column types: smallint, integer, bigint, text, varchar, char, citext, uuid, date, timestamp (without time zone), enum types, and domains over any of these. A column outside this list, such as timestamptz, numeric, a float, json/jsonb, an array, or bytea, has no stable text form for an audit key and stops the migration, naming the column and its type.

A table with no primary key needs a primary_key: entry in config/config.exs naming a unique index over NOT NULL columns. For a Phoenix many_to_many join table such as posts_tags, with a unique index over (post_id, tag_id):

config :threadline, :trigger_capture,
  tables: %{"posts_tags" => [primary_key: ["post_id", "tag_id"]]}

Then regenerate:

mix threadline.gen.triggers --tables posts_tags

A table that already has a primary key must not also declare primary_key: — Threadline discovers it automatically, and the migration stops with a hint to remove the redundant entry. A primary-key or declared primary_key: column that also appears in the table's :mask or :exclude stops the migration too: redacting a key column would erase row identity from the audit trail.

A trigger generated by a release before this primary-key resolution keeps capturing exactly as it did until you regenerate it; regenerating upgrades it to a trigger that resolves the real key. When a write's primary key cannot be resolved — such as before regenerating, or when every key column would come back NULL — the row's table_pk is recorded as an empty {} rather than failing the write.

Redaction (config :threadline, :trigger_capture)

At task start the host app config is loaded (Mix.Task.run("app.config", [])). Per-table entries under :tables may set :exclude, :mask, optional :mask_placeholder, :store_changed_from, :except_columns, and :primary_key (see "Primary keys" above). Overlap between :exclude and :mask is validated before writing the migration. A key names a table the way --tables does, so posts and public.posts are the same table; one table under two keys stops the task.

Rerunning

Run the task again for tables that already have a trigger migration, for example after changing :trigger_capture redaction rules or to clear a Drift detected status. It writes a new migration. When the table-derived name is already taken, the migration gets a numbered name, such as threadline_triggers_posts_2, and a matching numbered module. A rerun for a different table set, such as posts after posts,users, keeps the un-numbered threadline_triggers_posts. The new migration replaces the trigger in place, so capture has no gap.

A capture function the migration no longer needs, such as the per-table function of a table that returns to the default trigger, is dropped only after every trigger in the migration is re-pointed, and only when no trigger still uses it. If a trigger on some table still uses it, the function is kept and mix ecto.migrate prints a WARNING naming those tables. The drop never cascades, and a function that does not exist is skipped.

Rolling back a rerun migration keeps capture on for the tables it re-pointed. Rolling back does not restore the earlier capture policy: the trigger keeps the policy the rerun installed. If the rerun had removed redaction rules, a rolled back rerun leaves capture running unredacted until you regenerate, and mix threadline.policy.show flags the mismatch once your config lists those rules again. The generated down says the same in a comment. To stop capturing a table, write a migration that drops its trigger.

Rolling back the whole chain is different from rolling back just the rerun. The first trigger migration's down drops its table's per-table capture function if this migration or a later rerun created one, as long as no trigger still uses it — even though the rerun's own down leaves the table alone. So a full rollback of a default-then-rerun chain removes the per-table function the rerun added, with no orphan left behind.

Tables that shared a capture function

Releases up to 0.10.2 could give two tables one capture function. For example, public.billing_invoices and billing.invoices both used threadline_capture_changes_billing_invoices, so one table could apply the other's redaction rules. Later releases give billing.invoices a hashed name of its own, and public.billing_invoices keeps the old one.

Two orders are safe: regenerate both tables in one command, or regenerate the table outside public (or the table with the long name) first:

mix threadline.gen.triggers --tables billing_invoices,billing.invoices

If you regenerate only the table that keeps the old name, mix ecto.migrate stops with an error naming the other tables whose triggers still use that function. Its hint gives the command to run instead, such as mix threadline.gen.triggers --tables billing_invoices,billing.invoices. Nothing from that migration is applied, so capture keeps running as before. Delete the refused migration file, then generate the suggested one.

A WARNING from mix ecto.migrate that keeps a capture function because triggers on some tables still use it means those tables should be regenerated too. The WARNING also appears when a table in the public schema legitimately owns that function name, for example after regenerating billing.invoices on its own. Regenerating the tables it names is always safe.

Options

  • --tables — comma-separated list of table names (required). Each table is listed once: posts,public.posts names one table twice and stops the task.
  • --store-changed-from — emit per-table capture functions that persist sparse changed_from JSON on UPDATE (default: off)
  • --except-columns — comma-separated column names excluded from both changed_fields and changed_from when --store-changed-from is set (alphanumeric and underscore only). Merged with :except_columns from config.
  • --dry-run — print table=… exclude=… mask=… per table and skip writing a migration
  • --migrations-path — directory the migration is written to, used as given relative to the current directory. The repo is not loaded.
  • --repo / -r — one repo; the migration goes to that repo's migrations directory, the same place mix threadline.install writes.

Without --migrations-path or --repo, the task uses the first repo in :ecto_repos and its :priv setting. A configured repo that cannot be loaded falls back to priv/repo/migrations, with a warning naming it. In an umbrella, run the task from the child app's directory.

Long table names

In default capture mode, a table whose trigger name would exceed 63 bytes gets the name PostgreSQL would store, threadline_audit_<table> cut to 63 bytes. Per-table mode (redaction rules or --store-changed-from) works for long tables and for tables outside the public schema: their capture functions get hashed names that fit in 63 bytes. A --tables value that is not a valid identifier, or is longer than 63 bytes, stops the task with an error that names the host table and its size in bytes.

Guards

The task exits non-zero if audit_transactions or audit_changes is in the table list. Installing audit triggers on Threadline's own tables would cause recursive loops in the audit tables themselves.