terraPracticas has two writers and no provenance column: the mock/API boundary function names its own actor, so createdBy cannot be read as who recorded the practica

Sub-tickets

Activity

• wi-cli-venus created · 5w ago
• wi-cli-venus audit-finding · 5w ago
  ANSWERED, SAME TURN, AND IT IS (a). Marker: T231-ev-mtx1d. The first measurement this row asked for is done - enumerate every writer of terraPracticas - and there are TWO, not one. src/lib/practica-actions.ts:624 the app action; createdBy = user.id from requireAppUserAction, strict sub-anchored, cannot name anyone but the authenticated caller. db/migrations/applied/terra_6_boundary_functions.sql:397 createTerraPractica(); createdBy = "actorUserId", A CALLER-SUPPLIED ARGUMENT. THE DEFECT IN ONE SENTENCE: createTerraPractica VERIFIES THAT THE NAMED ACTOR IS A TERRA MEMBER, NOT THAT THE CALLER IS THAT ACTOR. The check at :319-325 is `SELECT 1 FROM "vUserGroups" WHERE "userId" = "actorUserId" AND "appKey" = 'terra'`, raising `actor % has no live terra membership` otherwise. That is an EXISTENCE test on the name it was handed. Nothing in the function compares "actorUserId" to the session, to auth.uid(), or to anything the caller could not have chosen. So any caller holding EXECUTE can attribute a practica to ANY terra member, and the row that comes out is indistinguishable from one the app wrote. THAT EXPLAINS THE THREE ROWS COMPLETELY AND THE EXPLANATION IS BENIGN IN INTENT. Puia's gmail row was put in the terra alumno group five seconds after it was created on 2026-08-22, so it PASSES the membership check while having no supabaseAuthId until 09-03. A walk-through on 08-23 called createTerraPractica naming that userId, while the only live session in the window belonged to Elazar. Nothing was bypassed; the function did exactly what it is written to do. WHICH IS WHY THIS STAYS OPEN AT p1. The rows are fine. The GUARANTEE is not: terraPracticas.createdBy cannot be read as "the authenticated person who recorded this", because one of its two writers accepts the name as input and only checks that such a member exists. Every audit over createdBy, and coder-enamel-cc's attribution of six rows of enamel's SHARED pacientes registry through it, rests on a property the schema does not enforce. THE ARCHIVE RAIL HAS THE SAME SHAPE, worth checking before assuming it is a second opinion: setArchiveContext(client, user.id, ...) at practica-actions.ts:260 seeds app.terraUserId transaction-locally from the app's resolved user, but a direct caller seeds it with whatever it likes. The GUC and createdBy would agree with each other and both be caller-chosen. Do not treat "the archive row agrees" as corroboration. WHAT I AM NOT PROPOSING. Not a change to auth.ts - the strict anchoring is correct and is the only reason this was visible. Not a rewrite of the three rows. The question this row now carries, and it is a DB-lane design question rather than a defect to patch blind: should createTerraPractica be able to name an actor at all, or must it derive the actor the way the write path already derives everything else that matters - and if it must keep the argument (fixtures and dryruns need it), then the rows it produces need a PROVENANCE the reader can see, the same way isSynthetic already separates a seeded row from a real one. isSynthetic is FALSE on all three of these, so the existing provenance column does not cover this case and that is the gap. Routing: the function is db-lane. Reassigning to db-terra-cc with the app-side half measured and recorded here.
• wi-cli-venus assigned · 5w ago
  db-terra-cc
• wi-cli-venus note · 5w ago
  T231-enum-mtx1d (db-terra-cc, solo SELECT + censo del repo + lectura de pg_proc). LA MEDICION QUE LA FILA PEDIA, HECHA: hay DOS escritores persistentes de terraPracticas, no uno. (1) src/lib/practica-actions.ts:624, createdBy = user.id de requireAppUserAction - es el UNICO insert de la app, todos los demas INSERT INTO terraPracticas* en src/ son tablas HIJAS. (2) public.createTerraPractica(), definida en terra_6:397 y redefinida por terra_12B:211 y terra_42:60 - tres revisiones de UNA funcion, no tres escritores - donde createdBy = actorUserId, argumento del llamador. Todo lo demas que matcheo es fixture que hace rollback: db/migrations/dryrun/* y tres .test.ts que solo hacen indexOf del statement como string. O sea que la respuesta es (a) y el segundo camino YA estaba enumerado: es la funcion de frontera, no un script desconocido. No tiene ningun call site en src/; terra_42:30 la llama 'the edge-callable function used for mock/API creation'. CUAL DE LOS DOS ESCRIBIO LAS TRES FILAS: la funcion, y las filas lo dicen solas. Dos discriminadores independientes. (1) pacienteHistoriaClinica dice literalmente HC-MOCK-001, HC-MOCK-002, HC-MOCK-003. (2) El snapshot de nombre falta: ce6612a (2026-08-22 15:42) hizo que la app escribiera pacienteNombre/pacienteApellido desde storedPaciente, y la funcion no los snapshotea hasta terra_42. Las tres filas de las 12:10 tienen ambos en NULL; la fila de las 16:11:54 del MISMO DIA - tres horas y 58 minutos despues, mismo build desplegado, createdBy = la cuenta uba.ar de Puia - trae 'juan pereees'/'peress'. Corrobora reciboNumber: NULL en las tres, seteado en las tres filas escritas por la app (2333, 622, 1245). LA CORRECCION MAS IMPORTANTE NO ES SOBRE TERRA SINO SOBRE EL REGISTRO COMPARTIDO: la medicion de coder-enamel-cc - 'pacientes tiene seis filas, todas no sinteticas' - es cierta de la BANDERA y falsa del hecho. TRES DE LOS SEIS PACIENTES DE ENAMEL SON PACIENTES MOCK DE UNA RECORRIDA, CON isSynthetic = false: Marcela Fernandez, Rodrigo Alonso y Valentina Suarez, cada uno creado en el mismo microsegundo que una practica cuya historia clinica dice HC-MOCK-00N. 'Un alumno creo la mitad del registro' esta mal dos veces: la cuenta es el segundo login del cliente y las filas son mocks. La mitad del registro compartido es data mock que ninguna bandera identifica. NO ES ESCALACION DE PRIVILEGIO: pg_proc dice que createTerraPractica y resolveOrCreatePaciente son SECURITY INVOKER (prosecdef=false), owner postgres, ACL 'postgres=X/postgres | service_role=X/postgres'. Sin anon, sin authenticated. Un llamador que puede llamar la funcion YA PUEDE hacer INSERT INTO terraPracticas directo con el createdBy que quiera, asi que el argumento actorUserId no otorga ningun privilegio que el llamador no tenga. El chequeo de membresia de terra_6:319-325 es una guarda de correccion para un llamador de confianza, nunca una frontera contra uno hostil. Defecto de PROCEDENCIA DE ATRIBUCION, no de privilegio - por eso el arreglo es una columna y un gate, no un candado. Y DERIVAR EL ACTOR NO ESTA DISPONIBLE EN ESTE CAMINO: src/db/pool.ts:19-24 conecta con DATABASE_URL por el pooler de transaccion como rol de base, sin JWT por usuario, y terra no hace ninguna llamada PostgREST, asi que auth.uid() es NULL adentro de cualquier funcion que llame la app. Una funcion reescrita para derivar su actor no tendria sesion de donde derivarlo y seria inllamable por la unica arquitectura que terra tiene. setArchiveContext tampoco es segunda opinion: archive-context.ts:124 siembra app.terraUserId con set_config desde el usuario resuelto por la app, pero un llamador directo lo siembra con lo que quiera, y GUC y createdBy coincidirian siendo ambos elegidos por el llamador. Evidencia completa: agents/db-terra/evidence/2026-09-11-terra_231-second-write-path-enumerated.result.md
• wi-cli-venus decision · 5w ago
  GO ISSUED, AND MY OWN FRAMING OF THIS ROW WAS WRONG IN ONE RESPECT. Marker: T231-dec-mtx1l. Authorizing GO: coder-terra-cc-mtx1lk12x9cj, terra PM lane under Elazar's 09:27 ART standing delegation. Measurement: db-terra-cc, marker T231-enum-mtx1d, evidence agents/db-terra/evidence/2026-09-11-terra_231-second-write-path-enumerated.result.md, SELECT only. NOT A PRIVILEGE DEFECT. I filed this reading the caller-supplied actorUserId as a privilege surface. It is not. Both boundary functions are SECURITY INVOKER, owner postgres, ACL `postgres=X | service_role=X` - no anon, no authenticated. A caller who can reach the function can already INSERT into terraPracticas with any createdBy it likes, so the argument confers no privilege it did not have. terra_6:319-325 is a CORRECTNESS GUARD FOR A TRUSTED CALLER, never a boundary against a hostile one. p1 stands, as ATTRIBUTION-PROVENANCE. The fix is a column and a gate, not a lock. THE CENSUS CLOSES THE (a)/(b) QUESTION: TWO persistent writers, and the second is not an unknown script - it is our own boundary function. practica-actions.ts:624 is the ONLY app insert (every other INSERT INTO terraPracticas* in src/ is a CHILD table). createTerraPractica at terra_6:397, redefined by terra_12B:211 and terra_42:60, is three revisions of ONE function, described at terra_42:30 as "the edge-callable function used for mock/API creation", with ZERO call sites in src/. Everything else is a dryrun that rolls back or a .test.ts running indexOf over the statement as a string. WHICH WROTE THE THREE ROWS, AND THE METHOD MATTERS MORE THAN THE ANSWER. The function did, and three independent signals agree: pacienteHistoriaClinica reads HC-MOCK-001/002/003; reciboNumber is NULL on the three and set on all three app rows; and the NAME SNAPSHOT discriminates by BUILD - ce6612a (08-22 15:42) made the APP write pacienteNombre/pacienteApellido, while the function did not snapshot names until terra_42, so the three 12:10 rows are NULL on both fields while the 16:11:54 row THE SAME DAY, 3h58m later on the SAME BUILD, carries "juan pereees"/"peress". Two writers distinguished by a field one of them had not learned to fill yet. DERIVING THE ACTOR IS NOT AVAILABLE, so the design question this row opened is CLOSED, not open. pool.ts:19-24 connects as a DB role through the transaction pooler with no per-user JWT and zero PostgREST calls, so auth.uid() is NULL inside anything the app calls. A derive-only function has no session to derive from and would be uncallable by the only architecture terra has. And setArchiveContext is confirmed NOT a second opinion: archive-context.ts:124 seeds app.terraUserId from the resolved user, but a direct caller seeds it with anything - GUC and createdBy agree and are both caller-chosen. THE FIX, GO'd, three conditions all binding. (1) A `createdVia` text column on terraPracticas, NULL on the six existing rows and NEVER backfilled - terra_40's rule; backfilling would assert we know how rows were written on the very rows we just finished proving we cannot attribute. (2) EACH WRITE PATH HARDCODES ITS OWN LITERAL INSIDE THE STATEMENT AND IT IS NEVER A FUNCTION ARGUMENT - a createdVia you can pass is this same defect one level up wearing a new column. (3) A totality gate in the f8b667c shape: enumerate every INSERT INTO terraPracticas in the corpus and go red on one that does not carry a literal, so the NEXT writer has to declare itself. Without (3) the column documents two paths and silently misses the third. isSynthetic CANNOT BE REPURPOSED FOR THIS and the reason is the next finding: those rows are correctly non-synthetic by terra's definition and are still mocks. That gap is the whole point of a separate column. THE FINDING THAT OUTRANKS THIS ROW: THREE OF THE SIX ROWS IN ENAMEL'S SHARED `pacientes` REGISTRY ARE MOCK PATIENTS WITH isSynthetic=false. Half a shared clinical registry is mock data that no flag identifies, and both enamel lanes cited those six rows to terra as a live measurement within the half hour. Escalated to them separately; it also invalidates every usage claim drawn from that corpus, including two I made on TERRA-150 today.
• wi-cli-venus titleChanged · 5w ago
  terraPracticas has two writers and no provenance column: the mock/API boundary function names its own actor, so createdBy cannot be read as who recorded the practica
• wi-cli-venus audit-finding · 5w ago
  Class-A pre-apply review, terra_231_practica_created_via_provenance.sql (audit-terra-cc, live-DB verified via audit_terra_cc_ro). ATTACK 1 - function body identical to live apart from the two named deltas: CONFIRMED, byte-for-byte. Pulled live pg_get_functiondef(createTerraPractica), stripped the migration file's comments, normalized whitespace, removed the two claimed deltas (createdVia in the INSERT column list, 'boundaryFn' in VALUES) from the migration text, diffed against the live def: zero residual difference. The claim in the file header ('reproduces the LIVE definition ... with exactly two deltas') is exactly true, not approximately true. ATTACK 2 - can CREATE OR REPLACE move ACL/prosecdef in a way the four assertions would miss: ACL is safe by Postgres semantics (REPLACE preserves ownership+permissions unconditionally; the file's v_acl assertion is legitimate belt-and-suspenders, not load-bearing). prosecdef is NOT covered by that guarantee - Postgres docs: on REPLACE, 'all OTHER properties are updated to what is specified or implied by the command', and SECURITY DEFINER/INVOKER is one of those other properties. This migration's CREATE OR REPLACE does not say SECURITY DEFINER, so it implies INVOKER regardless of prior state. THE DO $ BLOCK HAS NO prosecdef ASSERTION AT ALL - only v_col/v_backfil/v_literal/v_acl. Verified live right now: prosecdef=f, owner=postgres, proacl='postgres=X/postgres | service_role=X/postgres' - matches the file's premise (lines 48-49), so target==current and this migration does not flip anything. But the gap in the self-check is real and independent of this instance being safe: add a fifth assertion (SELECT prosecdef FROM pg_proc WHERE ... ; IF prosecdef THEN RAISE EXCEPTION) for parity with the ACL check, since 'both boundary functions are SECURITY INVOKER' is asserted in prose (line 48) but not in code. Recorded as a generalizable finding in db-verification-traps.md. No other issues. NOT VALID constraint / no-backfill / literal-only-never-argument design all check out against the stated rule and the file's own reasoning is internally consistent (checked against terra_40/terra_42/terra_6 precedent it cites, all real prior migrations in db/migrations/applied/). PASS. Non-blocking suggestion: add the prosecdef assertion before or without holding up apply - your call, since live state already matches.
• wi-cli-venus commit · 5w ago
  APLICADA. Marcador: T231-applied-a561c53. NO CIERRO LA FILA: falta el push de la mitad de app, que es de coder-terra-cc. terra_231_practica_created_via_provenance.sql aplicada 2026-09-11 14:39:45 UTC, registro 20260911143945. GO coder-terra-cc-mtx1lk12x9cj, PASS audit-terra-cc-mtx28iqfkpjh, ensayo pareado en db/migrations/dryrun/2026-09-11-terra_231-created-via-provenance.sql con su transcripcion (marcador T231-dryrun-mtx2a): siete brazos verdes, cero residuo verificado despues del aborto. VERIFICACION POST-APPLY, en sentencia aparte contra el catalogo vivo y no leida del reporte: "createdVia" text nullable presente, CHECK presente, 6 filas y 0 con valor, ACL postgres=X/postgres mas service_role=X/postgres, prosecdef=false. EL PASS ENCONTRO UNA FALTA REAL. El bloque de aserciones miraba proacl pero no prosecdef, y prosecdef es exactamente la mitad que Postgres NO arrastra: una clausula SECURITY omitida en CREATE OR REPLACE implica INVOKER sin importar lo que la funcion fuera antes, sin error y sin aviso. El estado ya era correcto; lo que faltaba era la asercion. Agregada como asercion 5 antes de aplicar. EL GATE SE ARMO SOLO Y ENCONTRO LO QUE TENIA QUE ENCONTRAR. src/lib/practica-write-provenance-guard.test.ts se arma leyendo db/migrations/applied/ - no una constante escrita a mano - asi que al mover el archivo paso de skip a activo sin editar una linea. Marco terra_6, terra_12B y terra_42: tres definiciones VIEJAS de createTerraPractica, en migraciones aplicadas que no se editan nunca. Saltear applied/ dejaria al gate ciego a la migracion que define la funcion de hoy, y nombrar los tres archivos es la lista de excepciones que este repo se niega a llevar. La regla quedo CALCULADA del corpus: una definicion queda superseded cuando una migracion posterior reemplaza la misma funcion, comparando terra_N numericamente y no lexicamente. La mas nueva sigue bajo el gate. Brazo de control en las dos direcciones, incluido el caso que importa - si la definicion MAS NUEVA es la que perdio createdVia, se marca igual. SECUENCIA, y la direccion es la inversa de la que yo habia escrito. La columna primero, la mitad de app despues. terra tiene UN entorno y un push despliega en minutos, asi que src nombrando una columna que todavia no existe es 42703 en el flujo principal. "Mismo commit" tenia razon en la atomicidad y estaba al reves en la direccion. Quedo escrito en el encabezado del archivo, no solo entendido. La ventana entre el apply y el push escribe NULL y es de minutos; la inversa es una caida. Commits: 9b13a38 (borrador, evidencia, gate), e261ca6 (el gate se prueba a si mismo desarmado), a561c53 (apply, REPLAY-ORDER, supersesion). Suite 556/556, citations rc=0. Sin push: los carriles de base no empujan. Avisado en db-terra-cc-mtx2gfk349l6.
• wi-cli-venus completed · 5w ago
  T231-close-3bb77d3: both halves shipped, the row's own defect is answered. SCHEMA: terra_231 applied 2026-09-11 14:39:45 UTC, registration 20260911143945, GO coder-terra-cc-mtx1lk12x9cj, PASS audit-terra-cc-mtx28iqfkpjh, commits e261ca6 + a561c53. terraPracticas.createdVia is text, nullable, CHECK NULL/appAction/boundaryFn; createTerraPractica writes the 'boundaryFn' literal HARDCODED IN THE STATEMENT and never as an argument - a passable provenance is the same defect one level up. The six historical rows stay NULL and are never backfilled. APP: coder-terra-cc writes 'appAction' at practica-actions.ts:657. GATE: src/lib/practica-write-provenance-guard.test.ts, armed off the applied corpus, computing supersession rather than carrying an exemption list - a definition is superseded when a LATER applied migration replaces the same function, compared NUMERICALLY, so terra_6/12B/42 fall out on their own and whatever replaces the current definer next is caught. AUDIT FINDING ANSWERED IN THE FILE: CREATE OR REPLACE carries ownership and ACL across by Postgres semantics but NOT prosecdef - an omitted SECURITY clause means INVOKER regardless of prior state, a silent downgrade with no error - so assertion 5 pins prosecdef, which is the load-bearing one, and the proacl assertion is real but is not. Live state re-verified after the apply: acl postgres=X/postgres | service_role=X/postgres, prosecdef=false. LATE FIX, worth reading before writing the next gate: the file ran 556/556 green and still blocked every lane's push on three tsc --noEmit narrowing errors, because npx tsx --test EXECUTES without typechecking. Fixed in 3bb77d3. A gate's tests cannot see the app's types and the app's tests cannot see the gate's; only the push hook sees both. NOT CLOSED BY THIS AND NOT A LOOSE END: deriving the actor in the database stays unavailable by architecture - the pool holds no JWT and auth.uid() is NULL - which is why this row got a column and a gate rather than a lock.