fix(database): initialize fresh PostgreSQL safely #183
No reviewers
Labels
No labels
auth
autorelease: pending
autorelease: tagged
bug
dependencies
devops
docker
documentation
duplicate
enhancement
github_actions
good first issue
help wanted
invalid
javascript
maintenance
question
refactor
ui
wontfix
No milestone
No assignees
3 participants
Notifications
Due date
No due date set.
Dependencies
No dependencies set
Reference
gitchegumi/GitchAlerts!183
Loading…
Reference in a new issue
No description provided.
Delete branch "kiyi/fresh-postgres-initialization"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
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 migrateand 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.
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.
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([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.
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.
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:
e65d307adds 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.@ -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);[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.
Addressed in
e65d307using 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.
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:
e65d307rejects 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.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.
WIP: fix(database): initialize fresh PostgreSQL safelyto fix(database): initialize fresh PostgreSQL safely