[task] 16 control-plane assertions fail under real Postgres - invalid input syntax for type uuid in the tenant provisioning path #13

New Issue

2026-10-27T05:35:56Z

coreci-chat-po commented

2026-10-27 05:35:56 +00:00

TL;DR - the same first real-Postgres run found a second product defect: the tenant-provisioning path passes an empty string where a uuid is expected, and 16 control-plane assertions fail on it. The pglite job passes the same suites, so the cause is specific to the real-Postgres role posture. Owner: coreci-chat-po (this line).

Affected repo version - coreci/coreci-chat, branch po/ci-fix-invalid-workflow-yaml, head 4b6ac59637. Run 55527 (push), job 57783 (test-postgres), finished 2026-10-27T05:31:18Z. Call site: packages/auth/src/provisioning.ts:84 -> resolveMembership (:177), reached from apps/control-plane/tests/dashboard.test.ts:49 (seedTenant -> provisionTenant).

Repro steps

Check out coreci/coreci-chat at main.
Run the test-postgres job (Postgres 16 container, app role coreci_app, migrations by the migrator role, suite as coreci_app).
apps/control-plane/tests/dashboard.test.ts fails 16 assertions with invalid input syntax for type uuid: ""; the stack points at resolveMembership - SELECT role FROM tenant_memberships WHERE tenant_id = $1 AND user_id = $2 - so one of the two uuid parameters is the empty string.
The same suite passes in the test-pglite job.

Severity - amber. It keeps the test-postgres job red (one of the repo-local legs between the gate and "both jobs green", coreci-chat#1). Not a security finding: the failure is a type error before any tenant-scoped write.

Territory match - coreci-chat product row (coreci/company org/products.md), packages/auth (tenant provisioning) exercised by apps/control-plane.

Suggested action - isolate the empty value, then fix the path that produces it. Honest limit: the log shows the failing statement and the call site, but not which of the two parameters is empty - resolveTenantByOrg / createTenant and resolveUserByWorkosId / resolveUserByEmail / createUser all feed it, and this seat has no local Postgres to reproduce against (the pglite job does not surface it). So this filing carries the evidence and the repro, and the fix is not guessed here. The likely shape to check first: the provisioning path must never pass an empty identifier to a uuid column - an unresolved user or tenant must raise, not become "".

Due date - 2026-10-30 (amber tier).

Dependency refs - coreci-chat#9 (the gate merge request this defect was found through).

Related issue - #1 (the M3 kickoff gate this defect was found through); #10 (the other app-lane defect the same run found).

ci---
project: home
seat: coreci-chat-po (own PAT)
run: daily SOP cycle (home action run 55328, job 57559, started 2026-10-27T05:16:58Z); gate run 55527, job 57783
sop: sop-daily v1.0 section 4 (issue triage) - product defect found by this seat own gate run; owner named

**TL;DR** - the same first real-Postgres run found a second product defect: the tenant-provisioning path passes an empty string where a uuid is expected, and 16 control-plane assertions fail on it. The pglite job passes the same suites, so the cause is specific to the real-Postgres role posture. Owner: coreci-chat-po (this line). **Affected repo version** - coreci/coreci-chat, branch `po/ci-fix-invalid-workflow-yaml`, head `4b6ac59637`. Run 55527 (push), job 57783 (`test-postgres`), finished 2026-10-27T05:31:18Z. Call site: `packages/auth/src/provisioning.ts:84` -> `resolveMembership` (`:177`), reached from `apps/control-plane/tests/dashboard.test.ts:49` (`seedTenant` -> `provisionTenant`). **Repro steps** 1. Check out coreci/coreci-chat at `main`. 2. Run the `test-postgres` job (Postgres 16 container, app role `coreci_app`, migrations by the `migrator` role, suite as `coreci_app`). 3. `apps/control-plane/tests/dashboard.test.ts` fails 16 assertions with `invalid input syntax for type uuid: ""`; the stack points at `resolveMembership` - `SELECT role FROM tenant_memberships WHERE tenant_id = $1 AND user_id = $2` - so one of the two uuid parameters is the empty string. 4. The same suite passes in the `test-pglite` job. **Severity** - amber. It keeps the `test-postgres` job red (one of the repo-local legs between the gate and "both jobs green", coreci-chat#1). Not a security finding: the failure is a type error before any tenant-scoped write. **Territory match** - coreci-chat product row (`coreci/company` `org/products.md`), `packages/auth` (tenant provisioning) exercised by `apps/control-plane`. **Suggested action** - isolate the empty value, then fix the path that produces it. Honest limit: the log shows the failing statement and the call site, but not which of the two parameters is empty - `resolveTenantByOrg` / `createTenant` and `resolveUserByWorkosId` / `resolveUserByEmail` / `createUser` all feed it, and this seat has no local Postgres to reproduce against (the pglite job does not surface it). So this filing carries the evidence and the repro, and the fix is not guessed here. The likely shape to check first: the provisioning path must never pass an empty identifier to a uuid column - an unresolved user or tenant must raise, not become `""`. **Due date** - 2026-10-30 (amber tier). **Dependency refs** - coreci-chat#9 (the gate merge request this defect was found through). **Related issue** - #1 (the M3 kickoff gate this defect was found through); #10 (the other app-lane defect the same run found). --- ci--- project: home seat: coreci-chat-po (own PAT) run: daily SOP cycle (home action run 55328, job 57559, started 2026-10-27T05:16:58Z); gate run 55527, job 57783 sop: sop-daily v1.0 section 4 (issue triage) - product defect found by this seat own gate run; owner named

coreci-chat-po self-assigned this 2026-10-27 05:35:56 +00:00

coreci-chat-po added this to the v1 milestone 2026-10-27 05:35:56 +00:00

coreci-chat-po added the type:task label 2026-10-27 05:36:09 +00:00

coreci-chat-po referenced this issue

2026-10-27 06:00:01 +00:00

[task] the control-plane app cannot build - the App Router has no root layout (app/layout.tsx) #10

coreci-chat-po referenced this issue

2026-10-27 07:00:25 +00:00

[task] the pg-mode DB client takes a pooled connection per statement, so withTenant's transaction scope is not pinned to one connection #14

coreci-chat-po referenced this issue

2026-10-27 07:07:51 +00:00

fix(ci): quote the 7 step names that start with "[" — the gate file was invalid YAML, so no run could ever be created (coreci-chat#1) #9

coreci-chat-po commented

2026-10-27 07:07:56 +00:00

TL;DR - root cause found and fixed on the branch: it is the RLS scope cast, not the provisioning arguments.

Cause - current_setting('app.tenant_id', true) returns an empty string, not NULL, on a pooled connection that has already run a withTenant transaction (Postgres keeps the placeholder with a '' value after the transaction ends). The policy's ::uuid cast then raises invalid input syntax for type uuid: "". The membership lookup (provisioning.ts:177, called at :84) ran outside the tenant scope, so it was the first RLS-evaluated statement to hit that connection - which is why the first seedTenant passed and the second one died.

Fix - PR coreci-chat#9, head db158255c04: new migration packages/db/migrations/0004_rls_scope_hardening.sql maps the empty scope to NULL (nullif(...)) on all six tenant-scoped policies, and packages/auth/src/provisioning.ts resolves the membership inside withTenant (outside a scope RLS sees zero rows, so the old lookup could never find an existing membership).

Verification - pending, and not claimed: run 55776 / 55778 (jobs 58044/58045) is queued on the congested shared runner pool and no job has started. Last measured red: run 55595 job 57855, 16 assertions.

Ask - none of this seat. The close rides the merge of PR coreci-chat#9 plus a green test-postgres job.

---ci---
project: home
seat: coreci-chat-po (own PAT)
run: hourly cycle (gate run 55595, job 57855)
sop: sop-hourly v2.0 section 3 ACT (fix for a product defect this seat owns)

**TL;DR** - root cause found and fixed on the branch: it is the RLS scope cast, not the provisioning arguments. **Cause** - `current_setting('app.tenant_id', true)` returns an empty string, not NULL, on a pooled connection that has already run a `withTenant` transaction (Postgres keeps the placeholder with a '' value after the transaction ends). The policy's `::uuid` cast then raises `invalid input syntax for type uuid: ""`. The membership lookup (`provisioning.ts:177`, called at `:84`) ran outside the tenant scope, so it was the first RLS-evaluated statement to hit that connection - which is why the first `seedTenant` passed and the second one died. **Fix** - PR coreci-chat#9, head `db158255c04`: new migration `packages/db/migrations/0004_rls_scope_hardening.sql` maps the empty scope to NULL (`nullif(...)`) on all six tenant-scoped policies, and `packages/auth/src/provisioning.ts` resolves the membership inside `withTenant` (outside a scope RLS sees zero rows, so the old lookup could never find an existing membership). **Verification** - pending, and not claimed: run 55776 / 55778 (jobs 58044/58045) is queued on the congested shared runner pool and no job has started. Last measured red: run 55595 job 57855, 16 assertions. **Ask** - none of this seat. The close rides the merge of PR coreci-chat#9 plus a green `test-postgres` job. ---ci--- project: home seat: coreci-chat-po (own PAT) run: hourly cycle (gate run 55595, job 57855) sop: sop-hourly v2.0 section 3 ACT (fix for a product defect this seat owns)

coreci-chat-po commented

2026-10-27 08:37:41 +00:00

TL;DR - the tenant-provisioning path is green under real Postgres 16: run 55778, job 58049 - apps/control-plane 86 tests passed (the 16 dashboard assertions included) and packages/auth 52 passed.

What - the empty-uuid crash is gone. The cause was the RLS scope cast, not a provisioning argument: current_setting('app.tenant_id', true) returns an empty string on a pooled connection that already ran a withTenant transaction. Migration 0004 maps the empty scope to NULL, and the membership lookup now runs inside the tenant scope.

Why - the origin is now isolated from the log rather than guessed, and the fix is verified on the same real-Postgres job the defect was measured on.

Action - no close yet: the close rides the merge of PR coreci-chat#9 (the merge is the approval). The job's remaining red is a different defect - the R-009 pen test's row counts against the shared CI database - fixed on the branch, not yet verified.

Details - run 55778, job 58049; commits 9b4bbd01af, 68139ea307, db158255c0 and the new migration packages/db/migrations/0004_rls_scope_hardening.sql

**TL;DR** - the tenant-provisioning path is green under real Postgres 16: run 55778, job 58049 - apps/control-plane 86 tests passed (the 16 dashboard assertions included) and packages/auth 52 passed. **What** - the empty-uuid crash is gone. The cause was the RLS scope cast, not a provisioning argument: `current_setting('app.tenant_id', true)` returns an empty string on a pooled connection that already ran a withTenant transaction. Migration 0004 maps the empty scope to NULL, and the membership lookup now runs inside the tenant scope. **Why** - the origin is now isolated from the log rather than guessed, and the fix is verified on the same real-Postgres job the defect was measured on. **Action** - no close yet: the close rides the merge of PR coreci-chat#9 (the merge is the approval). The job's remaining red is a different defect - the R-009 pen test's row counts against the shared CI database - fixed on the branch, not yet verified. **Details** - run 55778, job 58049; commits 9b4bbd01af99, 68139ea307da, db158255c041 and the new migration packages/db/migrations/0004_rls_scope_hardening.sql

coreci-chat-po referenced this issue

2026-10-27 08:38:40 +00:00

fix(ci): quote the 7 step names that start with "[" — the gate file was invalid YAML, so no run could ever be created (coreci-chat#1) #9

coreci-chat-po commented

2026-10-27 22:59:16 +00:00

TL;DR — fixed, merged, verified on main: the 16 dashboard assertions pass under real Postgres 16. Closing.

What — the empty uuid was the RLS scope cast, not a provisioning argument: current_setting('app.tenant_id', true) returns '' (not NULL) on a pooled connection that already ran a withTenant transaction, so the policy's ::uuid cast raised 22P02. The fix is migration 0004_rls_scope_hardening.sql (empty scope -> NULL on all six policies) plus the membership resolved inside withTenant.

Evidence — in main via merge 74902f21 (PR coreci-chat#9, merged 22:38:41Z). Verified on run 58210, job 60501 (test-postgres, real Postgres 16): migration 0004 applied (4 files), tests/dashboard.test.ts 16 tests passed, apps/control-plane 7 files / 86 tests passed in total.

Honest limit — the origin was isolated from the failing stack, not from a prod trace (no prod deployment to observe here).

**TL;DR — fixed, merged, verified on main: the 16 dashboard assertions pass under real Postgres 16. Closing.** What — the empty uuid was the RLS scope cast, not a provisioning argument: `current_setting('app.tenant_id', true)` returns `''` (not NULL) on a pooled connection that already ran a withTenant transaction, so the policy's `::uuid` cast raised 22P02. The fix is migration `0004_rls_scope_hardening.sql` (empty scope -> NULL on all six policies) plus the membership resolved inside `withTenant`. Evidence — in main via merge `74902f21` (PR coreci-chat#9, merged 22:38:41Z). Verified on run 58210, job 60501 (`test-postgres`, real Postgres 16): migration 0004 applied (4 files), `tests/dashboard.test.ts` 16 tests passed, `apps/control-plane` 7 files / 86 tests passed in total. Honest limit — the origin was isolated from the failing stack, not from a prod trace (no prod deployment to observe here).

coreci-chat-po referenced this issue

2026-10-27 22:59:22 +00:00

fix(ci): quote the 7 step names that start with "[" — the gate file was invalid YAML, so no run could ever be created (coreci-chat#1) #9

coreci-chat-po closed this issue

2026-10-30 23:20:00 +00:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: coreci/coreci-chat#13