Database Migrations

Database schema is a platform contract. Authoritative migrations live in packages/database/migrations.

Tooling

Phase 6 uses node-pg-migrate with SQL migration files:

• base migrations: packages/database/migrations/base
• opt-in Timescale policy migrations: packages/database/migrations/timescale-policies
• base migration history table: sens_schema_migrations
• Timescale policy migration history table: sens_timescale_policy_migrations

The separate migration history tables are intentional. If automatic Timescale policies are disabled, policy migrations are skipped and are not marked as applied.

Runtime Configuration

Variable	Required	Secret	Default	Purpose
SENS_DATABASE_URL	yes	yes	none	Target database for runtime and normal migrations.
SENS_TEST_DATABASE_URL	test only	yes	none	Target database for test migrations and DB integration tests.
SENS_DATABASE_POOL_MAX	no	no	10	Maximum pool size per service instance.
SENS_DATABASE_CONNECTION_TIMEOUT_MS	no	no	5000	Connection-open timeout.
SENS_DATABASE_IDLE_TIMEOUT_MS	no	no	30000	Idle pooled connection timeout.
SENS_DATABASE_ENABLE_TIMESCALE_POLICIES	no	no	false	Enables automatic retention/compression policy migrations.
SENS_DATABASE_MIGRATION_STARTUP_TIMEOUT_MS	no	no	120000	Maximum wait for database readiness before migration failure.

The Kubernetes migration Job may use the following structured connection variables instead of SENS_DATABASE_URL:

Variable	Required in structured mode	Secret	Default
SENS_DATABASE_HOST	yes	no	none
SENS_DATABASE_PORT	no	no	5432
SENS_DATABASE_NAME	yes	no	none
SENS_DATABASE_USER	yes	no	none
SENS_DATABASE_PASSWORD	yes	yes	none

Database URLs must be supplied through environment variables or Kubernetes Secrets. Production, staging, customer, or shared credentials must not be stored in source files, Helm values, examples, tests, or container images.

In Kubernetes, the password is injected from the existing environment-specific TimescaleDB authentication Secret. The migration runner constructs the connection URL in memory and does not log it.

Commands

Run base migrations against the configured database:

SENS_DATABASE_URL=postgresql://... pnpm db:migrate

Run migrations against a test database:

SENS_TEST_DATABASE_URL=postgresql://... pnpm db:test:migrate

Create a new SQL migration in the base migration directory:

pnpm --filter @sens-platform/database run db:migrate:create -- add-name-here

TimescaleDB Policies

Base migrations always create TimescaleDB hypertables for:

• raw_ingestion_messages, partitioned by received_at
• measurements, partitioned by observed_at

Automatic policies are opt-in:

SENS_DATABASE_ENABLE_TIMESCALE_POLICIES=true pnpm db:migrate

The opt-in policy migrations prepare:

• measurement compression after 30 days,
• removal of older automatic raw-ingestion retention policies,
• no automatic measurement retention.

The current local and embedded deployment image is timescale/timescaledb:2.28.1-pg17-oss. This Apache-2 build supports hypertables, but automatic retention and compression policy APIs require a TimescaleDB deployment with Community/TSL policy support. When policy opt-in is enabled against an unsupported database, the migration runner fails before running policy migrations and explains the missing prerequisite.

The hourly Helm CronJob is the only authoritative raw-ingestion lifecycle. It uses Apache-2-compatible drop_chunks for complete chunks, removes unregistered DevEUIs after 7 days, removes all other raw rows after 30 days, and deletes idempotency keys only after their 49-day expiry. The repository performs the row cleanup in bounded batches.

Repository Boundary

Application code must access persistence through @sens-platform/database repositories or explicit database adapters. Tenant-owned repository methods require tenantId in their input and use parameterized SQL.

Raw ingestion idempotency uses the normal PostgreSQL table raw_ingestion_idempotency_keys. This is separate from the raw_ingestion_messages hypertable because TimescaleDB unique constraints on a hypertable must include the time partition column.

Telemetry reads must be bounded by tenant, from, to, and limit. Unbounded measurement queries are not allowed.

Public Tenant-number migration

20260810000100000_tenant_numbers.sql adds the public tenant_number without changing any existing Tenant UUID, foreign key, dependent tenant_id column, or TimescaleDB hypertable. Existing Tenants are numbered from 1 in stable created_at, tenant_id order. PostgreSQL then owns the GENERATED ALWAYS AS IDENTITY sequence; the migration advances it above the backfill maximum and enforces positive, unique, non-null, immutable values. Deleted or disabled numbers are never allocated again by the application.

Before rollout, create and verify a recoverable backup or a disposable copy of the populated database. After the migration, verify:

select tenant_number, tenant_id, created_at
from tenants
order by tenant_number;

select count(*) as tenants,
       count(distinct tenant_number) as distinct_numbers,
       min(tenant_number) as minimum_number
from tenants;

select last_value, is_called
from tenants_tenant_number_seq;

The Tenant count and distinct-number count must match, the minimum must be at least 1, and the next inserted Tenant must receive a number above the current maximum. Run application-level cross-Tenant isolation checks before promotion.

Apply the additive migration before the coordinated API and Web Console rollout. The API route change is intentionally incompatible and has no aliases. The SQL down section removes only the number column, sequence, trigger, and function; after numeric routes are released, production recovery is forward-fix only. No new Secret, environment variable, Helm value, service, or external dependency is introduced.

The Device-Type Flow V2 migration intentionally resets development Flow data and requires a verified backup, coordinated application rollout, and derived-stream purge. Follow the dedicated Device-Type Flow V2 migration runbook before applying 20260806000100000_device_type_field_contract_flow_v2.sql.

The Phase 7A additive auth migration adds usernames, local credentials, global platform roles, sessions, refresh-token hashes, login throttles, and anonymous audit actors. Existing platform_admin memberships become global assignments; tenant memberships then accept only tenant_admin, org_admin, and viewer with role/scope constraints. The old API image does not depend on the new tables, so the migration can precede the application rollout.

The additive Phase 7B/7C migration 20260803000200000_core_admin_audit.sql adds the CLI protection marker, backfills earlier auth.platform_admin_bootstrapped events, and installs a trigger that remains compatible with the previous CLI image. It adds organization-cycle prevention, administration cursor indexes, and the request audit columns and constraints. Existing audit rows retain safe defaults as security_event/success, so the previous API image remains compatible.

Run this migration through the existing Kubernetes migration Job before the new API image. Production rollback means rolling back only the API image and leaving the additive schema in place. Do not run the down section in production; forward-fix any schema defect. The request-audit indexes and one begin plus one finalize write per /v1 call increase database write and storage load and must be included in capacity monitoring.

Phase 7D resource inventory migration

20260803000300000_resource_inventory_lifecycle.sql converts the provisional device and decoder tables into their published lifecycle model. Before rollout, operators must run these read-only preflight queries:

select device_id, tenant_id
from devices
where dev_eui is null;

select decoder_version_id, tenant_id, device_type_id, checksum
from decoder_versions
where checksum is null
   or lower(btrim(checksum)) !~ '^sha256:[a-f0-9]{64}$';

select decoder_version_id, tenant_id, device_type_id, module_ref
from decoder_versions
where btrim(module_ref) = ''
   or char_length(btrim(module_ref)) > 200;

Historical null DevEUIs do not block the migration: the new required constraint is NOT VALID, but it rejects every new null write. Resolve each reported legacy row with its authoritative Network Server identity, uppercase it, verify global uniqueness, and then validate devices_dev_eui_required in a later controlled maintenance step. Never synthesize or recycle a DevEUI.

Invalid or missing decoder checksums do block the migration because the new immutable contract requires a canonical value. Resolve each checksum from the deployment-controlled module artifact and store it as sha256:<64 lowercase hexadecimal characters> before rollout. Do not invent a checksum merely to pass the migration.

Blank or oversized module references also block the migration. Replace them with the bounded deployment-controlled module identifier that resolves to the same artifact; the migration trims otherwise valid legacy references before it protects their content from later changes.

The migration deterministically numbers old versions by created_at and ID, maps the old active row to the device-type pointer, maps disabled versions to blocked, and preserves the first activation timestamp. It then removes the old status and text-version columns. Composite constraints keep the pointer in the same tenant and device type; triggers reject blocked pointers, DevEUI changes, and decoder-content changes.

Run the migration Job before the Phase 7D API image. No new environment variable, secret, Helm value, service, or external dependency is introduced. Existing API images do not publish device/decoder endpoints, but operators must still avoid down-migrating under live traffic. The down section can only reconstruct legacy text versions from the new sequential numbers and cannot restore the previous semantic model. Production recovery is therefore a forward fix: correct the schema or data, rerun validation, and deploy a repaired image. Preserve a tested database backup before rollout; a volume is not a backup.

Netmore raw-ingestion migration

20260804000100000_netmore_raw_ingestion.sql is additive. It adds optional device scope, network-server service provider/customer, MQTT duplicate state, exact transport bytes, nullable decoded sensor payload, malformed status, and the composite tenant/device foreign key. Existing rows may leave new columns null; the new repository path always supplies the required transport and network-server values.

RawIngestionMessagesRepository.storeIdempotently resolves the globally unique DevEUI and creates the idempotency key plus raw row in one SQL statement. Worker code contains no SQL. The migration also adds expires_at and its cleanup index to idempotency keys.

The opt-in policy migration 20260809000100000_raw_ingestion_retention.sql replaces the former 35-day Timescale retention policy with a 7-day policy. The later migration 20260824000100000_raw_ingestion_retention_cronjob_authority.sql removes that policy because it cannot express the DevEUI-dependent 7-day/30-day lifecycle. The raw-retention CronJob is authoritative whether optional Timescale policies are enabled or not. Measurement compression remains unchanged.

20260902000100000_published_outbox_retention.sql adds the partial outbox_events_published_retention_idx index over delivered rows. The hourly CronJob uses that index to delete only rows whose published_at is older than SENS_OUTBOX_PUBLISHED_RETENTION_DAYS, in bounded batches. The default is seven days. Rows with published_at IS NULL are pending delivery and are never eligible for this cleanup. The migration does not delete data; cleanup begins only when the retention image runs.

Production rollback is forward-only. Do not down-migrate this change under live traffic. The development down section refuses to continue when nullable raw payload rows exist rather than deleting malformed data.

Retention can be run manually with the database image:

SENS_DATABASE_URL=postgresql://... \
SENS_OUTBOX_PUBLISHED_RETENTION_DAYS=7 \
SENS_RAW_RETENTION_DAYS=30 \
SENS_RAW_UNREGISTERED_DEVICE_RETENTION_HOURS=168 \
pnpm --filter @sens-platform/database raw:retain

The command emits one structured summary or exits non-zero. Local validation must prove that currently unregistered DevEUIs and malformed rows expire after 7 days, DevEUIs registered before cleanup remain available for the 30-day lifecycle, all raw rows expire after 30 days, delivered outbox rows expire after 7 days, and pending outbox rows remain untouched.

Tests

Normal unit tests do not require a running database:

pnpm --filter @sens-platform/database test

Database and auth integration tests are skipped unless both variables are set:

SENS_RUN_DATABASE_INTEGRATION_TESTS=true
SENS_TEST_DATABASE_URL=postgresql://...

On a dedicated Community/TSL-enabled TimescaleDB test database after policy migrations have run, additionally set SENS_RUN_TIMESCALE_POLICY_INTEGRATION_TESTS=true. The gated test proves that measurement compression remains installed and no automatic Raw retention job can undercut the CronJob lifecycle.

CI runs the database, HTTP, ingestion, JetStream, and telemetry-pipeline integration suites against the pinned OSS image. It does not enable the Community/TSL policy suite because timescale/timescaledb:*-oss reports the Apache license and cannot install automatic retention or compression policies. Run that one suite separately against the licensed deployment before enabling those policies in an environment.

Run the test database migration first, then run the gated integration tests:

SENS_TEST_DATABASE_URL=postgresql://... pnpm db:test:migrate
SENS_RUN_DATABASE_INTEGRATION_TESTS=true \
  SENS_TEST_DATABASE_URL=postgresql://... \
  pnpm exec vitest run packages/database/src/integration.test.ts

SENS_RUN_DATABASE_INTEGRATION_TESTS=true \
  SENS_TEST_DATABASE_URL=postgresql://... \
  pnpm exec vitest run services/platform-api/src

The gated raw-ingestion end-to-end test additionally requires a real dedicated NATS instance and the bootstrap, publisher, and consumer credentials documented in Netmore Raw Ingestion. It publishes through JetStream and verifies exactly one TimescaleDB row.

Rollback Notes

Migrations include down sections for development rollback. Production rollback selects the previous API image and leaves additive auth tables and credentials in place. Do not down-migrate or delete credentials as an auth release rollback; correct schema defects forward. Volumes are not backups.

Kubernetes Release Execution

Each immutable application release includes a dedicated database migration image. The Helm chart renders a migration Job that uses the same image tag as the release, waits for database readiness, and runs before application Deployments through Argo CD sync waves. Failed migrations block the application rollout and require operator review; they are not silently skipped.