Deleted users retain active-role grants in view and teacher validator

Sub-tickets

Activity

• db-pluto-ca created · 1w ago
• db-pluto-ca statusChanged · 1w ago
  Live role-read census complete; Class-A audit design ping sent. View returns 17 stale grants from 15 deleted users; direct teacher validator can accept one deleted docente. No source edit or production write pending design response.
• db-pluto-ca decision · 1w ago
  Audit-general-ca design PASS only (tag audit-general-ca-muwrs9i426bc): normal role writers lock live users first, sorted IDs in bulk; active-NEW-role table backstop checks every INSERT and all-column UPDATE incl. userId reassignment. Direct privileged UPDATE may invert row-lock order; 40P01/40001 retry is exceptional, no committed stale grant allowed. Cascade only on OLD.deletedAt NULL -> NEW non-NULL; role-sync primaryRoleId update must not recurse. Source/forced-red evidence and diff review still owed; no DDL or production apply cleared.
• db-pluto-ca progress · 1w ago
  Draft source-only migration 133-pluto-782-live-user-role-invariant.sql, SHA256 407536e1e541f294e27e593d1eadf69c068b8cffca6d9856d2a91d2df0c4f2a8. Includes live-parent view filter, AFTER active-role guard (all INSERT/UPDATE incl. owner reassignment), parent-first add/bulk/primary-role writers, user live->deleted cascade, exact 17-role/15-user digest-gated historical repair and archive-evidence postflight. Read-only prod recheck: 17 cohort rows, digest f8d917b7d4970f77af0741535e91f86c, sentinel actor exists. No SQL executed; no apply authority or write-capable Pluto seat. Source review and forced-red tests outstanding; companion PLUTO-783 restore contract not landed.
• db-pluto-ca blocked · 1w ago
  Source-only migration 133 split from 134 per PM sequencing, but view fix alone makes deleted non-alumno users appear roleless to validateAlumnoUser and can flip addAlumnoAssignment from refusal to acceptance. Read-only prod count: 2 of 15 deleted users with live role rows have no alumno role. PM ruling requested on live-target check vs structural DB guard; audit old combined-file hash withdrawn. No production write/exercise or apply GO.
• db-pluto-ca verified · 1w ago
  Forced-red baseline, read-only production: current public.vUserRolesActive returns 1 docente-role row whose owning users.deletedAt IS NOT NULL (no identifiers exported). The proposed fixed-view exclusion and addDocenteMembership refusal are NOT yet exercised; no DDL/write seat, source draft held over validateAlumnoUser zero-role flip. Read-only cohort: 2 deleted users with active non-alumno roles would newly pass validateAlumnoUser after view tightening; assignment is source inference, not exercised.
• db-pluto-ca blocked · 1w ago
  PM ruled BOTH: studentAssignments live-user AFTER trigger is the load-bearing DB gate and MUST land in same/earlier apply as owner-anchored vUserRolesActive; app live-target message is separate PLUTO-784 (coder). Class-A design ping to audit-general-ca sent before trigger implementation; source diff/forced-red review and exact apply GO still absent. No Pluto DDL seat provisioned. Migration 133 source held, 134 cascade/repair later behind 783.
• db-pluto-ca verified · 1w ago
  2026-10-06 live read-only catalog preflight for rolled-back studentAssignments INSERT forced-red: trg_studentAssignments_archive fires AFTER INSERT and fn_archive_studentAssignments inserts archive row; studentAssignmentsArchive.archiveSeq has nextval(...) default. A successful baseline INSERT consumes a sequence value that ROLLBACK cannot restore, so PM no-sequence prerequisite FAILS. No INSERT/prod write performed; other outward/realtime legs not checked because first gate failed. PM asked for adjusted synthetic-scratch/production-readonly route or explicit sequence-consumption authority; no DB writer seat in this lane.
• db-pluto-ca blocked · 1w ago
  Audit-general-ca design BLOCK audit-general-ca-muwserf6zqta: AFTER studentAssignments active-row guard is only a write-time gate; later user soft-delete leaves active assignment, and vStudentAssignmentsActive lacks live-owner filter. Read-only prod: 9 active assignments to 9 deleted users. PM must choose same-apply cascade/read filter for durable invariant or explicitly accept named residual as admission-only gate. Forced-red production INSERT baseline also fails no-sequence prerequisite via archiveSeq nextval. No source/apply clearance or write seat.
• db-pluto-ca decision · 1w ago
  Consumer-direction correction, coder-pluto-cc tag coder-pluto-cc-muwsgwqudi9a: `getActiveRoleNames` has MIXED downstream effects. validateTeacherUser tightens: deleted docente no longer passes addDocenteMembership (source-inferred, unexercised). validateAlumnoUser loosens at roles.length===0: deleted non-alumno user is treated as pre-registered; 2 measured targets flip refusal->acceptance absent the same-apply studentAssignments DB gate. practica-actions isDocente EXISTS tightens; docente-productivity report loses deleted docentes, a visible Informes row-count drop that must be named in the close. General failure mode: removing rows from a source can loosen a consumer that treats EMPTY as authorization. Coder owns PLUTO-784 app check and addDocenteMembership synthetic app forced-red after PLUTO-739, accepted; no prod roster row.
• db-pluto-ca audit-finding · 1w ago
  Audit-general-ca PLUTO-782 DESIGN HOLD addendum audit-general-ca-muwsi10gqsdw: AFTER INSERT/all-column UPDATE studentAssignments check is admission guard, not durable active-assignment=>live-student invariant; 9 observed active assignments already target deleted users. PM must choose historical preservation vs cascade/repair. Old-success production INSERT forced-red prohibited under no-sequence rule: archive trigger calls nextval, rollback does not reclaim. Use synthetic scratch DB for old-success/new-refusal. A later production refusal probe requires fresh catalog census of EVERY reachable trigger/outward leg plus explicit write authorization. Do not treat alphabetically earlier new guard refusal as proof of old-success. No source/apply clearance.
• db-pluto-ca decision · 1w ago
  PM contract 2026-10-06 11:46 ART, tag pmmaster-evolutiva-cc-muwskg6j8la9: studentAssignments trigger is ADMISSION GATE only (no NEW assignment to deleted user), not durable active=>live invariant. Preserve 9 existing active assignment rows for 9 deleted users as history; no cascade/repair/backfill. Same 133 apply must owner-filter the existing active assignment view and vUserRolesActive, with admission trigger installed no later than role-view change. Forced-red read: base 9 visible, current view 9 visible, fixed view 0; write old-success/new-refusal in synthetic scratch DB, not prod (archiveSeq nextval is nontransactional). Live catalog correction: requested vStudentAssignmentsActive does not exist; actual view vActiveStudentAssignments has 13 columns, owner postgres, six-grantee ACL and returns all 9. Name correction asked of PM tag db-pluto-ca-muwsuu4c2f9o; source/apply remain held pending confirmation and design re-review.
• db-pluto-ca blocked · 1w ago
  PM historical-preservation/admission-gate/read-filter policy decided, but exact view name correction pending: live object is vActiveStudentAssignments, not vStudentAssignmentsActive. Design re-review and source/forced-red evidence still owed; no source/apply GO or writer seat.
• db-pluto-ca progress · 1w ago
  Revised untracked migration 133 frozen for source-only audit at SHA-256 ed9a9dbccbf3c0c4f5b36e08123c6a175c201226374331d940c763aeca60e10f (audit request db-pluto-ca-muwszo3t8olp). Adds admission-only AFTER INSERT/all-column UPDATE active-NEW studentAssignments guard before both view changes; owner-filters actual existing vActiveStudentAssignments preserving 13-column shape/ACL; leaves 9 historical base rows untouched. Postflight covers view shape/owner/ACL/behavior, trigger mode/type/WHEN/function, five function body hashes and metadata. Static hash self-check and proof-run-guard forced refusal passed. No DB SQL run; synthetic scratch old-success/new-refusal still unexercised, no source/apply GO, writer seat absent. PM correction on actual view name pending.
• db-pluto-ca decision · 1w ago
  PM tag pmmaster-evolutiva-cc-muwslvzfip3p WITHDRAWS rolled-back production INSERT forced-red; do not seek write authorization or a synthetic scratch INSERT to satisfy PLUTO-782 acceptance. Read-side nine-row red is sufficient: 2026-10-06 SELECT-only prod base 9/9 deleted owners and existing vActiveStudentAssignments 9, cohort ID digest 61d3403c97dec7deed40609f0fe16dee; migration 133 preflight pins exact nine, postflight asserts same nine remain base-visible but view-visible count zero. public.vStudentAssignmentsActive in PM wording is nonexistent; real existing name is public.vActiveStudentAssignments, name correction sent to PM. Superseded hash ed9a9d; exact-byte SOURCE review hash cca9c0bbee3dfed0c41343d88d233ec3906e191fe88f6f2776074c8bb4cfc79a (audit tag db-pluto-ca-muwt15qzgatl). No apply authority or writer credential.
• db-pluto-ca blocked · 1w ago
  Migration 133 source-only frozen at cca9c0bbee3dfed0c41343d88d233ec3906e191fe88f6f2776074c8bb4cfc79a; audit exact-byte SOURCE verdict pending and no apply GO/writer seat. Real active assignment view name correction sent to PM. Historical nine preserved; rolled-back INSERT arm withdrawn by PM.
• db-pluto-ca progress · 1w ago
  PM confirmed real view name vActiveStudentAssignments, 13 columns, tag pmmaster-evolutiva-cc-muwsvh1cugw3. Exact-byte migration 133 supersedes cca9c0; NEW frozen SHA-256 1a22bde550f85cb51c0a864452a860291b4d7970879b9ba9af7a63d78d8f8199, audit review tag db-pluto-ca-muwt70d5pem6. Preflight captures/pins assignment and role view definition MD5s and six-grantee ACL in transaction-local settings; postflight proves definitions changed, ACL unchanged, ordered 13-column assignment view preserved, same exact nine historical base rows, zero deleted-owner rows through view. Substring-only definition assertion removed. Admission gate remains before view changes; no row repair/cascade. Five function-body postflight digests match source. No SQL executed/apply GO; scratch INSERT-arm wording clarification pending separately.
• db-pluto-ca blocked · 1w ago
  Exact-byte source review pending audit-general-ca on 133 SHA-256 1a22bde550f85cb51c0a864452a860291b4d7970879b9ba9af7a63d78d8f8199. PM confirmed actual view name and historical-preservation contract. No apply GO/writer seat; no SQL executed. Scratch INSERT-arm acceptance clarification pending.
• db-pluto-ca progress · 1w ago
  Audit name correction tag audit-general-ca-muwsxsdkaaiw: source must name only real existing vActiveStudentAssignments. Removed obsolete name from comment (zero occurrences); SQL behavior unchanged. Exact-byte review target supersedes 1a22bd: SHA-256 ac6433953af031c431f61d57a3c1f4119ec336b1099ed7021c485a635e641abc, sent audit tag db-pluto-ca-muwt7weucpxa. No SQL executed/apply authority.
• db-pluto-ca blocked · 1w ago
  Exact-byte SOURCE verdict pending audit-general-ca on migration 133 SHA-256 ac6433953af031c431f61d57a3c1f4119ec336b1099ed7021c485a635e641abc. No apply GO/writer seat; no SQL executed.
• db-pluto-ca verified · 1w ago
  audit-general-ca DESIGN PASS tag audit-general-ca-muwsyjwbldae against PM corrected contract: preserve nine historical base rows; replace existing vActiveStudentAssignments in same transactional 133 apply as AFTER INSERT/all-column UPDATE final-NEW admission gate and vUserRolesActive; admission-only, not persistent active-assignment=>live-user invariant; retain 13 columns/order/name, owner/ACL; pre/postflight proves captured nine stay base-visible and become view-invisible and definition changes. Old-success/new-refusal INSERT, if required, synthetic scratch DB only because archiveSeq nextval survives rollback; no production write probe. This is DESIGN clearance only, not source PASS/apply GO. Exact-byte SOURCE request already sent to audit at tag db-pluto-ca-muwt7weucpxa for current SHA-256 ac6433953af031c431f61d57a3c1f4119ec336b1099ed7021c485a635e641abc. No SQL executed.
• db-pluto-ca progress · 1w ago
  audit-general-ca BLOCK tag audit-general-ca-muwt10bwkcow reviewed obsolete ed9a9dbc; its missing pre/post witness/changed-definition findings were already addressed in later source. Its header ambiguity was valid: clarified migration 133 lines 4ff that PLUTO-639 migration 134 cascades/repairs ONLY 17 userRoles rows and NEVER the nine historical studentAssignments. Comment-only new exact-byte source SHA-256 e503268e2da6295437deb80f88a1a6275180317f8e99c6192c2c05c8b5fc6bc2, review requested tag db-pluto-ca-muwtacbnbyuq. No SQL/apply.
• db-pluto-ca blocked · 1w ago
  Source review pending audit-general-ca on latest migration 133 SHA-256 e503268e2da6295437deb80f88a1a6275180317f8e99c6192c2c05c8b5fc6bc2. Older ed9a9dbc BLOCK superseded by pre/post witness and clarified role-only 134. No apply GO/writer seat; no SQL executed.
• db-pluto-ca decision · 1w ago
  PM tag pmmaster-evolutiva-cc-muwt1p44e67g definitive: rolled-back production INSERT arm AND scratch/write-path rehearsal are WITHDRAWN, not pending acceptance. Nine-row pre/post read witness is the forced red. Source PASS means reviewed frozen migration file plus handoff commit through coder pusher, NOT applied DDL; Pluto has no write-capable DB seat. PM dispatch cited obsolete cca9c0bb; latest exact-byte 133 source is SHA-256 e503268e2da6295437deb80f88a1a6275180317f8e99c6192c2c05c8b5fc6bc2, clarified with PM/audit; do not bind verdict to older hash. PLUTO-770 authoring-only follows 133 verdict/handoff.
• db-pluto-ca verified · 1w ago
  audit-general-ca PASS:cca9c0bb tag audit-general-ca-muwt2prnbyyk applies ONLY to obsolete exact bytes, not current migration 133. Current frozen SHA-256 e503268e2da6295437deb80f88a1a6275180317f8e99c6192c2c05c8b5fc6bc2 includes extra captured viewdef/ACL preimage assertions and comment clarification; asked audit for distinct e503 exact-byte verdict tag db-pluto-ca-muwtc87mg39o. No handoff commit under old PASS; no SQL executed/apply authority.
• db-pluto-ca decision · 1w ago
  PM FINAL contract tag pmmaster-evolutiva-cc-muwt5805mhkh supersedes earlier "both arms withdrawn" reading: nine-row 9/9→9/0 witness tests VIEW; separate SYNTHETIC scratch-DB old-success/new-refusal pair required to force ADMISSION TRIGGER red. Only rolled-back PRODUCTION INSERT withdrawn; no live fixture/sequence use or prod write authorization. Exact-byte handoff still blocked: PASS:cca9c0bb covers old bytes; current shared untracked migration 133 is SHA-256 e503268e2da6295437deb80f88a1a6275180317f8e99c6192c2c05c8b5fc6bc2. Reconstruction attempt did not reproduce cca hash, so restored known e503 bytes and retained coder push HOLD; PM asked whether to re-review e503 or supply exact cca copy. No SQL applied.
• db-pluto-ca blocked · 1w ago
  Current 133 SHA e503268e lacks exact-byte SOURCE PASS; old cca9c0bb PASS cannot authorize handoff. PM direction pending on e503 re-review vs exact cca recovery; separate synthetic scratch-DB trigger success/refusal proof required, no production INSERT. No apply GO/writer seat.
• db-pluto-ca verified · 1w ago
  SEPARATE SYNTHETIC SCRATCH-DB ADMISSION-GATE FORCED RED, 2026-10-06. Private local PostgreSQL 17 cluster (no Pluto DSN/data, no production write), synthetic users/studentAssignments FK and AFTER INSERT archive trigger consuming an identity archiveSeq; extracted fn_requireLiveStudentForAssignment and trg_studentAssignments_requireLiveStudent verbatim from current migration 133 using awk, no hand-retyped gate. Deleted synthetic user ID ...0001, live ...0002. Before guard: INSERT active assignment ...0011 to deleted user SUCCEEDED; SELECT old_success|1. After gate: same-class INSERT ...0012 to deleted user REFUSED with SQLSTATE P7822 and exact message PLUTO-782: active assignment requires a live student. Positive control INSERT ...0013 to live user SUCCEEDED. Final synthetic counts: deleted-target accepted-before row 1, live-target positive row 1; archiveSeq max=3 because failed attempt consumed identity sequence but rolled back its archive row. This is why production rollback was prohibited. Cluster stopped and moved to trash; no production SQL. Trigger body local MD5 65bb81f0c240f938f05f5493c5c5f1e5. Source-review/hash handoff remains separate and held: current 133 e503268e, old PASS cca9c0bb only.
• db-pluto-ca decision · 1w ago
  PM FINAL exact-byte rebind tag pmmaster-evolutiva-cc-muwtchfmi6kz: review target migration 133 SHA-256 e503268e2da6295437deb80f88a1a6275180317f8e99c6192c2c05c8b5fc6bc2; cca9c0bb and 1a22bde5 obsolete, no verdict transfer. Acceptance is nine-row READ arm alone. Production AND synthetic scratch INSERT rehearsal withdrawn from acceptance; admit guard production refusal path named UNEXERCISED in close. A synthetic scratch pair was run and recorded before this final ruling; it is historical, not acceptance or proof of production refusal. No SQL applied. On PASS:e503, coder-pluto-cc handoff-pushes exact file; close must say REVIEWED FROZEN FILE, not applied, Elazar-gated apply due no write-capable Pluto DB seat, nine assignment rows preserved no cascade/repair, expected docente-productivity report row-count drop at queries.ts:1850.
• db-pluto-ca blocked · 1w ago
  PM explicitly rebound exact-byte SOURCE review to e503268e; audit verdict pending. Coder handoff-push only after PASS:e503. No apply GO/writer seat; no production SQL.
• db-pluto-ca verified · 1w ago
  audit-general-ca exact-byte SOURCE PASS:e503268e tag audit-general-ca-muwtd6zsnbbh; working file and shared index independently rehashed SHA-256 e503268e2da6295437deb80f88a1a6275180317f8e99c6192c2c05c8b5fc6bc2. PM exact-byte rebind tag pmmaster-evolutiva-cc-muwtchfmi6kz. Coder-pluto-cc handoff push HOLD RELEASED (tag db-pluto-ca-muwtpjywj0ac), exact migration path only; awaiting commit SHA/pushed readback before row close. PASS is source-only, no SQL/apply authority. PM final contract withdrew write-arm rehearsal from acceptance; production admission refusal unexercised.
• db-pluto-ca unblocked · 1w ago
  Exact-byte SOURCE PASS:e503 received; handoff push authorized to coder-pluto-cc, awaiting SHA/readback; not applied.
• db-pluto-ca audit-finding · 1w ago
  Handoff race caught by coder-pluto-cc tag coder-pluto-cc-muwti033cd72: an unreviewed transient 4247d418 file appeared during db-pluto-ca byte-reconstruction attempt after crossed PM instructions; coder did NOT push. db-pluto-ca restored exact PASS:e503 source, then stopped edits; fresh 2026-10-06 15:12:30 UTC working-file SHA-256 e503268e2da6295437deb80f88a1a6275180317f8e99c6192c2c05c8b5fc6bc2 and shared-index blob same e503, status A. Coder requested to independently re-add/hash index and report before pathscoped commit/push; db lane will not touch path through handoff. No unreviewed commit or production DDL.
• db-pluto-ca progress · 1w ago
  Coder-pluto-cc independent post-git-add INDEX SHA-256 e503268e2da6295437deb80f88a1a6275180317f8e99c6192c2c05c8b5fc6bc2, 22144 bytes, staged (tag coder-pluto-cc-muwtknztbmsn). Exact-byte PASS:e503 covers index. NOT pushed: PATH gitpush.sh refused on shared venus-host.push-gate held by coder-venus-cc; coder registered for free DM, no polling. db-pluto-ca frozen off 133 path until coder reports pushed SHA. No production SQL/apply.
• db-pluto-ca verified · 1w ago
  audit-general-ca tag audit-general-ca-muwtmgtr3xt4 confirms the previously recorded synthetic PG17 pair is a genuine admission-gate refusal-path red: deleted-target INSERT succeeded before verbatim function+trigger installation and failed P7822/exact message after; live-target INSERT succeeded. Audit reviewed the BWI event SUMMARY, not raw psql transcript. Coverage is extracted gate on synthetic schema only; NOT full migration apply, production firing, or race behavior. PM later withdrew this write-arm as acceptance requirement, so it is extra evidence, not handoff/apply gate. Exact index hash e503 staged; push still pending shared gate.
• db-pluto-ca progress · 1w ago
  PLUTO-480 schema-freshness hook unblocked: db-pluto-ca ran read-only pnpm db:export via DATABASE_URL_DIRECT on 2026-10-06; succeeded 57 tables/53 views/21 lookup categories, regenerated db/schema.md at 15:22:27 UTC (migration 133 mtime 15:12:30), rebuilt gitignored reference-data.sqlite. To comply with trash-only rule, prior generated SQLite was moved to trash before export (recoverable), then recreated. 133 working hash unchanged e503268e. Export was NOT content-neutral: schema.md remains unstaged dirty (live row-count drift, new columns=1322 section marker, three generated-default expressions); coder told migration-only scoped push must exclude schema.md. No production write/apply.
• db-pluto-ca commit · 1w ago
  Migration-only handoff pushed to origin/main at e7107677853ba56ba1ee1b40193b74ab980aedfe (coder-pluto-cc); HEAD=origin/main readback, one path db/migrations/133-pluto-782-live-user-role-invariant.sql / 464 insertions, committed SHA-256 e503268e2da6295437deb80f88a1a6275180317f8e99c6192c2c05c8b5fc6bc2, audit SOURCE PASS. db/schema.md export diff dirty/unstaged, not part of push; no production DDL/apply. Attribution note: commit has Authored-By: db-pluto-ca + Agent: coder-pluto-cc, not PM-requested Local-Author trailer; PM asked for close exception/follow-up direction, no shared-main rewrite.
• db-pluto-ca decision · 1w ago
  PM tag pmmaster-evolutiva-cc-muwu13pedxb9 confirms Authored-By: db-pluto-ca + Agent: coder-pluto-cc is the CORRECT db-authored/coder-pushed handoff trailer pattern (PTD item 8.4); earlier Local-Author terminology was PM slip. No attribution exception or commit rewrite.
• db-pluto-ca completed · 1w ago
  DONE AS REVIEWED FROZEN FILE, NOT APPLIED DDL. Migration 133 pushed migration-only at e7107677853ba56ba1ee1b40193b74ab980aedfe on origin/main; committed SHA-256 e503268e2da6295437deb80f88a1a6275180317f8e99c6192c2c05c8b5fc6bc2 with audit-general-ca exact-byte SOURCE PASS:e503268e (muwtd6zsnbbh). Pluto has no write-capable DB seat; production apply stays Elazar-gated and has NO apply GO. Existing nine active studentAssignments on deleted users are PRESERVED as historical base rows: no cascade, repair or backfill of them in 133. The admission-trigger refusal path is UNEXERCISED IN PRODUCTION by PM ruling; a separate synthetic scratch pair was recorded as extra evidence, not acceptance or proof of live firing. The nine-row base/view read witness is source-gated, not post-apply readback. When 133 is eventually applied, vActiveStudentAssignments will hide deleted-owner rows and docente-productivity report at src/db/queries.ts:1850 is expected to show a lower row count; this has NOT happened yet. Migration-only push has no app version bump or deploymentId. db/schema.md export diff was not committed. No production SQL executed.