Base de données & Schéma

Cette page documente l'état du schéma PostgreSQL tel qu'il ressort des fichiers Drizzle:

apps/server/src/db/schema/auth.ts
apps/server/src/db/schema/naivi.ts

Note: ce SQL est fourni à titre documentaire. Les migrations officielles sont gérées par Drizzle.

Structure

Dossier: apps/server/src/db/
- index.ts — initialise Drizzle avec DATABASE_URL
- schema/ — définitions Drizzle par module:
  - auth.ts — tables user, session, account, verification
  - naivi.ts — enums, files, chunks, qa_pair, file_assignments, logo, tags, roles, role_tags, item_tags, indexes, index_chunks
- Dossier migrations généré par Drizzle: apps/server/drizzle/ (créé par db:generate)

DDL (PostgreSQL)

-- Enable UUID generation (required for DEFAULT gen_random_uuid())
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ─── Enums ────────────────────────────────────────────────────
CREATE TYPE file_type AS ENUM ('regular', 'qa');
CREATE TYPE classification_status AS ENUM ('pending', 'in_progress', 'completed');
CREATE TYPE category AS ENUM ('dynamic', 'static', 'not an info', 'finetuned', 'not categorized');
CREATE TYPE item_type AS ENUM ('chunk', 'qa_pair');

-- =========================
-- Auth schema (apps/server/src/db/schema/auth.ts)
-- =========================

CREATE TABLE "user" (
  "id"              text PRIMARY KEY,
  "name"            text NOT NULL,
  "email"           text NOT NULL UNIQUE,
  "email_verified"  boolean NOT NULL DEFAULT false,
  "image"           text,
  "created_at"      timestamp NOT NULL DEFAULT now(),
  "updated_at"      timestamp NOT NULL DEFAULT now(),
  "role"            text NOT NULL DEFAULT 'user',
  "banned"          boolean,
  "ban_reason"      text,
  "ban_expires"     timestamp,
  "last_connection" timestamp
);

CREATE TABLE "session" (
  "id"              text PRIMARY KEY,
  "expires_at"      timestamp NOT NULL,
  "token"           text NOT NULL UNIQUE,
  "created_at"      timestamp NOT NULL,
  "updated_at"      timestamp NOT NULL,
  "ip_address"      text,
  "user_agent"      text,
  "user_id"         text NOT NULL REFERENCES "user"("id") ON DELETE CASCADE,
  "impersonated_by" text
);

CREATE TABLE "account" (
  "id"                        text PRIMARY KEY,
  "account_id"                text NOT NULL,
  "provider_id"               text NOT NULL,
  "user_id"                   text NOT NULL REFERENCES "user"("id") ON DELETE CASCADE,
  "access_token"              text,
  "refresh_token"             text,
  "id_token"                  text,
  "access_token_expires_at"   timestamp,
  "refresh_token_expires_at"  timestamp,
  "scope"                     text,
  "password"                  text,
  "created_at"                timestamp NOT NULL,
  "updated_at"                timestamp NOT NULL
);

CREATE TABLE "verification" (
  "id"          text PRIMARY KEY,
  "identifier"  text NOT NULL,
  "value"       text NOT NULL,
  "expires_at"  timestamp NOT NULL,
  "created_at"  timestamp DEFAULT now(),
  "updated_at"  timestamp DEFAULT now()
);

-- =========================
-- Naivi schema (apps/server/src/db/schema/naivi.ts)
-- =========================

-- ─── Unified files table (replaces regular_files + qa_files) ──
-- type = 'regular' → document with chunks
-- type = 'qa'      → Q&A CSV with qa_pair rows
CREATE TABLE "files" (
  "id"                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "name"                  text NOT NULL,
  "old_name"              text,
  "type"                  file_type NOT NULL,
  "created_by"            text NOT NULL REFERENCES "user"("id"),
  "classification_status" classification_status NOT NULL,
  "created_at"            timestamptz NOT NULL DEFAULT now(),
  "last_updated_at"       timestamptz NOT NULL DEFAULT now(),
  "last_updated_by"       text NOT NULL REFERENCES "user"("id")
);
CREATE INDEX "files_type_idx" ON "files" ("type");

-- ─── Chunks ────────────────────────────────────────────────────
CREATE TABLE "chunks" (
  "id"              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "rank"            integer NOT NULL,
  "content"         text NOT NULL,
  "old_content"     text,
  "category"        category NOT NULL,
  "file_id"         uuid NOT NULL REFERENCES "files"("id") ON DELETE CASCADE,
  "embedding"       bytea,
  "last_updated_at" timestamptz NOT NULL DEFAULT now(),
  "last_updated_by" text NOT NULL REFERENCES "user"("id")
);
CREATE INDEX "chunks_file_id_idx" ON "chunks" ("file_id");

-- ─── QA pairs ──────────────────────────────────────────────────
CREATE TABLE "qa_pair" (
  "id"              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "file_id"         uuid NOT NULL REFERENCES "files"("id") ON DELETE CASCADE,
  "rank"            integer NOT NULL,
  "question"        text NOT NULL,
  "old_question"    text,
  "answer"          text NOT NULL,
  "old_answer"      text,
  "category"        category NOT NULL DEFAULT 'not categorized',
  "embedding"       bytea,
  "last_updated_at" timestamptz NOT NULL DEFAULT now(),
  "last_updated_by" text NOT NULL REFERENCES "user"("id")
);
CREATE INDEX "qa_pair_file_id_idx" ON "qa_pair" ("file_id");

-- ─── File assignments ──────────────────────────────────────────
CREATE TABLE "file_assignments" (
  "id"              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "file_id"         uuid NOT NULL REFERENCES "files"("id") ON DELETE CASCADE,
  "assigned_to"     text NOT NULL REFERENCES "user"("id"),
  "assigned_by"     text NOT NULL REFERENCES "user"("id"),
  "last_updated_at" timestamptz NOT NULL DEFAULT now(),
  "last_updated_by" text NOT NULL REFERENCES "user"("id")
);
CREATE UNIQUE INDEX "file_assignments_file_user_unique"
  ON "file_assignments" ("file_id", "assigned_to");
CREATE INDEX "file_assignments_assigned_to_idx" ON "file_assignments" ("assigned_to");

-- ─── Logo (branding assets) ────────────────────────────────────
CREATE TABLE "logo" (
  "id"        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "name"      text NOT NULL,
  "data"      bytea NOT NULL,
  "mime_type" text NOT NULL,
  "is_active" boolean NOT NULL DEFAULT true
);

-- ─── Tags ──────────────────────────────────────────────────────
-- Used to label chunks/qa_pairs for role-based index filtering
CREATE TABLE "tags" (
  "id"              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "name"            text NOT NULL UNIQUE,
  "description"     text,
  "color"           text NOT NULL DEFAULT '#6366f1',
  "created_at"      timestamptz NOT NULL DEFAULT now(),
  "created_by"      text REFERENCES "user"("id"),
  "last_updated_at" timestamptz NOT NULL DEFAULT now(),
  "last_updated_by" text REFERENCES "user"("id")
);

-- ─── Roles ─────────────────────────────────────────────────────
-- Application roles (not Better Auth roles); used for FAISS index scoping
CREATE TABLE "roles" (
  "id"            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "name"          text NOT NULL UNIQUE,
  "description"   text,
  "display_order" integer NOT NULL DEFAULT 0,
  "is_super_admin" boolean NOT NULL DEFAULT false,
  "created_at"    timestamptz NOT NULL DEFAULT now()
);

-- ─── Role-Tag junction ─────────────────────────────────────────
-- Defines which tags are visible/included for a given role
CREATE TABLE "role_tags" (
  "id"         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "role_id"    uuid NOT NULL REFERENCES "roles"("id") ON DELETE CASCADE,
  "tag_id"     uuid NOT NULL REFERENCES "tags"("id") ON DELETE CASCADE,
  "created_at" timestamptz NOT NULL DEFAULT now(),
  "created_by" text REFERENCES "user"("id")
);
CREATE UNIQUE INDEX "role_tags_unique" ON "role_tags" ("role_id", "tag_id");

-- ─── Item-Tag junction (chunks + qa_pairs) ─────────────────────
-- Unified tagging for both chunks and qa_pairs
CREATE TABLE "item_tags" (
  "id"        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "item_id"   uuid NOT NULL,
  "item_type" item_type NOT NULL,
  "tag_id"    uuid NOT NULL REFERENCES "tags"("id") ON DELETE CASCADE
);
CREATE UNIQUE INDEX "item_tags_unique" ON "item_tags" ("item_id", "item_type", "tag_id");
CREATE INDEX "item_tags_item_idx" ON "item_tags" ("item_id", "item_type");

-- ─── FAISS indexes (serialized, per-role) ──────────────────────
-- Stores serialized FAISS indexes in the DB for persistence and role-based access
CREATE TABLE "indexes" (
  "id"               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "serialized_index" bytea NOT NULL,
  "role_id"          uuid REFERENCES "roles"("id") ON DELETE SET NULL,
  "created_at"       timestamptz NOT NULL DEFAULT now(),
  "is_active"        boolean NOT NULL DEFAULT true
);
CREATE INDEX "indexes_role_active_idx" ON "indexes" ("role_id", "is_active");

-- ─── Index-Chunk junction ──────────────────────────────────────
-- Maps a serialized index to its constituent chunk/qa_pair IDs
-- chunk_id references BOTH chunks.id and qa_pair.id (no FK by design)
CREATE TABLE "index_chunks" (
  "id"         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "index_id"   uuid NOT NULL REFERENCES "indexes"("id") ON DELETE CASCADE,
  "chunk_id"   uuid NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX "index_chunks_unique" ON "index_chunks" ("index_id", "chunk_id");

Remarques

Tables unifiées: regular_files et qa_files ont été fusionnées en une seule table files avec un champ type (regular | qa). Toutes les FK pointent désormais sur files.
Tags & Rôles: le système de tags permet d'étiqueter les chunks/qa_pairs. Les rôles (table roles) déterminent quels tags sont visibles et donc quels chunks entrent dans l'index FAISS d'un utilisateur. Les rôles admin et user sont créés automatiquement au démarrage du serveur via ensureDefaultRoles().
Index FAISS persisté: les index FAISS sérialisés sont stockés en base (indexes). La table index_chunks établit la relation entre un index et ses chunks sources. Cela permet de charger un index depuis la DB sans recalculer tous les embeddings.
Embedding sur qa_pair: qa_pair.embedding stocke l'embedding calculé sur "question : answer", mais seule la réponse (answer) est injectée dans le prompt RAG.
gen_random_uuid() requiert l'extension pgcrypto. Si vous utilisez uuid-ossp, remplacez par uuid_generate_v4().
last_updated_at est mis à jour par l'application (clause $onUpdate Drizzle). Pour un trigger SQL automatique, ajoutez-le manuellement.

Database & Schema

Base de données & Schéma

Structure

DDL (PostgreSQL)

Remarques

On this page