migrations/p37_vfs_optimistic_lock.sql

-- backend/migrations/p37_vfs_optimistic_lock.sql
-- GAP-VFS: aggiunge colonna version + trigger auto-bump a vfs_files.
--
-- OPZIONALE — il codice Python usa updated_at come token ottimistico
-- anche senza questa migration (backward compatible).
-- Eseguire nel SQL Editor di Supabase per abilitare il contatore version
-- esplicito (integer monotono, immune a clock skew).
--
-- Idempotente: safe da eseguire più volte.
-- Prerequisito: tabella vfs_files esistente (vedi 20260601_vfs_and_semantic.sql).

-- ── Step 1: colonna version ───────────────────────────────────────────────────
-- DEFAULT 0 per righe esistenti. Il trigger la incrementerà ad ogni UPDATE.
ALTER TABLE public.vfs_files
  ADD COLUMN IF NOT EXISTS version BIGINT NOT NULL DEFAULT 0;

-- ── Step 2: funzione trigger (CREATE OR REPLACE = idempotente) ────────────────
CREATE OR REPLACE FUNCTION public._vfs_bump_version()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- Incremento atomico server-side: Python non deve leggere la versione corrente
  NEW.version = OLD.version + 1;
  RETURN NEW;
END;
$$;

-- ── Step 3: trigger BEFORE UPDATE (DROP IF EXISTS + CREATE = idempotente) ────
DROP TRIGGER IF EXISTS trg_vfs_version ON public.vfs_files;
CREATE TRIGGER trg_vfs_version
  BEFORE UPDATE ON public.vfs_files
  FOR EACH ROW
  EXECUTE FUNCTION public._vfs_bump_version();

-- ── Step 4: index per conflict detection rapido ───────────────────────────────
CREATE INDEX IF NOT EXISTS vfs_files_version_idx
  ON public.vfs_files (id, version);

-- ── Note operative ────────────────────────────────────────────────────────────
-- Dopo questa migration, PUT /api/files/{id} accetta anche:
--   body: { ..., "expected_version": <N> }
-- Se version corrente != N → HTTP 409 { error: "conflict", current_version, current_updated_at }
-- Il campo expected_updated_at (BIGINT ms) funziona allo stesso modo senza migration.