<!-- source: openspec/changes/add-user-last-seen-at/design.md -->

# Design — add-user-last-seen-at

> Companion to proposal.md. Numbered deltas, each with rationale.

### 1. Supabase migration — add `last_seen_at` column

```sql
ALTER TABLE public.users
  ADD COLUMN last_seen_at timestamptz DEFAULT NULL;
```

**Rationale.** `timestamptz` is the Supabase/Postgres convention for all temporal columns — avoids timezone ambiguity. Nullable + default `NULL` means no backfill needed; existing rows simply show "never seen." No index added — we don't query by this column yet; add one later if an admin dashboard needs it.

**File:** `supabase/migrations/<timestamp>_add_user_last_seen_at.sql`

### 2. Middleware — fire-and-forget `last_seen_at` update

On every authenticated request, after session validation, issue:

```ts
// Fire-and-forget — do not await
supabase
  .from('users')
  .update({ last_seen_at: new Date().toISOString() })
  .eq('id', user.id)
  .then(null, (err) => console.error('last_seen_at update failed', err));
```

**Rationale.** Fire-and-forget keeps the update off the critical path — no added latency to the response. `console.error` catch ensures a silent DB failure doesn't crash the request. Debouncing is explicitly out of scope (see proposal.md § Out of scope); every request writes. Acceptable for current traffic; optimise if it becomes a bottleneck.

**Placement.** Wherever the authenticated Supabase client is initialised per-request — likely `middleware.ts` or a server-side utility that runs on every route. Exact file TBD based on repo structure.

### 3. RLS policy — users can read own `last_seen_at`, cannot write another user's

The column inherits existing RLS policies on `public.users`. One explicit test is added to confirm:

- **Read own row:** `SELECT last_seen_at FROM users WHERE id = auth.uid()` — succeeds.
- **Update another user's row:** `UPDATE users SET last_seen_at = now() WHERE id = <other_user_id>` — blocked by RLS.

**Rationale.** No new RLS policy is needed if existing row-level policies already restrict reads/writes to `auth.uid()`. The test validates that assumption. If existing policies are insufficient, the migration adds:

```sql
-- Only if not already covered
CREATE POLICY "Users can update own last_seen_at"
  ON public.users FOR UPDATE
  USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);
```

### 4. Edge cases and failure modes

| Scenario | Behaviour |
|---|---|
| Unauthenticated request | Middleware skips update — no user ID available |
| DB write fails (network, RLS) | Caught by `.then(null, err =>)`, logged, request proceeds |
| Concurrent writes from same user | Last-write-wins on `last_seen_at` — acceptable for a timestamp |
| Column already exists (re-run migration) | Postgres `ALTER TABLE ADD COLUMN` will error; use `IF NOT EXISTS` in migration SQL |

**Revision to migration SQL** — safer form:

```sql
ALTER TABLE public.users
  ADD COLUMN IF NOT EXISTS last_seen_at timestamptz DEFAULT NULL;
```
