Migrations
nxs-anomaly manages its PostgreSQL schema through SQL migrations embedded in the binary.
Where they live
The canonical path:
internal/store/migrations/*.sqlThe files are embedded at build time:
//go:embed migrations/*.sql
var migrationsFS embed.FSNo other path is used for migrations.
How they run
Migrations run automatically when the store is created:
store.NewPostgreSQLStore(ctx)In order:
- connect to PostgreSQL;
- take advisory lock
72544000; - create
nxs_anomaly_schema_migrationsif it does not exist; - sort
migrations/*.sqlby file name; - for each file, check whether its version is recorded;
- apply the missing ones, each in its own transaction;
- record the applied version;
- release the advisory lock.
Critical data updates use their own advisory lock keys inside the engine, so that schema initialisation and domain operations never contend for one lock.
The version table
CREATE TABLE IF NOT EXISTS nxs_anomaly_schema_migrations (
version TEXT PRIMARY KEY,
applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);A version is the file name without its .sql extension:
0001_initial_schema
0002_typed_columnsThe migrations
| Version | What it does |
|---|---|
0001_initial_schema | The JSONB tables for every entity, and the first expression indexes. |
0002_typed_columns | Typed columns for hot-path fields: users, teams, schedules, integrations, chatops, mobile, alerts, alert_groups, notifications. |
0003_constraints_and_notification_retries | Retry metadata (retry_count, next_retry_at, last_error), a unique index on idempotency_key, deferred foreign keys. |
0004_alertmanager_channels_and_batches | A source_type column, the nxs_anomaly_notification_batches table, batch columns on notifications. |
0005_delivery_attempts | nxs_anomaly_notification_delivery_attempts, for auditing and debugging delivery. |
0006_duty_epic_templates | On-duty flags on users, epic-tracking fields on alert_groups. |
0007_notification_batches_group_idx | Typed alert_group_id/integration_id on batches, and an index for archiving finished groups. |
0008_delivery_scheduled_idx | A partial index for status='delivery_scheduled' — the worker's hot path. |
0009_alerts_archival_idx | An alert_group_id index on alerts, for the cascading cleanup during archival. |
0010_validate_constraints | VALIDATE CONSTRAINT for everything added NOT VALID earlier. |
0011_grafana_compat | Tables for a Grafana plugin compatibility layer. The layer has been removed; the tables remain unused — see "Retired tables" below. |
0012_kafka_outbox | Table nxs_anomaly_kafka_outbox plus a created_at index, for a transactional outbox (asynchronous alert export to Kafka, published FIFO by the worker cycle). The schema is identical in both editions — moving between them is meant to be an image swap — but the producer that fills this table is an enterprise feature; in this build the table exists and stays empty. |
0013_soft_delete_integrations | A deleted_at column on integrations plus a partial index. Soft delete: a removed integration disappears from listing and ingest but stays in the database. |
0014_mobile_verification_tokens | The nxs_anomaly_mobile_verification_tokens table plus an index on expires_at. QR verification tokens of the removed compatibility layer; since 0030, hashes of phone pairing codes. |
0015_chatops_messages_created_at_idx | An expression index on ((data->>'created_at')) for chatops messages, which speeds up the TTL archival of old messages. |
0016_due_groups_idx_predicate | Rebuilds nxs_anomaly_alert_groups_due_idx with the predicate status NOT IN ('resolved','silenced'), matching what the worker actually queries — the old status='open' predicate did not cover acknowledged groups. |
0017_notification_claim | A partial index on (status) WHERE status IN ('delivering','retrying') for claim-then-deliver — safe delivery across several workers — and for the reaper that frees stuck claims. |
0018_audit_events | nxs_anomaly_audit_events, an append-only trail of every mutating operation. A group's timeline (data->'logs') will not do: it is capped by maxGroupLogs, rewritten on every write to the group, and covers only groups. Append-only is held by a trigger rather than by grants, because the service owns its schema and connects as the table's owner — so pruning requires deliberately disabling the trigger. |
0019_identity_sessions | nxs_anomaly_user_credentials and nxs_anomaly_web_sessions. Credentials live outside nxs_anomaly_users.data on purpose: that JSONB is returned verbatim through the generic collection endpoints, so a password hash inside it would be one GET /api/v1/users away from disclosure. Sessions store the SHA-256 of a token rather than the token, so a database dump does not hand over live sessions. |
0020_integration_team | A team_id column on integrations. The team boundary is drawn here: alerts and alert groups already carry integration_id, so "my team's groups" is a filter on that rather than a copy of team_id that would go stale when an integration is reassigned. NULL means unassigned and visible to everyone. |
0021_notification_integration | An integration_id column on notifications, so delivery records scope by team too. The one place where denormalisation is right: a copy of team_id would go stale on reassignment, a copy of integration_id cannot, because an alert group does not move between integrations. Before this, notifications and delivery attempts — including the message text — were readable by any authenticated user. |
0022_audit_request_id | A request_id column on audit events plus a partial index over non-empty values. Every request already carries X-Request-ID in the access log and in error responses; without it on the audit row, "what else happened in that same request" was answered by matching timestamps, which stops working exactly when it is needed — under load, and when one request produces several events. |
0023_notification_policy_runs | nxs_anomaly_notification_policy_runs plus indexes on next_step_at (active runs) and on the (group, user) pair. A personal notification policy is a long-lived state machine — notify → wait → fall back — driven by the worker cycle, and keeping it in the database is what lets it survive a restart. Finished runs are bookkeeping rather than history: they are marked done and swept during archival. |
0024_login_rate_buckets | nxs_anomaly_rate_buckets plus an index on updated_at. The sign-in token bucket lives in the database so the limit is cluster-wide rather than per pod — otherwise a password guess would get N attempts against N replicas. The rows are ephemeral and cleaned by the worker's retention sweep. See SECURITY_PROFILE.md. |
0025_audit_trace_id | A trace_id column on audit events plus a partial index. It sits beside request_id from 0022: a request id ties together what one request did, a trace id ties a chain across processes and time — ingest in an API pod and delivery in a worker cycle an hour later are different requests. See TRACING.md. |
0026_maintenance_windows | nxs_anomaly_maintenance_windows plus an index on ends_at. Planned work: integrations listed in a window still record alerts inside its bounds but do not page — the group is created silenced, and the dead-man switch stays quiet for them. Expand-only, and nothing outside the feature reads the table, so rolling back to an earlier release simply stops looking at it. |
0027_retention_indexes | Indexes for the retention sweep: a partial one on updated_at of terminal notifications, ones on created_at of delivery attempts and web sessions, and one on actor_id in the audit trail. The sweep asks the same question — "the oldest N rows past the cutoff" — every worker cycle; without indexes that is a sequential scan of the largest tables every few seconds, on exactly the installation where they are large because retention was just switched on. The actor index also serves pseudonymisation when a user is deleted. See DATA_INVENTORY.md. |
0028_oncall_quality_reports | Persist on-call quality reports: team, period, generation time, format version and JSON digest; index on (team_id, generated_at). |
0029_silence_expiry | Make time-limited silences end: silenced groups with silenced_until get it as next_run_at, so the worker returns them to open. Before this, a silence or maintenance window never expired. |
0030_mobile_session_hardening | Make mobile sessions a credential: an expires_at column, the token stored as SHA-256. Sessions issued before (48-bit plaintext tokens, no expiry) are revoked and their tokens erased. The mobile_verification_tokens table from 0014 is emptied and holds hashes of one-time phone pairing codes. |
0031_chatops_status_replies | Shrink stored ChatOps status replies to the shape 1.9.2 writes: at most 50 groups as id/title/severity/status plus open_count and truncated. Earlier replies carried every open group whole, logs included — megabytes per call. Data only, touches only replies in the old shape; the freed space returns to PostgreSQL after vacuum. |
0032_alert_groups_chain_idx | A partial index on escalation_chain_id of unresolved alert groups. A paired phone asks on every event-stream tick which open groups concern its person; that is now asked by the chains that name them, and this index keeps it a lookup. Index only; rolling back leaves it unused. |
0033_alert_groups_recent_unresolved_idx | A partial index on (last_received_at DESC, id) of unresolved alert groups. A chat's status and alerts now count and page the open groups in the database, newest first, instead of loading them all; this index is their order. Index only. |
0034_list_default_order_idx | Indexes in the exact default order of the alert, alert group and notification listings (time column DESC NULLS LAST, then id DESC). Without them every page sorted the whole table. |
0035_alerts_firing_idx | A partial index on firing alerts in the listing's order. The alert list filtered to firing counts and pages from it instead of reading the table. |
0036_alert_groups_insights_idx | A covering index on alert groups' created_at with status and severity included. The insights tiles and trend are answered from it instead of reading the table twice. |
0037_drop_alert_groups_status_json_idx | Drops the unused expression index on data->>'status' of alert groups (CONCURRENTLY, no transaction). An index on an expression over data kept every group update from being a HOT update. |
0038_drop_alert_groups_next_run_json_idx | Drops the unused expression index on data->>'next_run_at' of alert groups (CONCURRENTLY, no transaction); the typed due index answers that lookup. |
The connection to the store code
internal/store/store.go defines:
EntityTables— collection name → PostgreSQL table;TypedColumns— the extra typed columns written on upsert;typedValues— the values for those columns.
Adding or removing a typed column in a migration means updating TypedColumns and typedValues in the same change. Integration tests cover this path, because the store writes through the real migrations.
The domain entities, their relationships and their life cycles are described in DOMAIN_MODEL.md. Migrations must stay compatible with that model: a table holds the whole JSONB document, and typed columns are an indexable projection of the hot-path fields.
Adding a migration
- Create the next file in order:
internal/store/migrations/0039_short_description.sql- Make the operations idempotent where you can:
ALTER TABLE nxs_anomaly_alert_groups
ADD COLUMN IF NOT EXISTS example text;
CREATE INDEX IF NOT EXISTS nxs_anomaly_example_idx
ON nxs_anomaly_alert_groups (example);If it adds a typed column, update
TypedColumnsandtypedValuesinstore.go.Add or update the tests covering the new path through the schema.
Check:
go test ./...
tests/run_postgres_integration.shOperational notes
- Migrations are automatic; there is no separate CLI to run them.
- Back the database up before a production upgrade.
- For a heavy migration — backfilling a large table — roll out a single instance first.
- Avoid blocking DDL in an ordinary migration. The preferred pattern is additive nullable column → backfill → index → validate.
- Add constraints over existing data
NOT VALID, then validate in a separate migration, as0010_validate_constraintsdoes.
Non-blocking migrations (CREATE INDEX CONCURRENTLY)
An ordinary migration runs inside a transaction, which is incompatible with CREATE INDEX CONCURRENTLY — and which takes an ACCESS EXCLUSIVE lock on large tables. To add an index without blocking, start the file with a marker:
-- nxs:no-transaction
CREATE INDEX CONCURRENTLY IF NOT EXISTS nxs_anomaly_example_idx
ON nxs_anomaly_alerts (example);Such a migration runs outside a transaction, and therefore must be idempotent (IF NOT EXISTS): if it fails it can be left partly applied and unrecorded. After a failed CONCURRENTLY, PostgreSQL leaves an INVALID index behind, which has to be dropped before retrying.
Checking what has been applied
SELECT version, applied_at
FROM nxs_anomaly_schema_migrations
ORDER BY version;The expected set of versions is asserted by the Go integration tests.
Retired tables
The Grafana plugin compatibility layer has been removed, together with the grafana_plugins entity and the QR verification tokens. Four tables remain in the schema and are read by nothing:
nxs_anomaly_grafana_plugins(from0001);nxs_anomaly_grafana_notification_policies,nxs_anomaly_grafana_channel_filters,nxs_anomaly_grafana_heartbeats(from0011).
nxs_anomaly_mobile_verification_tokens (from 0014) is in use again since 0030: it holds hashes of one-time phone pairing codes.
They were deliberately not dropped in the same release. The expand/contract rule in BACKUP_RESTORE.md requires the previous binary to run against the migrated schema — which the rollback drill verifies. A DROP TABLE in the release that removes the code would make rolling back to the previous tag impossible, because that binary still touches these tables.
They should be dropped by a separate migration in the next release, once rolling back to a version with the layer is no longer a scenario.