PostgreSQL persistence

@helyx/database is the published PostgreSQL adapter used by approved Helyx runtime packages. Browsers and feature modules never connect to it directly.

The bot verifies connectivity and applies core migrations before modules or Discord become active. Core and module migration histories are ordered, contiguous, transactionally applied under advisory locks, and protected by SHA-256 checksums. A changed, missing, downgraded, or failed migration prevents readiness. Removing a module package retains its schema and migration history.

A module migration may include one same-stem JSON seed companion, for example 0003_catalogue.sql and 0003_catalogue.seed.json. The runner validates the bounded JSON object, binds its raw content to the SQL migration as $1, and includes both exact files in the applied checksum. Seeded migrations remain immutable after release, just like SQL-only migrations.

Repository classes own parameterised SQL for installations, configuration, permissions, sessions, audit events, idempotent control operations, distributed rate limits, subscriptions, and entitlements. Versioned writes use optimistic concurrency, and related mutations/audits can share withTransaction. @helyx/platform exposes these capabilities through SDK contracts.

Account-owned Premium billing uses the separate billing_customers, premium_subscriptions, premium_slots, billing_provider_events and billing_operations boundary. It deliberately does not reinterpret the legacy guild-owned subscriptions table. Stable slot records, provider event receipts and billing-operation idempotency support asynchronous provider reconciliation without putting payment calls on Discord runtime paths.

The Bot runs bounded hourly cleanup for expired interaction sessions and rate-limit windows. The hosted API separately cleans expired OAuth state and expired or revoked dashboard sessions. It also reconciles bounded cross-database OAuth orphans after a creation grace period. Repository cleanup methods remain available to other approved deployment processes without creating background work merely by importing this package.

EckoOperationsRepository.history({ from, through, now? }) reads bounded, content-free telemetry from the retained minute, hourly or daily tier selected by the age of the requested range. Bounds are UTC, Through is exclusive and partial source buckets are excluded. The response reports its effective bounds and source resolution; missing observations are not invented zero readings. Counters are attributed to batch end, scan-compute histograms are capped at 5,000 ms, and queue readings are bucket maxima rather than live queue depth. No private staff tables, permissions or dashboard routes belong to this API. Calling the read does not start a scanner, collector or cleanup worker.

Each bounded orphan pass advances updated_at only for active bindings found in both databases. That operational timestamp acts as a rotating cursor so a large healthy population cannot permanently hide later orphan rows; the separate user-visible connected_at value is unchanged.

Run core migrations with an approved database:

npm run migrate -w @helyx/database

The hosted OAuth vault has a separate migration lifecycle. Its running API identity never applies DDL. The one-off command requires the runtime vault URL and HELYX_OAUTH_MIGRATION_DATABASE_URL, verifies that both reach the same vault under different principals, applies the immutable vault migrations only through the migration identity, and restores the fixed least-privilege runtime grants:

npm run oauth:migrate -w @helyx/database

Do not configure the migration URL on the running API. Normal API startup reads the OAuth migration ledger and fails closed on pending, unknown or changed migrations.

Packaged installations use helyx migrate; helyx doctor checks connectivity and immutable migration history without changing the database. Use DATABASE_SSL=verify-full whenever the provider supplies a publicly trusted certificate chain. require encrypts transport without certificate verification and should only be used when the provider cannot support verification.

Helyx Verification uses the dedicated verification schema and the restricted helyx_verification_executor login. Apply core migrations with the owner connection before inspecting or provisioning the role. Provisioning requires HELYX_VERIFICATION_EXECUTOR_PASSWORD, RAILWAY_ENVIRONMENT_NAME, and an exact --confirm-environment=<name> argument:

npm run verification:role:check -w @helyx/database
npm run verification:role:provision -w @helyx/database -- --confirm-environment=staging

The role is restricted to data operations inside the Verification schema. It has no DDL, role-management, ownership, public-schema, dashboard-session, OAuth, billing, or unrelated module privileges.