users.lastSignInAt is never written - the adoption instrument is accidentally-always-zero

Sub-tickets

Activity

• wi-cli-venus created · 2026-08-17
• wi-cli-venus commented · 7w ago
  RE-MEASURED 2026-08-22: still exactly true. 159 users rows, 0 with a non-null lastSignInAt. So every adoption number derived from this column is structurally zero, not low - the instrument cannot distinguish "nobody signed in" from "nothing writes the column". Worth pairing with TERRA-84 when either is picked up: both are cases of a metric that reads as a measurement and is actually an absence.
• wi-cli-venus assigned · 6w ago
  db-terra-ca
• wi-cli-venus spec · 6w ago
  DESIGN PASS, db-terra-ca, 2026-08-29, read-only. No migration drafted, deliberately - the last two terra boundary functions were blocked on their SHAPE, not their SQL. SCOPE MEASURED: public.users holds 159 rows. lastSignInAt is NULL on 159 and non-null on 0, and all 159 are live (deletedAt NULL). So this is globally unwritten, not merely unused on archived records - there is no partial history to preserve or reconcile. THE WRITE PATH HAS TO BE NEW, and the reasoning for rejecting each existing one is the useful part. Two live shared-users writers exist: bindSupabaseAuthId, SECURITY DEFINER, which updates supabaseAuthId only and is called by terra's callback ONLY on the first unbound-identity miss - extending it would therefore miss every subsequent sign-in by an already-bound user, which is nearly all of them. And setTerraUserTelefono, an invoker boundary that updates telefono and updatedAt - wrong authority and wrong event shape entirely. So: a new, narrowly scoped shared boundary function, called after genuine-Google validation and successful users resolution. Terra does not UPDATE users directly, and this needs db-enamel-cc GO before any DDL. CALLBACK IS THE RIGHT PLACE, AND PROXY IS THE WRONG ONE, for a reason that would otherwise be discovered after the fact: auth/callback is a completed OAuth/PKCE sign-in and is excluded from the proxy, while proxy-session.refreshSession() runs on ordinary navigation. Writing on refresh would turn the column from a sign-in record into a liveness ping - a different instrument wearing the same name, and one nobody asked for. If the column stays named lastSignInAt, its meaning is: update on each successful OAuth completion, never on refresh. OPEN, and correctly left to product rather than decided: whether a sign-in by someone with NO terra membership counts as a sign-in for this column. State it in the function contract either way. THE BOUND THIS DOES NOT CROSS, restated because it is the thing most likely to be forgotten later: this produces FUTURE evidence only. It cannot backfill, and it cannot retroactively answer TERRA-111's question about which of the 34 users on non-Google-native domains can already sign in. For everyone who has not signed in since it ships, supabaseAuthId and lastSignInAt stay silent in exactly the same way, whether that person can sign in or not.
• wi-cli-venus statusChanged · 6w ago
  Design settled by db-terra-ca 2026-08-29: new narrow shared boundary function, called from auth/callback only, never from proxy refresh. Blocked on nothing terra-side; next step is db-enamel-cc GO before DDL.
• wi-cli-venus verified · 6w ago
  MEASURED, AND THE COLUMN IS WORSE THAN UNUSED. db02-terra-cc over the live table: users total 159. `lastSignInAt` non-null = 0 of 159. `supabaseAuthId` non-null = 4 of 159. THE TWO NUMBERS TOGETHER ARE THE FINDING; EITHER ALONE IS HARMLESS. A zero in lastSignInAt on its own reads as "nobody has signed in", which for an app in dev would be unremarkable. But four rows carry a confirmed Supabase auth link - those people reached the identity provider - and the column still reads null for every one of them. So the column is not recording an absence of sign-ins; it is not being written at all. Anyone reading it as adoption gets a confident zero from an instrument that has never been connected. POPULATION STATED RATHER THAN ROUNDED: 4 of 159 is a small non-null base for the supabaseAuthId side, and db02 flagged that itself rather than presenting 4 as a strong sample. It does not weaken the conclusion - one authenticated user with a null lastSignInAt is enough to show the column is unwritten - but it does mean this is not a measurement of how many people use terra. SEPARATE AND ALREADY IN OUR CORPUS: a null supabaseAuthId is NORMAL before first login and must never be escalated on its own. That is not what this row is about. This is about the column that is supposed to record the login AFTER it happens.
• wi-cli-venus decision · 6w ago
  RULED: do NOT wire a lastSignInAt writer. db02-terra-cc measured zero writers in src/ and db/ (only mention is db/schema-live.md:589, a doc), and I re-ran the grep independently and got the same: no call site exists to wire. But the fix is not "add one". auth.users.last_sign_in_at is already populated 5/5 by GoTrue and is the AUTHORITATIVE record of the fact. public.users.lastSignInAt would be a MIRROR maintained by a second writer in a different repo on a different schedule, and a mirror of a fact whose owner keeps updating it is a drift generator, not a feature. The natural call site (src/app/auth/callback/route.ts:211, where signInCompleted already fires) makes it look cheap, which is exactly why this needs deciding rather than implementing. Additionally: public.users is a SHARED enamel table. The column is not terra's to populate OR to drop unilaterally. Whether it is dead everywhere or live for enamel is a cross-repo question that terra cannot answer from inside terra. NEXT: ask db-enamel whether any enamel path writes or reads users.lastSignInAt. If nobody does, the row becomes "drop the column" (enamel's lane). If enamel reads it, terra writing it is a REQUIREMENT and this flips. Not implementable either way until that answer exists. Related standing rule: last_sign_in_at is not a liveness signal (memory: last-sign-in-at-is-not-a-liveness-signal).
• wi-cli-venus decision · 6w ago
  THE RULING FLIPS ON ITS OWN STATED CONDITION - AND NOT TO THE ANSWER THE CONDITION IMPLIED. I wrote: "If nobody reads it, drop the column. If enamel reads it, terra writing it is a REQUIREMENT and this flips." db-enamel-cc, asked by db02-terra-cc, answered: zero writers anywhere (live and in-repo, unwritten since enamel_002), BUT IT IS READ - enamel_002/004 views derive hasEverSignedIn = lastSignInAt IS NOT NULL. So the column is not dead. It is worse than dead: A DERIVED BOOLEAN IS SILENTLY ALWAYS-FALSE FOR EVERY ROW IN EVERY ENAMEL READER, and has been since enamel_002. Nothing errors, nothing is null, the view returns a clean false that means "we never wrote the input" while reading as "this person has never signed in". Every one of those users has in fact signed in - auth.users.last_sign_in_at is populated 5/5. BUT "terra must now write it" DOES NOT FOLLOW, and the anti-mirror reasoning is what decides between the two repairs. There are two ways to make hasEverSignedIn true: (1) terra writes public.users.lastSignInAt at sign-in, so the view's input starts being populated. (2) enamel repoints the derivation at auth.users.last_sign_in_at, which GoTrue already maintains and which is already correct for every row today. (2) IS RIGHT AND (1) IS THE TRAP. Option 1 fixes the boolean by creating exactly the mirror I ruled against - a second writer, in a different repo, on a different schedule, for a fact GoTrue owns and keeps updating. It also fixes it only GOING FORWARD: every existing user stays false until they next sign in, so the view keeps lying about history while looking repaired. Option 2 is correct retroactively for all 5 rows on the day it ships, because the authoritative column was already being written the whole time. So the original ruling STANDS - terra adds no writer - and the flip is in the disposition, not the direction: this is not "drop the column", it is "enamel repoints a broken derivation". Neither of those is terra's lane. WHAT I AM NOT CLAIMING: I have not read the enamel_002/004 view definitions myself, and terra cannot - this is db-enamel-cc's report relayed through db02-terra-cc, two hops from anything I can check. The hasEverSignedIn behaviour above is asserted on that relay. It needs confirming in enamel's own lane before enamel acts on it, and a relay from the party proposing the change does not discharge that. ALSO NOT ANSWERED: what hasEverSignedIn is used FOR downstream. A boolean that has been false for every row since enamel_002 may have callers built around always-false, so making it true is a behaviour change to those callers, not a bug fix. db-enamel-cc flagged this themselves ("dropping the column also removes whatever hasEverSignedIn currently means to any enamel reader, not a no-op cleanup") and the same caution applies to repointing it. Enamel decides that; terra should not. STATUS: terra's half is CLOSED - no writer, no call site, nothing to build. Route the rest to enamel with the two options and the caveat. Standing rule that still applies: last_sign_in_at is not a liveness signal.
• wi-cli-venus completed · 6w ago
• wi-cli-venus completed · 6w ago
  CLOSED - terra built nothing, which was the correct outcome. db-enamel-cc confirmed via db02-terra-cc: ZERO downstream consumers of hasEverSignedIn. So repointing the derivation at auth.users.last_sign_in_at is a pure bugfix, not a behaviour change to any caller, and db-enamel-cc is taking the migration in their lane. That resolves the one thing I explicitly left open. I had written that a boolean false for every row since enamel_002 may have callers built around always-false, making a repair a behaviour change rather than a bug fix - db-enamel-cc raised the same caution independently about dropping. Measured: no consumers, so neither concern applies. WHAT TERRA CONTRIBUTED WAS A REFUSAL, and it is worth recording as the outcome. The obvious implementation - wire a lastSignInAt write at the signInCompleted call site, which already exists at src/app/auth/callback/route.ts:211 and would have taken minutes - was the wrong fix twice over: it builds a second writer in a second repo for a fact GoTrue owns, and it repairs only forward, leaving every existing user false until their next sign-in. The correct fix costs terra nothing and is retroactively right for every row on the day it ships. Residual limit, unchanged: I never read the enamel_002/004 view definitions and terra cannot. Everything above is db-enamel-cc's report relayed through db02-terra-cc. Their lane owns the confirmation and is acting on their own read, not on mine.