fix(database): initialize fresh PostgreSQL safely #183

Merged
gitchegumi merged 2 commits from kiyi/fresh-postgres-initialization into main 2026-09-14 10:41:25 -05:00 AGit
Collaborator

Fresh PostgreSQL deployments fail because a backdated linked-account migration runs before the users table exists. Initialize a verified-empty public schema with the frozen v0.12.2 schema, catalog seed and historical migration checksums in one transaction. Preserve complete existing histories and data, and refuse incomplete, failed or unknown states without automatic adoption or reset.

Run one Compose migration job before web, ingress and worker. Readiness uses shared locks, excludes the migrator, and verifies the packaged client's tables, columns, primary keys and unique keys. Generate key metadata alongside Prisma Client and reject missing or stale metadata. Accept equivalent index names and compound-column ordering while refusing indexes that cannot enforce the required uniqueness. Reject unsupported non-public DATABASE_URL schemas before connecting; the runtime adapter queries public.

Validation: 10 migration/generator unit tests, 71 database tests, workspace typecheck and lint, touched-file formatting, staged secret scanning, production web image build, and complete packaged acceptance suites on PostgreSQL 17 and 18. Coverage includes fresh OIDC bootstrap and alert SSE, both legacy-history fixtures with decryption preservation, actual Prisma upserts, missing/unusable key rejection without changing data/history, equivalent key definitions, unsupported schemas, repeat runs, concurrent checks and failed future migrations.

Custom deployments must replace raw Prisma startup overrides with sh /usr/local/bin/gitchalerts-start migrate and retain the success gate. Omit DATABASE_URL's schema parameter or use schema=public; existing non-public data requires operator migration. Standard image builds generate the readiness metadata. Historical SQL and the frozen snapshot remain unchanged; no new environment setting, encryption-key rotation or provider reconnect is required.

Refs #182. Release publication, signed Homelab bundle adoption and dev deployment acceptance remain follow-up work under the deployment hold. Production cutover remains a separate decision.

Fresh PostgreSQL deployments fail because a backdated linked-account migration runs before the users table exists. Initialize a verified-empty public schema with the frozen v0.12.2 schema, catalog seed and historical migration checksums in one transaction. Preserve complete existing histories and data, and refuse incomplete, failed or unknown states without automatic adoption or reset. Run one Compose migration job before web, ingress and worker. Readiness uses shared locks, excludes the migrator, and verifies the packaged client's tables, columns, primary keys and unique keys. Generate key metadata alongside Prisma Client and reject missing or stale metadata. Accept equivalent index names and compound-column ordering while refusing indexes that cannot enforce the required uniqueness. Reject unsupported non-public DATABASE_URL schemas before connecting; the runtime adapter queries public. Validation: 10 migration/generator unit tests, 71 database tests, workspace typecheck and lint, touched-file formatting, staged secret scanning, production web image build, and complete packaged acceptance suites on PostgreSQL 17 and 18. Coverage includes fresh OIDC bootstrap and alert SSE, both legacy-history fixtures with decryption preservation, actual Prisma upserts, missing/unusable key rejection without changing data/history, equivalent key definitions, unsupported schemas, repeat runs, concurrent checks and failed future migrations. Custom deployments must replace raw Prisma startup overrides with `sh /usr/local/bin/gitchalerts-start migrate` and retain the success gate. Omit DATABASE_URL's schema parameter or use schema=public; existing non-public data requires operator migration. Standard image builds generate the readiness metadata. Historical SQL and the frozen snapshot remain unchanged; no new environment setting, encryption-key rotation or provider reconnect is required. Refs #182. Release publication, signed Homelab bundle adoption and dev deployment acceptance remain follow-up work under the deployment hold. Production cutover remains a separate decision.
fix(database): initialize fresh PostgreSQL with a migration gate
All checks were successful
CI / Lint, typecheck, tests and build (pull_request) Successful in 3m0s
CI / security-1 (pull_request) Successful in 7m43s
CI / security (pull_request) Successful in 0s
Container checks / Build and smoke-test service images (pull_request) Successful in 7m49s
c76bd46478
Initialize verified-empty application schemas with a frozen v0.12.2 schema
and catalog snapshot, recording historical migration checksums in the same
transaction. Preserve existing histories and reject incomplete, failed or
unknown states without automatic adoption or reset.

Run one Compose migration job before web, ingress and worker. Use shared
readiness locks and an exclusive migrator lock, validate the generated
client's tables and columns, and keep migration output free of secrets.
Custom deployments must replace raw Prisma startup overrides with
sh /usr/local/bin/gitchalerts-start migrate and retain the success gate.
No encryption-key rotation, provider reconnect or new environment setting
is required. Existing historical SQL files remain unchanged.

Add packaged PostgreSQL 17/18 acceptance coverage for the original failure,
the complete historical sequence, fresh OIDC bootstrap and alert SSE,
both existing-history fixtures, data and decryption preservation, repeat
runs, concurrent checks, schema isolation and failed migration gating.

Refs #182. Release publication, the signed Homelab bundle update and dev
deployment acceptance remain follow-up work; preserve the deployment hold.
portegumi requested changes 2026-09-14 09:18:37 -05:00
Dismissed
portegumi left a comment

Request changes: two independently reproduced false-positive readiness checks. Both occur in the production web image built from this exact head against disposable PostgreSQL 17.

The main approach is sound: unchanged historical SQL, transactional fresh initialization, one migration owner, secret-safe output, and separate real-history/manual-baseline upgrade fixtures. Local validation passed: frozen install; workspace typecheck; 7 migration unit tests; 71 database tests; 31 script tests; production web image build; complete packaged migration acceptance suites on PostgreSQL 17 and 18. Exact-head hosted CI and container checks are also green. Those suites do not cover the runtime-schema mismatch or missing unique index below.

  1. A custom-schema URL passes initialization/readiness, but the application's actual Prisma adapter still queries public. Either share schema selection with the application client and test a real application query, or explicitly reject unsupported non-public schemas before initialization.
  2. A recorded but incomplete schema can pass readiness when a required unique index is missing, then fail ordinary workspace writes. Validate the required runtime constraints as well as table/column existence, preserving the documented differences between valid legacy and fresh schemas.

No branch changes or deployment performed. Upstream release, signed downstream bundle adoption and dev acceptance remain follow-up work under #182's deployment hold.

Request changes: two independently reproduced false-positive readiness checks. Both occur in the production web image built from this exact head against disposable PostgreSQL 17. The main approach is sound: unchanged historical SQL, transactional fresh initialization, one migration owner, secret-safe output, and separate real-history/manual-baseline upgrade fixtures. Local validation passed: frozen install; workspace typecheck; 7 migration unit tests; 71 database tests; 31 script tests; production web image build; complete packaged migration acceptance suites on PostgreSQL 17 and 18. Exact-head hosted CI and container checks are also green. Those suites do not cover the runtime-schema mismatch or missing unique index below. 1. A custom-schema URL passes initialization/readiness, but the application's actual Prisma adapter still queries public. Either share schema selection with the application client and test a real application query, or explicitly reject unsupported non-public schemas before initialization. 2. A recorded but incomplete schema can pass readiness when a required unique index is missing, then fail ordinary workspace writes. Validate the required runtime constraints as well as table/column existence, preserving the documented differences between valid legacy and fresh schemas. No branch changes or deployment performed. Upstream release, signed downstream bundle adoption and dev acceptance remain follow-up work under #182's deployment hold.
@ -0,0 +128,4 @@
.filter((field) => field.kind !== 'object')
.map((field) => identifier(field.dbName ?? field.name));
try {
await client.query(
Collaborator

[P2] Column-only SELECT probes accept an incomplete runtime schema. Reproduction in the exact production image: initialize a fresh public schema; DROP INDEX channels_slug_key in the disposable fixture, leaving all historical records intact; run --check. It exits 0 and prints 'Database is ready'. A normal Prisma channel.upsert({where:{slug:'probe'},create:{slug:'probe',name:'Probe'},update:{name:'Probe'}} then fails P2039 / PostgreSQL 42P10 ('there is no unique or exclusion constraint matching the ON CONFLICT specification'). Both baseline and current verification only SELECT columns LIMIT 0, so neither catches the missing uniqueness contract. This contradicts #182's fail-closed requirement for partial/unknown schema states. Check required runtime indexes/constraints (without demanding byte-identical legacy/fresh catalogs), and add a packaged negative fixture for a missing required unique index that asserts the migrator/readiness gate reject it without altering data.

[P2] Column-only SELECT probes accept an incomplete runtime schema. Reproduction in the exact production image: initialize a fresh public schema; DROP INDEX channels_slug_key in the disposable fixture, leaving all historical records intact; run --check. It exits 0 and prints 'Database is ready'. A normal Prisma channel.upsert({where:{slug:'probe'},create:{slug:'probe',name:'Probe'},update:{name:'Probe'}} then fails P2039 / PostgreSQL 42P10 ('there is no unique or exclusion constraint matching the ON CONFLICT specification'). Both baseline and current verification only SELECT columns LIMIT 0, so neither catches the missing uniqueness contract. This contradicts #182's fail-closed requirement for partial/unknown schema states. Check required runtime indexes/constraints (without demanding byte-identical legacy/fresh catalogs), and add a packaged negative fixture for a missing required unique index that asserts the migrator/readiness gate reject it without altering data.
Owner

Addressed in e65d307. Readiness now verifies primary and unique keys generated from the full Prisma schema against usable PostgreSQL indexes. It rejects missing, nonunique, partial, expression and deferrable replacements, while accepting equivalent names, compound-column order and INCLUDE columns.

The packaged missing_unique fixture reproduces the Prisma upsert failure after dropping channels_slug_key, then verifies that migration, --check and service startup all fail without changing application data or migration history. PostgreSQL 17 and 18 acceptance suites, all 81 focused tests, and hosted CI/security/container checks passed. The legacy and manual-baseline upgrade fixtures remain green. Resolving this addressed finding.

Addressed in e65d307. Readiness now verifies primary and unique keys generated from the full Prisma schema against usable PostgreSQL indexes. It rejects missing, nonunique, partial, expression and deferrable replacements, while accepting equivalent names, compound-column order and INCLUDE columns. The packaged missing_unique fixture reproduces the Prisma upsert failure after dropping channels_slug_key, then verifies that migration, --check and service startup all fail without changing application data or migration history. PostgreSQL 17 and 18 acceptance suites, all 81 focused tests, and hosted CI/security/container checks passed. The legacy and manual-baseline upgrade fixtures remain green. Resolving this addressed finding.
Author
Collaborator

Attribution correction: the previous reply was written by the coding agent through the wrong browser session and was incorrectly posted under gitchegumi. Confirming as kiyi: e65d307 adds generated primary/unique-key validation and the missing_unique regression, which verifies rejection without data/history changes. The PostgreSQL 17/18 acceptance suites and hosted CI/security/container checks passed. This finding remains addressed and resolved.

Attribution correction: the previous reply was written by the coding agent through the wrong browser session and was incorrectly posted under gitchegumi. Confirming as kiyi: e65d307 adds generated primary/unique-key validation and the missing_unique regression, which verifies rejection without data/history changes. The PostgreSQL 17/18 acceptance suites and hosted CI/security/container checks passed. This finding remains addressed and resolved.
gitchegumi marked this conversation as resolved
@ -0,0 +203,4 @@
log = (message) => console.info(`[migrations] ${message}`),
} = {}) {
log(checkOnly ? 'Checking database readiness.' : 'Starting database migration.');
const { schema, ...connection } = connectionSettings(process.env.DATABASE_URL);
Collaborator

[P2] Do not certify a schema different from the runtime client's schema. With DATABASE_URL ending in ?schema=alerts, this initializer creates alerts.users and --check returns 0 / 'Database is ready'. In the same packaged image, constructing PrismaClient exactly as packages/database/src/client.ts does (new PrismaPg({ connectionString })) and calling prisma.user.count() fails P2021 / 42P01: relation public.users does not exist. The adapter does not take its Prisma schema from this URL parameter. The adapter limitation predates this PR, but the newly introduced schema-aware initializer/readiness path and its custom_schema acceptance test now explicitly accept this unusable installation; the test only checks table placement. Thread one schema selection through migration and application paths and exercise a runtime query, or fail closed on unsupported non-public schemas instead of declaring them ready. The runbook's instruction to configure client schema consistently does not provide a working configuration mechanism.

[P2] Do not certify a schema different from the runtime client's schema. With DATABASE_URL ending in ?schema=alerts, this initializer creates alerts.users and --check returns 0 / 'Database is ready'. In the same packaged image, constructing PrismaClient exactly as packages/database/src/client.ts does (new PrismaPg({ connectionString })) and calling prisma.user.count() fails P2021 / 42P01: relation public.users does not exist. The adapter does not take its Prisma schema from this URL parameter. The adapter limitation predates this PR, but the newly introduced schema-aware initializer/readiness path and its custom_schema acceptance test now explicitly accept this unusable installation; the test only checks table placement. Thread one schema selection through migration and application paths and exercise a runtime query, or fail closed on unsupported non-public schemas instead of declaring them ready. The runbook's instruction to configure client schema consistently does not provide a working configuration mechanism.
Owner

Addressed in e65d307 using the fail-closed option. Migration and readiness reject non-public or conflicting repeated schema parameters before opening a connection, matching the application's public-schema adapter. The runbook now requires DATABASE_URL without a schema parameter or with schema=public, and explains that existing non-public data needs an operator-planned migration.

The packaged custom_schema_empty and custom_schema_populated fixtures verify rejection by migration, --check and service startup without creating or modifying schema/data/history. The explicit-public fresh installation passes real OIDC login, workspace bootstrap and alert SSE. PostgreSQL 17 and 18 suites, all 81 focused tests, and hosted CI/security/container checks passed. Resolving this addressed finding.

Addressed in e65d307 using the fail-closed option. Migration and readiness reject non-public or conflicting repeated schema parameters before opening a connection, matching the application's public-schema adapter. The runbook now requires DATABASE_URL without a schema parameter or with schema=public, and explains that existing non-public data needs an operator-planned migration. The packaged custom_schema_empty and custom_schema_populated fixtures verify rejection by migration, --check and service startup without creating or modifying schema/data/history. The explicit-public fresh installation passes real OIDC login, workspace bootstrap and alert SSE. PostgreSQL 17 and 18 suites, all 81 focused tests, and hosted CI/security/container checks passed. Resolving this addressed finding.
Author
Collaborator

Attribution correction: the previous reply was written by the coding agent through the wrong browser session and was incorrectly posted under gitchegumi. Confirming as kiyi: e65d307 rejects unsupported or conflicting schema parameters before connecting and documents the public-only requirement. Packaged empty/populated custom-schema regression cases, public-schema login and alert checks, PostgreSQL 17/18 suites, and hosted CI/security/container checks passed. This finding remains addressed and resolved.

Attribution correction: the previous reply was written by the coding agent through the wrong browser session and was incorrectly posted under gitchegumi. Confirming as kiyi: e65d307 rejects unsupported or conflicting schema parameters before connecting and documents the public-only requirement. Packaged empty/populated custom-schema regression cases, public-schema login and alert checks, PostgreSQL 17/18 suites, and hosted CI/security/container checks passed. This finding remains addressed and resolved.
gitchegumi marked this conversation as resolved
fix(database): reject false-positive readiness states
All checks were successful
CI / Lint, typecheck, tests and build (pull_request) Successful in 2m36s
CI / security-1 (pull_request) Successful in 6m8s
CI / security (pull_request) Successful in 0s
Container checks / Build and smoke-test service images (pull_request) Successful in 9m2s
e65d307df9
Reject non-public DATABASE_URL schemas before connecting because the
runtime adapter queries public. Preserve existing data and explain the
operator migration needed for unsupported schemas.

Generate primary and unique key metadata alongside Prisma Client, bind it
to the packaged schema, and verify usable catalog indexes before readiness.
Accept equivalent key names, compound order and INCLUDE columns while
rejecting missing, partial, expression, nonunique and deferrable keys.

Add packaged PostgreSQL regression fixtures and runtime Prisma upserts,
including data/history preservation and missing or stale metadata checks.
Keep the frozen snapshot and historical migration SQL unchanged. Standard
image builds generate the metadata; no key rotation or provider reconnect
is needed. Preserve the release and deployment hold under #182.

Refs #183
portegumi approved these changes 2026-09-14 10:03:12 -05:00
portegumi left a comment

Re-review approved at e65d307df9. Both findings from review 744 are resolved; this approval supersedes that change request.

Non-public and conflicting schema parameters are rejected before connecting, with explicit public-only documentation and packaged empty/populated-data preservation tests. Required primary/unique keys are generated from full Prisma metadata alongside the client and verified against usable PostgreSQL indexes; equivalent key names/order/INCLUDE definitions remain accepted. Missing/stale metadata fails closed. Historical SQL and the frozen snapshot remain unchanged.

Independent validation: regenerated Prisma Client/readiness metadata; all 10 migration/generator unit tests; all 71 database tests; workspace typecheck; production web image build; full packaged acceptance suites on PostgreSQL 17 and 18, including real upserts, missing/unusable keys, equivalent definitions, data/history preservation, unsupported schemas and metadata failures. Mutation checks are sensitive: disabling schema rejection fails its unit regression, and removing the production key-verification call fails the packaged suite at the missing_unique fixture. The clean image migration source was compared byte-for-byte with the reviewed checkout. Exact-head hosted CI/security/container checks are successful.

No remaining blocking findings in the reviewed changes. No merge or deployment performed. Release publication, signed Homelab bundle adoption and dev acceptance remain follow-up work; the #182 deployment hold is unchanged.

Re-review approved at e65d307df9cfac37beb040b913b8a4899dff7e98. Both findings from review 744 are resolved; this approval supersedes that change request. Non-public and conflicting schema parameters are rejected before connecting, with explicit public-only documentation and packaged empty/populated-data preservation tests. Required primary/unique keys are generated from full Prisma metadata alongside the client and verified against usable PostgreSQL indexes; equivalent key names/order/INCLUDE definitions remain accepted. Missing/stale metadata fails closed. Historical SQL and the frozen snapshot remain unchanged. Independent validation: regenerated Prisma Client/readiness metadata; all 10 migration/generator unit tests; all 71 database tests; workspace typecheck; production web image build; full packaged acceptance suites on PostgreSQL 17 and 18, including real upserts, missing/unusable keys, equivalent definitions, data/history preservation, unsupported schemas and metadata failures. Mutation checks are sensitive: disabling schema rejection fails its unit regression, and removing the production key-verification call fails the packaged suite at the missing_unique fixture. The clean image migration source was compared byte-for-byte with the reviewed checkout. Exact-head hosted CI/security/container checks are successful. No remaining blocking findings in the reviewed changes. No merge or deployment performed. Release publication, signed Homelab bundle adoption and dev acceptance remain follow-up work; the #182 deployment hold is unchanged.
gitchegumi changed title from WIP: fix(database): initialize fresh PostgreSQL safely to fix(database): initialize fresh PostgreSQL safely 2026-09-14 10:40:46 -05:00
Sign in to join this conversation.
No description provided.