supabase_schema.sql

-- ============================================================
-- RABELIA - Schema Supabase complet (v2.0)
--
-- Fusionne les tables B2B multi-tenant (tenants, user_roles, invitations,
-- api_keys, usage_metrics) avec les tables legacy (conversations, messages,
-- user_memory, events, feedback) utilisees par le code applicatif.
--
-- Ce fichier est destine aux NOUVEAUX deploiements. Si les tables existent
-- deja, NE PAS re-executer ce script (utilisez ALTER TABLE manuellement).
-- ============================================================

-- =========================
-- TENANTS (entreprises B2B)
-- =========================
CREATE TABLE IF NOT EXISTS tenants (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name         VARCHAR(255) NOT NULL,
    slug         VARCHAR(100) NOT NULL UNIQUE,       -- ex: "acme-corp"
    owner_id     UUID NOT NULL,                       -- Supabase auth user
    plan         VARCHAR(20) DEFAULT 'free',          -- free, pro, enterprise
    max_users    INT DEFAULT 5,
    is_active    BOOLEAN DEFAULT TRUE,
    created_at   TIMESTAMPTZ DEFAULT NOW(),
    updated_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_tenants_owner ON tenants(owner_id);
CREATE INDEX idx_tenants_slug  ON tenants(slug);


-- =========================
-- USER ROLES (appartenance tenant)
-- =========================
CREATE TABLE IF NOT EXISTS user_roles (
    id         BIGSERIAL PRIMARY KEY,
    user_id    UUID NOT NULL,
    tenant_id  UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    role       VARCHAR(20) DEFAULT 'member',         -- owner, admin, member, viewer
    invited_by UUID,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(user_id, tenant_id)
);

CREATE INDEX idx_user_roles_tenant ON user_roles(tenant_id);
CREATE INDEX idx_user_roles_user   ON user_roles(user_id);


-- =========================
-- INVITATIONS (en attente)
-- =========================
CREATE TABLE IF NOT EXISTS invitations (
    id          BIGSERIAL PRIMARY KEY,
    email       VARCHAR(255) NOT NULL,
    tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    role        VARCHAR(20) DEFAULT 'member',
    invited_by  UUID,
    token       UUID DEFAULT gen_random_uuid(),
    accepted_at TIMESTAMPTZ,
    expires_at  TIMESTAMPTZ DEFAULT (NOW() + INTERVAL '7 days'),
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_invitations_tenant ON invitations(tenant_id);
CREATE INDEX idx_invitations_email  ON invitations(email);


-- =========================
-- API KEYS (par tenant)
-- =========================
CREATE TABLE IF NOT EXISTS api_keys (
    id          BIGSERIAL PRIMARY KEY,
    tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name        VARCHAR(255) DEFAULT 'Default',
    key_hash    VARCHAR(64) NOT NULL UNIQUE,
    key_prefix  VARCHAR(12) NOT NULL,
    is_active   BOOLEAN DEFAULT TRUE,
    last_used   TIMESTAMPTZ,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, name)
);

CREATE INDEX idx_api_keys_tenant ON api_keys(tenant_id);


-- =========================
-- USAGE METRICS (par tenant / jour)
-- =========================
CREATE TABLE IF NOT EXISTS usage_metrics (
    id            BIGSERIAL PRIMARY KEY,
    tenant_id     UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    date          DATE NOT NULL DEFAULT CURRENT_DATE,
    requests      INT DEFAULT 0,
    tokens_input  BIGINT DEFAULT 0,
    tokens_output BIGINT DEFAULT 0,
    storage_bytes BIGINT DEFAULT 0,
    UNIQUE(tenant_id, date)
);

CREATE INDEX idx_usage_tenant_date ON usage_metrics(tenant_id, date);

-- WHY (Bug): _increment_usage() used to SELECT the current row, add 1 in
-- Python, then UPDATE - a classic lost-update race. Two concurrent
-- /api/ask calls for the same tenant/day can both read requests=5 and
-- both write requests=6, losing one increment. This directly undercounts
-- usage-based billing. UNIQUE(tenant_id, date) already existed on this
-- table, so no dedup migration is needed here (unlike the conversations
-- fix) - INSERT ... ON CONFLICT DO UPDATE ... SET x = x + delta is
-- atomic at the Postgres level regardless of how many concurrent callers
-- hit it.
CREATE OR REPLACE FUNCTION increment_usage(
    p_tenant_id UUID, p_date DATE, p_requests INT, p_tokens_in BIGINT, p_tokens_out BIGINT
)
RETURNS void AS $$
BEGIN
    INSERT INTO usage_metrics (tenant_id, date, requests, tokens_input, tokens_output)
    VALUES (p_tenant_id, p_date, p_requests, p_tokens_in, p_tokens_out)
    ON CONFLICT (tenant_id, date) DO UPDATE SET
        requests      = usage_metrics.requests + EXCLUDED.requests,
        tokens_input  = usage_metrics.tokens_input + EXCLUDED.tokens_input,
        tokens_output = usage_metrics.tokens_output + EXCLUDED.tokens_output;
END;
$$ LANGUAGE plpgsql;


-- =========================
-- CONVERSATIONS (sessions utilisateur)
-- =========================
-- NOTE: pas de colonne tenant_id ici (evite une migration DB).
-- L'isolation tenant se fait en filtrant par user_roles.created_at (date de
-- join tenant) au niveau applicatif (voir get_user_conversations / conversation_belongs_to_user).
CREATE TABLE IF NOT EXISTS conversations (
    id         BIGSERIAL PRIMARY KEY,
    user_id    UUID NOT NULL,
    session_id UUID NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- WHY (Bug #8): _get_or_create_conversation did a check-then-insert with no
-- DB-level constraint backing it. Two concurrent /api/ask calls on the same
-- session_id could both see "no existing row" and both INSERT, producing
-- two conversation rows for one session_id and interleaved/garbled history.
-- This UNIQUE constraint is what ON CONFLICT (session_id) in
-- get_or_create_conversation() below relies on.
-- NOTE FOR EXISTING DEPLOYMENTS: if this race has already happened, adding
-- this constraint will fail on any duplicate session_id already in the
-- table. Do not run this ALTER directly against a live database that
-- predates this fix - use docs/migration_conversations_dedup.sql instead,
-- which de-duplicates safely (reassigning messages before dropping the
-- extra row) before adding the constraint.
ALTER TABLE conversations ADD CONSTRAINT conversations_session_id_key UNIQUE (session_id);

CREATE INDEX idx_conversations_user_id    ON conversations(user_id);
CREATE INDEX idx_conversations_session_id ON conversations(session_id);

-- WHY (Bug #8): a plain upsert(on_conflict="session_id") from Python would
-- work for atomicity, but PostgREST's default merge-duplicates resolution
-- overwrites ALL submitted columns on conflict - including user_id. That
-- would let anyone who reuses/guesses another user's session_id reassign
-- that conversation's ownership. This function inserts normally on the
-- happy path, and on conflict does a no-op update (session_id to its own
-- value) purely so RETURNING still yields the existing row's id - user_id
-- is never touched on conflict.
CREATE OR REPLACE FUNCTION get_or_create_conversation(p_session_id UUID, p_user_id UUID)
RETURNS BIGINT AS $$
DECLARE
    v_id BIGINT;
BEGIN
    INSERT INTO conversations (session_id, user_id)
    VALUES (p_session_id, p_user_id)
    ON CONFLICT (session_id) DO UPDATE SET session_id = EXCLUDED.session_id
    RETURNING id INTO v_id;
    RETURN v_id;
END;
$$ LANGUAGE plpgsql;


-- =========================
-- MESSAGES (contenu des conversations)
-- =========================
CREATE TABLE IF NOT EXISTS messages (
    id              BIGSERIAL PRIMARY KEY,
    conversation_id BIGINT REFERENCES conversations(id) ON DELETE CASCADE,
    user_id         UUID NOT NULL,
    role            VARCHAR NOT NULL CHECK (role IN ('user', 'assistant')),
    content         TEXT NOT NULL,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_messages_conversation_created ON messages(conversation_id, created_at DESC);
CREATE INDEX idx_messages_user_id              ON messages(user_id);


-- =========================
-- USER MEMORY (summary long-terme)
-- =========================
-- NOTE: user_id est TEXT (pas UUID) car le code stocke une cle composite
-- "{tenant_id}:{user_id}" pour isoler les summaries par tenant sans migration
-- de schema (voir tracker.get_user_summary / save_user_summary).
-- En mode single-tenant (tenant_id vide), la cle est juste user_id (UUID).
CREATE TABLE IF NOT EXISTS user_memory (
    user_id    TEXT PRIMARY KEY,
    summary    TEXT DEFAULT '',
    updated_at TIMESTAMPTZ DEFAULT NOW()
);


-- =========================
-- EVENTS (monitoring)
-- =========================
CREATE TABLE IF NOT EXISTS events (
    id         BIGSERIAL PRIMARY KEY,
    type       VARCHAR NOT NULL,
    detail     TEXT,
    latency_ms INT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_events_type_created ON events(type, created_at DESC);


-- =========================
-- FEEDBACK (Human-in-the-Loop)
-- =========================
CREATE TABLE IF NOT EXISTS feedback (
    id          BIGSERIAL PRIMARY KEY,
    session_id  UUID NOT NULL,
    user_id     UUID NOT NULL,
    rating      SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment     TEXT DEFAULT '',
    judge_score FLOAT,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_feedback_session_id ON feedback(session_id);
CREATE INDEX idx_feedback_user_id    ON feedback(user_id);


-- =========================
-- RLS (Row Level Security)
-- Le backend utilise la service_role key -> bypass RLS automatiquement.
-- Les policies ci-dessous s'appliquent aux appels client-side (frontend Supabase JS).
-- =========================
ALTER TABLE tenants       ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_roles    ENABLE ROW LEVEL SECURITY;
ALTER TABLE invitations   ENABLE ROW LEVEL SECURITY;
ALTER TABLE api_keys      ENABLE ROW LEVEL SECURITY;
ALTER TABLE usage_metrics ENABLE ROW LEVEL SECURITY;
ALTER TABLE conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages      ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_memory   ENABLE ROW LEVEL SECURITY;
ALTER TABLE feedback      ENABLE ROW LEVEL SECURITY;

-- B2B tables
CREATE POLICY "tenants_owner" ON tenants FOR ALL
    USING (auth.uid() = owner_id);

CREATE POLICY "user_roles_self" ON user_roles FOR SELECT
    USING (auth.uid() = user_id);

CREATE POLICY "invitations_self" ON invitations FOR SELECT
    USING (EXISTS (
        SELECT 1 FROM user_roles WHERE user_id = auth.uid()
        AND tenant_id = invitations.tenant_id AND role IN ('owner', 'admin')
    ));

-- Legacy tables (user-scoped)
CREATE POLICY "conversations_policy" ON conversations FOR ALL
    USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

CREATE POLICY "messages_policy" ON messages FOR ALL
    USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

-- NOTE: user_memory.user_id est TEXT (cle composite tenant:user), la policy
-- RLS sur auth.uid() (UUID) ne peut pas matcher directement. Le backend
-- gere l'isolation via la service_role key (bypass RLS). Pour les appels
-- client-side, ajouter une policy basee sur un prefixe si necessaire.
CREATE POLICY "feedback_policy" ON feedback FOR ALL
    USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);