Database Schema: Supabase PostgreSQL, pgvector, and RLS

-- UUID generation used by all primary keys
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Vector similarity search for RAG embeddings
CREATE EXTENSION IF NOT EXISTS vector;

-- Case-insensitive text columns (slugs, emails)
CREATE EXTENSION IF NOT EXISTS citext;

-- Planned for full-text trigram search
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Query performance observability
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Treat active records as those with no deletion timestamp
WHERE deleted_at IS NULL

-- Example partial unique index from workspaces
CREATE UNIQUE INDEX uq_workspaces_slug_active
  ON workspaces (slug)
  WHERE deleted_at IS NULL AND slug IS NOT NULL;

# From ai-service/src/models/ai.py
class DocumentEmbedding(Base):
    __tablename__ = "document_embeddings"

    id: Mapped[uuid.UUID]        # UUID PK
    document_id: Mapped[uuid.UUID]   # FK to documents.id (DB-enforced, not in ORM)
    workspace_id: Mapped[uuid.UUID]  # Denormalized for workspace-scoped search
    chunk_index: Mapped[int]         # Zero-based chunk position in the document
    chunk_text: Mapped[str]          # Raw text of this chunk
    embedding: Mapped[list[float]]   # Vector(1536) — OpenAI text-embedding-3-small
    created_at / updated_at          # Timestamps

-- Cosine distance index for fast ANN search, scoped to workspace
CREATE INDEX ON document_embeddings
  USING hnsw (embedding vector_cosine_ops);

// Composite PK: (document_id, seq)
// seq is a monotonic integer per document; enforced by application code
// update column is PostgreSQL bytea (binary Y.js encoded update)
export const document_collab_updates = pgTable("document_collab_updates", {
  document_id: uuid("document_id").notNull(),
  seq:         bigint("seq", { mode: "number" }).notNull(),
  update:      bytea("update").notNull(),
  client_id:   bigint("client_id", { mode: "number" }),  // optional debug info
  user_id:     uuid("user_id"),                          // SET NULL on user delete
  created_at:  timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

// Unique on (document_id, snapshot_seq)
// ydoc: full Y.Doc binary state; state_vector: Y.js state vector for sync
export const document_collab_snapshots = pgTable("document_collab_snapshots", {
  id:                  uuid("id").primaryKey().defaultRandom().notNull(),
  document_id:         uuid("document_id").notNull(),
  snapshot_seq:        bigint("snapshot_seq", { mode: "number" }).notNull(),
  ydoc:                bytea("ydoc").notNull(),
  state_vector:        bytea("state_vector").notNull(),
  created_by_user_id:  uuid("created_by_user_id"),
  created_at:          timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});