/** * V1-1.1 workspaces (M1) on the real stack of `pnpm db:sandbox`: the sign-up * chain, reads and refusals through PostgREST per role, the residual GoTrue * admin path, the audit correlation cap, and concurrent sessions. * Contract: docs/development/V1-1_OWNERS_ANIMALS.md §4, §8.1, §9. */ import { randomUUID } from 'node:crypto' import { adminDeleteUser, adminMagicLinkSession, confirmByMail, confirmedAccount, errorCode, rest, rpc, signIn, signUp, sql, sqlOutcome, stack, uniqueEmail, type Account, } from './stack' const workspaceOf = (userId: string) => sql(`select id from public.workspaces where personal_user_id = '${userId}'`) const holderCount = (userId: string) => sql(`select count(*) from public.workspaces w join public.workspace_members m on m.workspace_id = w.id and m.user_id = w.personal_user_id and m.member_role = 'holder' where w.personal_user_id = '${userId}'`) describe('sign-up and confirmation', () => { it('creates the workspace in the sign-up transaction, readable only after confirmation', async () => { const email = uniqueEmail('ws-signup') const signup = await signUp(email) expect(signup.status).toBe(200) expect(signup.body.access_token).toBeUndefined() const id = signup.body.id as string expect(await holderCount(id)).toBe('1') const early = await signIn(email) expect(early.body.error_code).toBe('email_not_confirmed') expect(await confirmByMail(email)).toBe(303) const session = await signIn(email) const read = await rest( '/workspaces?select=id,kind,personal_user_id', session.body.access_token as string, ) expect(read.body).toEqual([ { id: await workspaceOf(id), kind: 'personal', personal_user_id: id }, ]) }) }) describe('reads and refusals per role', () => { let a: Account let b: Account let wA: string let wB: string beforeAll(async () => { a = await confirmedAccount('ws-a') b = await confirmedAccount('ws-b') wA = await workspaceOf(a.id) wB = await workspaceOf(b.id) }) it('each account reads exactly its workspace and membership', async () => { expect((await rest('/workspaces?select=id', a.token)).body).toEqual([ { id: wA }, ]) expect( (await rest('/workspace_members?select=user_id,member_role', a.token)) .body, ).toEqual([{ user_id: a.id, member_role: 'holder' }]) const head = await rest('/workspaces?select=id', b.token, { method: 'HEAD', headers: { Prefer: 'count=exact' }, }) expect(head.headers.get('content-range')).toBe('0-0/1') }) it('naming another account or workspace returns nothing', async () => { expect( (await rest(`/workspaces?select=id&id=eq.${wA}`, b.token)).body, ).toEqual([]) expect( (await rest(`/workspaces?select=id&personal_user_id=eq.${a.id}`, b.token)) .body, ).toEqual([]) expect( ( await rest( `/workspace_members?select=user_id&workspace_id=eq.${wA}`, b.token, ) ).body, ).toEqual([]) }) it('refuses every write, for a user, the anon key and the service key', async () => { const attempts: [string, string, RequestInit, number][] = [ [ '/workspaces', b.token, { method: 'POST', body: JSON.stringify({ kind: 'personal', personal_user_id: b.id }), }, 403, ], [ '/workspace_members', b.token, { method: 'POST', body: JSON.stringify({ workspace_id: wA, user_id: b.id, member_role: 'holder', }), }, 403, ], [ `/workspaces?id=eq.${wB}`, b.token, { method: 'PATCH', body: JSON.stringify({ personal_user_id: a.id }) }, 403, ], [ `/workspace_members?user_id=eq.${b.id}`, b.token, { method: 'DELETE' }, 403, ], ['/workspaces?select=id', stack.anonKey, {}, 401], ['/workspace_members?select=user_id', stack.anonKey, {}, 401], ['/workspaces?select=id', stack.serviceKey, {}, 403], [ '/workspace_members', stack.serviceKey, { method: 'POST', body: JSON.stringify({ workspace_id: wA, user_id: b.id, member_role: 'holder', }), }, 403, ], ] for (const [path, token, init, status] of attempts) { const result = await rest(path, token, init) expect([path, result.status, errorCode(result)]).toEqual([ path, status, '42501', ]) expect(result.text).not.toContain(wA) expect(result.text).not.toContain(a.id) } }) it('keeps the private helpers out of the API', async () => { expect((await rpc('caller_workspace_ids', {}, b.token)).status).toBe(404) expect( (await rpc('ensure_personal_workspace', { p_user_id: b.id }, b.token)) .status, ).toBe(404) const profile = await rest('/rpc/backfill_personal_workspaces', b.token, { method: 'POST', body: '{}', headers: { 'Content-Profile': 'private' }, }) expect([profile.status, errorCode(profile)]).toEqual([406, 'PGRST106']) }) it('cuts a deactivated account at its next request, same token', async () => { await sql(`update public.profiles set active = false where id = '${b.id}'`) expect((await rest('/workspaces?select=id', b.token)).body).toEqual([]) await sql(`update public.profiles set active = true where id = '${b.id}'`) expect((await rest('/workspaces?select=id', b.token)).body).toEqual([ { id: wB }, ]) }) it('RESIDUAL (contract §6.3): the service key mints a session for any account through GoTrue admin', async () => { const token = await adminMagicLinkSession(a.email) expect(token).toBeTruthy() expect((await rest('/workspaces?select=id', token ?? '')).body).toEqual([ { id: wA }, ]) }) it('stores an oversized correlation header as null and keeps a short token', async () => { const owner = await signIn( 'owner@faunosteo.local', 'LocalDev-only-password-1', ) const token = owner.body.access_token as string const grant = await rpc( 'grant_platform_role', { target_user_id: b.id, role: 'support' }, token, { 'x-correlation-id': 'x'.repeat(6000), }, ) const revoke = await rpc( 'revoke_platform_role', { target_user_id: b.id, role: 'support', reason: 'sandbox' }, token, { 'x-correlation-id': 'req-sandbox-01', }, ) expect([grant.status, revoke.status]).toEqual([200, 200]) expect( await sql(`select string_agg(coalesce(correlation_id, 'NULL'), '|' order by occurred_at, action) from audit.audit_logs where target_user_id = '${b.id}'`), ).toBe('NULL|req-sandbox-01') }) it('deletes an empty account through GoTrue admin with its workspace', async () => { const e = await confirmedAccount('ws-e') expect(await adminDeleteUser(e.id)).toBe(200) expect( await sql( `select count(*) from public.workspace_members where user_id = '${e.id}'`, ), ).toBe('0') expect(await workspaceOf(e.id)).toBe('') }) }) describe('concurrent sessions', () => { const ids = Array.from({ length: 20 }, () => randomUUID()) beforeAll(async () => { const values = ids .map( (id, i) => `('${id}', 'race-${i}-${id.slice(0, 8)}@sandbox.local', now())`, ) .join(',') await sql(`begin; alter table public.profiles disable trigger profiles_create_personal_workspace; insert into auth.users (id, email, email_confirmed_at) values ${values}; alter table public.profiles enable trigger profiles_create_personal_workspace; commit;`) }) it('two sessions creating one workspace: the second waits for the commit, then writes nothing', async () => { const first = sql( `begin; select private.ensure_personal_workspace('${ids[0]}'); select pg_sleep(1.5); commit;`, ) await new Promise((resolve) => setTimeout(resolve, 400)) const started = Date.now() const second = await sql( `select private.ensure_personal_workspace('${ids[0]}')`, ) expect(Date.now() - started).toBeGreaterThan(700) expect(await first).toContain('t') expect(second).toBe('f') expect(await holderCount(ids[0])).toBe('1') }) it('refuses at COMMIT a workspace without its holder or with a foreign member', async () => { const alone = await sqlOutcome( `begin; insert into public.workspaces (kind, personal_user_id) values ('personal', '${ids[1]}'); commit;`, ) expect(alone.ok).toBe(false) expect(alone.out).toContain( 'personal workspace must have exactly its account as holder', ) expect(await workspaceOf(ids[1])).toBe('') const foreign = await sqlOutcome(`begin; insert into public.workspace_members (workspace_id, user_id, member_role) select id, '${ids[1]}', 'holder' from public.workspaces where personal_user_id = '${ids[0]}'; commit;`) expect(foreign.ok).toBe(false) }) it('a rolled-back creation leaves nothing; the retry creates once', async () => { await sql( `begin; select private.ensure_personal_workspace('${ids[1]}'); rollback;`, ) expect(await workspaceOf(ids[1])).toBe('') expect( await sql(`select private.ensure_personal_workspace('${ids[1]}')`), ).toBe('t') expect( await sql(`select private.ensure_personal_workspace('${ids[1]}')`), ).toBe('f') }) it('two racing backfills create each missing workspace once', async () => { const [x, y] = await Promise.all([ sql('select private.backfill_personal_workspaces()'), sql('select private.backfill_personal_workspaces()'), ]) expect(Number(x) + Number(y)).toBe(18) const counts = await Promise.all(ids.map(holderCount)) expect(counts.every((c) => c === '1')).toBe(true) }) it('parallel GoTrue sign-ups each get one workspace', async () => { const results = await Promise.all( Array.from({ length: 8 }, () => signUp(uniqueEmail('ws-par'))), ) expect(results.map((r) => r.status)).toEqual(Array(8).fill(200)) const counts = await Promise.all( results.map((r) => holderCount(r.body.id as string)), ) expect(counts).toEqual(Array(8).fill('1')) }) })