Database Schema

This page presents the database structure of ERD Builder Pro visually. The diagrams below represent the main tables stored in the PostgreSQL database and how they relate to each other.

The application supports two database modes:

Mode	Description
Supabase	Uses Supabase Auth (`auth.users`) with `BigInt` ID types, `auth` and `public` schemas
Pure PostgreSQL	Uses a local User model, `Int` ID types, no Supabase Auth dependency — suitable for local/self-hosted development

Database Structure Visualization

Use the interactive diagram below to understand the data flow between tables. Select the tab matching your database mode.

Supabase
Pure PostgreSQL

React Flow

:::tip Navigation Arrow lines indicate Foreign Key relationships between tables. Different colors help you identify table categories (e.g., Purple for Project, Pink for Diagram). :::

In Supabase mode, auth tables are managed by Supabase Auth (auth.users) and are not shown in the diagram. In Pure PostgreSQL mode, local users and sessions tables replace Supabase Auth.

Full SQL (DDL) Code

For developers who want to perform a self-hosted installation or database migration, please use the full SQL schema below matching your selected mode.

Supabase SQL Schema (DDL + RLS Policies + Triggers + Seed)

-- ==========================================
-- SUPABASE SCHEMA — ERD Builder Pro
-- ==========================================
-- Requires: auth.users (Supabase built-in)
-- Run on Supabase SQL Editor or migration.
-- ==========================================

-- ========================
-- 1. MAIN APP TABLES
-- ========================

-- Projects Table
CREATE TABLE IF NOT EXISTS projects (
  id BIGSERIAL PRIMARY KEY,
  uid UUID DEFAULT gen_random_uuid() UNIQUE,
  name TEXT NOT NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  is_deleted BOOLEAN DEFAULT FALSE,
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  is_public BOOLEAN DEFAULT FALSE,
  share_token TEXT,
  expiry_date TIMESTAMPTZ
);

-- Diagrams Table (ERD Files)
CREATE TABLE IF NOT EXISTS diagrams (
  id BIGSERIAL PRIMARY KEY,
  uid UUID DEFAULT gen_random_uuid() UNIQUE,
  name TEXT NOT NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  project_id BIGINT REFERENCES projects(id) ON DELETE SET NULL,
  is_deleted BOOLEAN DEFAULT FALSE,
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  viewport_x FLOAT DEFAULT 0,
  viewport_y FLOAT DEFAULT 0,
  viewport_zoom FLOAT DEFAULT 1.0,
  is_public BOOLEAN DEFAULT FALSE,
  share_token TEXT,
  expiry_date TIMESTAMPTZ,
  published_at TIMESTAMPTZ,
  _version INTEGER DEFAULT 0
);

-- Entities Table
CREATE TABLE IF NOT EXISTS entities (
  id TEXT PRIMARY KEY,
  diagram_id BIGINT REFERENCES diagrams(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  x DOUBLE PRECISION DEFAULT 0,
  y DOUBLE PRECISION DEFAULT 0,
  color TEXT DEFAULT '#6366f1',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Columns Table
CREATE TABLE IF NOT EXISTS columns (
  id TEXT PRIMARY KEY,
  entity_id TEXT REFERENCES entities(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  type TEXT NOT NULL,
  is_pk BOOLEAN DEFAULT FALSE,
  is_nullable BOOLEAN DEFAULT TRUE,
  enum_values TEXT, -- comma separated
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Relationships Table
CREATE TABLE IF NOT EXISTS relationships (
  id TEXT PRIMARY KEY,
  diagram_id BIGINT REFERENCES diagrams(id) ON DELETE CASCADE,
  source_entity_id TEXT REFERENCES entities(id) ON DELETE CASCADE,
  target_entity_id TEXT REFERENCES entities(id) ON DELETE CASCADE,
  source_column_id TEXT,
  target_column_id TEXT,
  source_handle TEXT,
  target_handle TEXT,
  type TEXT DEFAULT 'one-to-many',
  label TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Notes Table
CREATE TABLE IF NOT EXISTS notes (
  id BIGSERIAL PRIMARY KEY,
  uid UUID DEFAULT gen_random_uuid() UNIQUE,
  title TEXT NOT NULL,
  content TEXT DEFAULT '',
  user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  project_id BIGINT REFERENCES projects(id) ON DELETE SET NULL,
  is_deleted BOOLEAN DEFAULT FALSE,
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  is_public BOOLEAN DEFAULT FALSE,
  share_token TEXT,
  expiry_date TIMESTAMPTZ,
  published_at TIMESTAMPTZ,
  _version INTEGER DEFAULT 0
);

-- Drawings Table
CREATE TABLE IF NOT EXISTS drawings (
  id BIGSERIAL PRIMARY KEY,
  uid UUID DEFAULT gen_random_uuid() UNIQUE,
  title TEXT NOT NULL,
  data TEXT DEFAULT '[]',
  user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  project_id BIGINT REFERENCES projects(id) ON DELETE SET NULL,
  is_deleted BOOLEAN DEFAULT FALSE,
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  is_public BOOLEAN DEFAULT FALSE,
  share_token TEXT,
  expiry_date TIMESTAMPTZ,
  published_at TIMESTAMPTZ,
  _version INTEGER DEFAULT 0
);

-- Flowcharts Table
CREATE TABLE IF NOT EXISTS flowcharts (
  id BIGSERIAL PRIMARY KEY,
  uid UUID DEFAULT gen_random_uuid() UNIQUE,
  title TEXT NOT NULL,
  data TEXT DEFAULT '{"nodes":[], "edges":[]}',
  user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  project_id BIGINT REFERENCES projects(id) ON DELETE SET NULL,
  is_deleted BOOLEAN DEFAULT FALSE,
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  is_public BOOLEAN DEFAULT FALSE,
  share_token TEXT,
  expiry_date TIMESTAMPTZ,
  published_at TIMESTAMPTZ,
  _version INTEGER DEFAULT 0
);

-- Entity Changes Table (Audit Trail)
CREATE TABLE IF NOT EXISTS entity_changes (
  id BIGSERIAL PRIMARY KEY,
  entity_type TEXT NOT NULL, -- 'diagram', 'note', 'drawing', 'flowchart'
  entity_id TEXT NOT NULL,
  version INTEGER NOT NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  changes JSONB NOT NULL, -- {field: old_value, field: new_value, ...}
  change_type TEXT DEFAULT 'update', -- 'create', 'update', 'delete'
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ========================
-- 2. AI INTEGRATION TABLES
-- ========================

-- AI Providers Table
CREATE TABLE IF NOT EXISTS ai_providers (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL, -- 'OpenAI', 'Google Gemini', etc.
    code TEXT NOT NULL UNIQUE, -- 'openai', 'gemini', 'openai_compatible'
    base_url TEXT DEFAULT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- AI Models Table
CREATE TABLE IF NOT EXISTS ai_models (
    id BIGSERIAL PRIMARY KEY,
    provider_id BIGINT REFERENCES ai_providers(id) ON DELETE CASCADE,
    model_identifier TEXT NOT NULL, -- 'gpt-4o', 'gemini-1.5-pro'
    display_name TEXT NOT NULL,
    context_window INTEGER DEFAULT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Backups Table
CREATE TABLE IF NOT EXISTS backups (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    file_path TEXT,
    file_size BIGINT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- User Preferences Table
CREATE TABLE IF NOT EXISTS user_preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL UNIQUE REFERENCES auth.users(id) ON DELETE CASCADE,
    backup_folder TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- User AI Configurations Table
CREATE TABLE IF NOT EXISTS user_ai_configs (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
    provider_id BIGINT REFERENCES ai_providers(id) ON DELETE CASCADE,
    selected_model_id BIGINT REFERENCES ai_models(id) ON DELETE SET NULL,
    api_key TEXT NOT NULL,
    is_enabled BOOLEAN DEFAULT TRUE,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(user_id, provider_id)
);

-- AI Chat Sessions Table
CREATE TABLE IF NOT EXISTS ai_chat_sessions (
    id BIGSERIAL PRIMARY KEY,
    uid UUID DEFAULT gen_random_uuid() UNIQUE,
    user_id UUID,
    project_id BIGINT REFERENCES projects(id) ON DELETE SET NULL,
    entity_type TEXT DEFAULT NULL,
    entity_uid TEXT DEFAULT NULL,
    title TEXT DEFAULT 'New Conversation',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- AI Chat Messages Table
CREATE TABLE IF NOT EXISTS ai_chat_messages (
    id BIGSERIAL PRIMARY KEY,
    session_id BIGINT REFERENCES ai_chat_sessions(id) ON DELETE CASCADE NOT NULL,
    role TEXT CHECK (role IN ('system', 'user', 'assistant')) NOT NULL,
    content TEXT NOT NULL,
    selection_text TEXT DEFAULT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- System Prompts Table
CREATE TABLE IF NOT EXISTS ai_system_prompts (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    category VARCHAR(50) NOT NULL DEFAULT 'custom', -- 'system', 'context', 'format', 'custom'
    is_default BOOLEAN DEFAULT false,
    is_built_in BOOLEAN DEFAULT false,
    user_id VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- AI Rules Table
CREATE TABLE IF NOT EXISTS user_ai_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    view_type TEXT NOT NULL CHECK (view_type IN ('erd', 'notes', 'flowchart')),
    content TEXT NOT NULL DEFAULT '',
    is_enabled BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(user_id, view_type)
);

-- ========================
-- 3. INDEXES
-- ========================

CREATE INDEX IF NOT EXISTS idx_entity_changes_lookup ON entity_changes(entity_type, entity_id, version DESC);
CREATE INDEX IF NOT EXISTS idx_entity_changes_user ON entity_changes(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_entity_changes_retention ON entity_changes(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_entity_changes_entity_id ON entity_changes(entity_id);

CREATE INDEX IF NOT EXISTS idx_diagrams_version ON diagrams(_version);
CREATE INDEX IF NOT EXISTS idx_diagrams_updated_at ON diagrams(updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_notes_version ON notes(_version);
CREATE INDEX IF NOT EXISTS idx_notes_updated_at ON notes(updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_drawings_version ON drawings(_version);
CREATE INDEX IF NOT EXISTS idx_drawings_updated_at ON drawings(updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_flowcharts_version ON flowcharts(_version);
CREATE INDEX IF NOT EXISTS idx_flowcharts_updated_at ON flowcharts(updated_at DESC);

-- ========================
-- 4. ROW LEVEL SECURITY
-- ========================

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE diagrams ENABLE ROW LEVEL SECURITY;
ALTER TABLE entities ENABLE ROW LEVEL SECURITY;
ALTER TABLE columns ENABLE ROW LEVEL SECURITY;
ALTER TABLE relationships ENABLE ROW LEVEL SECURITY;
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE drawings ENABLE ROW LEVEL SECURITY;
ALTER TABLE flowcharts ENABLE ROW LEVEL SECURITY;
ALTER TABLE entity_changes ENABLE ROW LEVEL SECURITY;
ALTER TABLE backups ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_preferences ENABLE ROW LEVEL SECURITY;

-- AI tables
ALTER TABLE ai_providers ENABLE ROW LEVEL SECURITY;
ALTER TABLE ai_models ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_ai_configs ENABLE ROW LEVEL SECURITY;
ALTER TABLE ai_chat_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE ai_chat_messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE ai_system_prompts ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_ai_rules ENABLE ROW LEVEL SECURITY;

-- Projects Policies
CREATE POLICY "Users can view their own projects" ON projects FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own projects" ON projects FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own projects" ON projects FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own projects" ON projects FOR DELETE USING (auth.uid() = user_id);

-- Diagrams Policies
CREATE POLICY "Anyone can view public diagrams" ON diagrams FOR SELECT USING (is_public = true AND (expiry_date IS NULL OR expiry_date > NOW()));
CREATE POLICY "Users can view their own diagrams" ON diagrams FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own diagrams" ON diagrams FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own diagrams" ON diagrams FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own diagrams" ON diagrams FOR DELETE USING (auth.uid() = user_id);

-- Notes Policies
CREATE POLICY "Anyone can view public notes" ON notes FOR SELECT USING (is_public = true AND (expiry_date IS NULL OR expiry_date > NOW()));
CREATE POLICY "Users can view their own notes" ON notes FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own notes" ON notes FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own notes" ON notes FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own notes" ON notes FOR DELETE USING (auth.uid() = user_id);

-- Drawings Policies
CREATE POLICY "Anyone can view public drawings" ON drawings FOR SELECT USING (is_public = true AND (expiry_date IS NULL OR expiry_date > NOW()));
CREATE POLICY "Users can view their own drawings" ON drawings FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own drawings" ON drawings FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own drawings" ON drawings FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own drawings" ON drawings FOR DELETE USING (auth.uid() = user_id);

-- Flowcharts Policies
CREATE POLICY "Anyone can view public flowcharts" ON flowcharts FOR SELECT USING (is_public = true AND (expiry_date IS NULL OR expiry_date > NOW()));
CREATE POLICY "Users can view their own flowcharts" ON flowcharts FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own flowcharts" ON flowcharts FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own flowcharts" ON flowcharts FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own flowcharts" ON flowcharts FOR DELETE USING (auth.uid() = user_id);

-- Backups Policies
CREATE POLICY "Users can view their own backups" ON backups FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own backups" ON backups FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Service role can update backups" ON backups FOR UPDATE USING (true);

-- User Preferences Policies
CREATE POLICY "Users can view their own preferences" ON user_preferences FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own preferences" ON user_preferences FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own preferences" ON user_preferences FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own preferences" ON user_preferences FOR DELETE USING (auth.uid() = user_id);

-- Entity Changes Policies
CREATE POLICY "Users can insert their own entity changes" ON entity_changes FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can view their own entity changes" ON entity_changes FOR SELECT TO authenticated USING (auth.uid() = user_id);

-- Entities Policies
CREATE POLICY "Anyone can view entities of public diagrams" ON entities FOR SELECT USING (EXISTS (SELECT 1 FROM diagrams WHERE diagrams.id = entities.diagram_id AND diagrams.is_public = true AND (diagrams.expiry_date IS NULL OR diagrams.expiry_date > NOW())));
CREATE POLICY "Users can manage entities in their own diagrams" ON entities FOR ALL USING (EXISTS (SELECT 1 FROM diagrams WHERE diagrams.id = entities.diagram_id AND diagrams.user_id = auth.uid()));

-- Columns Policies
CREATE POLICY "Anyone can view columns of public diagrams" ON columns FOR SELECT USING (EXISTS (SELECT 1 FROM entities JOIN diagrams ON diagrams.id = entities.diagram_id WHERE entities.id = columns.entity_id AND diagrams.is_public = true AND (diagrams.expiry_date IS NULL OR diagrams.expiry_date > NOW())));
CREATE POLICY "Users can manage columns in their own diagrams" ON columns FOR ALL USING (EXISTS (SELECT 1 FROM entities JOIN diagrams ON diagrams.id = entities.diagram_id WHERE entities.id = columns.entity_id AND diagrams.user_id = auth.uid()));

-- Relationships Policies
CREATE POLICY "Anyone can view relationships of public diagrams" ON relationships FOR SELECT USING (EXISTS (SELECT 1 FROM diagrams WHERE diagrams.id = relationships.diagram_id AND diagrams.is_public = true AND (diagrams.expiry_date IS NULL OR diagrams.expiry_date > NOW())));
CREATE POLICY "Users can manage relationships in their own diagrams" ON relationships FOR ALL USING (EXISTS (SELECT 1 FROM diagrams WHERE diagrams.id = relationships.diagram_id AND diagrams.user_id = auth.uid()));

-- AI Providers Policies (Publicly readable)
CREATE POLICY "Users can view active providers"
ON ai_providers
FOR SELECT
TO public
USING (is_active = true);

-- AI Models Policies (Publicly readable)
CREATE POLICY "Users can view active models"
ON ai_models
FOR SELECT
TO public
USING (is_active = true);

CREATE POLICY "Users can manage models catalog"
ON ai_models
FOR ALL
TO public
USING (true)
WITH CHECK (true);

-- User AI Configs Policies
CREATE POLICY "Users can manage their own AI configs"
ON user_ai_configs
FOR ALL
TO public
USING (auth.uid() = user_id);

-- AI Chat Sessions Policies
DROP POLICY IF EXISTS "policy_ai_chat_sessions_all" ON ai_chat_sessions;
CREATE POLICY "allow_all_sessions_access"
ON ai_chat_sessions FOR ALL
TO public
USING (true)
WITH CHECK (true);

-- AI Chat Messages Policies
DROP POLICY IF EXISTS "policy_ai_chat_messages_all" ON ai_chat_messages;
CREATE POLICY "allow_all_messages_access"
ON ai_chat_messages FOR ALL
TO public
USING (
    EXISTS (SELECT 1 FROM ai_chat_sessions WHERE ai_chat_sessions.id = ai_chat_messages.session_id)
)
WITH CHECK (
    EXISTS (SELECT 1 FROM ai_chat_sessions WHERE ai_chat_sessions.id = ai_chat_messages.session_id)
);

-- AI System Prompts Policies
CREATE POLICY "Allow application access for ai_system_prompts"
ON ai_system_prompts
FOR ALL
TO public
USING (true)
WITH CHECK (true);

-- AI Rules Policies
CREATE POLICY "Users can manage own AI rules"
ON user_ai_rules
FOR ALL
TO public
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- ========================
-- 5. TRIGGERS
-- ========================

-- Version increment for optimistic locking
CREATE OR REPLACE FUNCTION increment_version()
RETURNS TRIGGER AS $$
BEGIN
  NEW._version = COALESCE(OLD._version, 0) + 1;
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Audit trail: captures full row snapshot
CREATE OR REPLACE FUNCTION log_entity_changes()
RETURNS TRIGGER AS $$
DECLARE
  v_user_id UUID;
  v_changes JSONB;
  v_version INTEGER;
BEGIN
  v_user_id := auth.uid();
  IF v_user_id IS NULL THEN
    BEGIN
      v_user_id := NEW.user_id;
    EXCEPTION WHEN OTHERS THEN
      v_user_id := NULL;
    END;
  END IF;

  v_changes := to_jsonb(NEW);
  v_version := COALESCE((to_jsonb(NEW)->>'_version')::INTEGER, 0);

  -- Throttle: skip if last snapshot < 5 min ago
  IF TG_OP = 'UPDATE' THEN
    IF EXISTS (
      SELECT 1 FROM entity_changes
      WHERE entity_type = TG_TABLE_NAME
      AND entity_id = NEW.id::TEXT
      AND created_at > NOW() - INTERVAL '5 minutes'
    ) THEN
      RETURN NEW;
    END IF;
  END IF;

  INSERT INTO entity_changes (entity_type, entity_id, version, user_id, changes, change_type)
  VALUES (TG_TABLE_NAME, NEW.id::TEXT, v_version, v_user_id, v_changes, LOWER(TG_OP));

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply version + audit triggers per table
-- Diagrams
DROP TRIGGER IF EXISTS tr_diagrams_version ON diagrams;
CREATE TRIGGER tr_diagrams_version BEFORE UPDATE ON diagrams FOR EACH ROW EXECUTE FUNCTION increment_version();

-- Notes
DROP TRIGGER IF EXISTS tr_notes_version ON notes;
CREATE TRIGGER tr_notes_version BEFORE UPDATE ON notes FOR EACH ROW EXECUTE FUNCTION increment_version();
DROP TRIGGER IF EXISTS tr_notes_audit ON notes;
CREATE TRIGGER tr_notes_audit AFTER INSERT OR UPDATE ON notes FOR EACH ROW EXECUTE FUNCTION log_entity_changes();

-- Drawings
DROP TRIGGER IF EXISTS tr_drawings_version ON drawings;
CREATE TRIGGER tr_drawings_version BEFORE UPDATE ON drawings FOR EACH ROW EXECUTE FUNCTION increment_version();
DROP TRIGGER IF EXISTS tr_drawings_audit ON drawings;
CREATE TRIGGER tr_drawings_audit AFTER INSERT OR UPDATE ON drawings FOR EACH ROW EXECUTE FUNCTION log_entity_changes();

-- Flowcharts
DROP TRIGGER IF EXISTS tr_flowcharts_audit ON flowcharts;
CREATE TRIGGER tr_flowcharts_audit AFTER INSERT OR UPDATE ON flowcharts FOR EACH ROW EXECUTE FUNCTION log_entity_changes();

-- AI: ensure only one default prompt per user
CREATE OR REPLACE FUNCTION handle_single_default_prompt()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.is_default = true THEN
        UPDATE ai_system_prompts
        SET is_default = false
        WHERE id != NEW.id AND user_id = NEW.user_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS single_default_prompt_trigger ON ai_system_prompts;
CREATE TRIGGER single_default_prompt_trigger
BEFORE INSERT OR UPDATE ON ai_system_prompts
FOR EACH ROW EXECUTE FUNCTION handle_single_default_prompt();

-- Auto-update updated_at on AI rules
CREATE OR REPLACE FUNCTION update_ai_rules_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS ai_rules_updated_at_trigger ON user_ai_rules;
CREATE TRIGGER ai_rules_updated_at_trigger
BEFORE UPDATE ON user_ai_rules
FOR EACH ROW EXECUTE FUNCTION update_ai_rules_timestamp();

-- ========================
-- 6. SEED DATA
-- ========================

-- Seed AI Providers
INSERT INTO ai_providers (name, code, base_url) VALUES
('OpenAI', 'openai', 'https://api.openai.com/v1'),
('Google Gemini', 'gemini', NULL),
('OpenAI Compatible', 'openai_compatible', 'https://ai.paas.id')
ON CONFLICT (code) DO NOTHING;

-- Seed AI Models
INSERT INTO ai_models (provider_id, model_identifier, display_name)
SELECT id, 'gpt-4o', 'GPT-4o (Smartest)' FROM ai_providers WHERE code = 'openai'
UNION ALL
SELECT id, 'gpt-4o-mini', 'GPT-4o Mini (Fast)' FROM ai_providers WHERE code = 'openai'
UNION ALL
SELECT id, 'gemini-1.5-pro', 'Gemini 1.5 Pro' FROM ai_providers WHERE code = 'gemini'
UNION ALL
SELECT id, 'gemini-1.5-flash', 'Gemini 1.5 Flash' FROM ai_providers WHERE code = 'gemini'
ON CONFLICT DO NOTHING;

-- Seed AI Rules for existing users (auto-seeded server-side also)
INSERT INTO user_ai_rules (user_id, view_type, content, is_enabled)
SELECT
    id,
    unnest(ARRAY['erd', 'notes', 'flowchart']),
    unnest(ARRAY[
        '- Each table must have created_at and updated_at columns with TIMESTAMP type.\n- Use snake_case for all table and column naming.\n- Every table must have a PRIMARY KEY named id with BIGSERIAL type.\n- Use consistent FOREIGN KEY with column names ending in _id.\n- Avoid ENUM — use VARCHAR with CHECK constraint instead.\n- Add a deleted_at column for soft delete on master tables.',
        '- Write notes in a clear structured format.\n- Use headings, bullet points, and code blocks.\n- Each note should have a summary at the beginning.',
        '- Use short, clear labels (max 3 words per symbol).\n- Every diagram must have at least one Start and one End node.\n- Use descriptive names for each branch (decision label).'
    ]),
    true
FROM auth.users
ON CONFLICT (user_id, view_type) DO NOTHING;

Pure PostgreSQL SQL Schema (DDL + Foreign Keys + Indexes)

-- -------------------------------------------------------------
-- TablePlus 6.0.0(550)
--
-- Database: erdbuilderpro
-- Generation Time: 2026-06-16 07:09:40.8790
-- -------------------------------------------------------------

CREATE SEQUENCE IF NOT EXISTS ai_chat_messages_id_seq;

CREATE TABLE "public"."ai_chat_messages" (
    "id" int4 NOT NULL DEFAULT nextval('ai_chat_messages_id_seq'::regclass),
    "session_id" int4 NOT NULL,
    "role" text NOT NULL,
    "content" text NOT NULL,
    "selection_text" text,
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE SEQUENCE IF NOT EXISTS ai_chat_sessions_id_seq;

CREATE TABLE "public"."ai_chat_sessions" (
    "id" int4 NOT NULL DEFAULT nextval('ai_chat_sessions_id_seq'::regclass),
    "uid" text,
    "user_id" text,
    "project_id" int4,
    "title" text DEFAULT 'New Conversation'::text,
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "entity_uid" text,
    "entity_type" text,
    PRIMARY KEY ("id")
);

CREATE SEQUENCE IF NOT EXISTS ai_models_id_seq;

CREATE TABLE "public"."ai_models" (
    "id" int4 NOT NULL DEFAULT nextval('ai_models_id_seq'::regclass),
    "provider_id" int4,
    "model_identifier" text NOT NULL,
    "display_name" text NOT NULL,
    "context_window" int4,
    "is_active" bool DEFAULT true,
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE SEQUENCE IF NOT EXISTS ai_providers_id_seq;

CREATE TABLE "public"."ai_providers" (
    "id" int4 NOT NULL DEFAULT nextval('ai_providers_id_seq'::regclass),
    "name" text NOT NULL,
    "code" text NOT NULL,
    "base_url" text,
    "is_active" bool DEFAULT true,
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."ai_system_prompts" (
    "id" text NOT NULL,
    "name" text NOT NULL,
    "content" text NOT NULL,
    "category" text NOT NULL DEFAULT 'custom'::text,
    "is_default" bool DEFAULT false,
    "is_built_in" bool DEFAULT false,
    "user_id" text,
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."backups" (
    "id" text NOT NULL,
    "user_id" text,
    "name" text NOT NULL,
    "status" text NOT NULL DEFAULT 'pending'::text,
    "file_path" text,
    "file_size" int4,
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."columns" (
    "id" text NOT NULL,
    "entity_id" text,
    "name" text NOT NULL,
    "type" text NOT NULL,
    "is_pk" bool DEFAULT false,
    "is_nullable" bool DEFAULT true,
    "enum_values" text,
    "sort_order" int4 DEFAULT 0,
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE SEQUENCE IF NOT EXISTS db_accounts_id_seq;

CREATE TABLE "public"."db_accounts" (
    "id" int4 NOT NULL DEFAULT nextval('db_accounts_id_seq'::regclass),
    "user_id" text NOT NULL,
    "name" text NOT NULL,
    "type" text NOT NULL,
    "host" text,
    "port" int4,
    "user" text,
    "password" text,
    "created_at" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE SEQUENCE IF NOT EXISTS db_catalogs_id_seq;

CREATE TABLE "public"."db_catalogs" (
    "id" int4 NOT NULL DEFAULT nextval('db_catalogs_id_seq'::regclass),
    "account_id" int4 NOT NULL,
    "database_name" text NOT NULL,
    "label" text,
    "created_at" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE SEQUENCE IF NOT EXISTS diagrams_id_seq;

CREATE TABLE "public"."diagrams" (
    "id" int4 NOT NULL DEFAULT nextval('diagrams_id_seq'::regclass),
    "uid" text,
    "name" text NOT NULL,
    "user_id" text,
    "project_id" int4,
    "is_deleted" bool DEFAULT false,
    "deleted_at" timestamp(3),
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "viewport_x" float8 DEFAULT 0,
    "viewport_y" float8 DEFAULT 0,
    "viewport_zoom" float8 DEFAULT 1.0,
    "_version" int4 DEFAULT 0,
    "is_public" bool DEFAULT false,
    "share_token" text,
    "expiry_date" timestamp(3),
    "published_at" timestamp(3),
    "source_connection_id" int4,
    "source_type" text DEFAULT 'blank'::text,
    "data" text,
    PRIMARY KEY ("id")
);

CREATE SEQUENCE IF NOT EXISTS drawings_id_seq;

CREATE TABLE "public"."drawings" (
    "id" int4 NOT NULL DEFAULT nextval('drawings_id_seq'::regclass),
    "uid" text,
    "title" text NOT NULL,
    "data" text DEFAULT '[]'::text,
    "user_id" text,
    "project_id" int4,
    "is_deleted" bool DEFAULT false,
    "deleted_at" timestamp(3),
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "_version" int4 DEFAULT 0,
    "is_public" bool DEFAULT false,
    "share_token" text,
    "expiry_date" timestamp(3),
    "published_at" timestamp(3),
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."entities" (
    "id" text NOT NULL,
    "diagram_id" int4,
    "name" text NOT NULL,
    "x" float8 NOT NULL,
    "y" float8 NOT NULL,
    "color" text DEFAULT '#6366f1'::text,
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE SEQUENCE IF NOT EXISTS entity_changes_id_seq;

CREATE TABLE "public"."entity_changes" (
    "id" int4 NOT NULL DEFAULT nextval('entity_changes_id_seq'::regclass),
    "entity_type" text NOT NULL,
    "entity_id" text NOT NULL,
    "version" int4 NOT NULL,
    "user_id" text,
    "changes" text DEFAULT '{}'::text,
    "change_type" text DEFAULT 'update'::text,
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE SEQUENCE IF NOT EXISTS flowcharts_id_seq;

CREATE TABLE "public"."flowcharts" (
    "id" int4 NOT NULL DEFAULT nextval('flowcharts_id_seq'::regclass),
    "uid" text,
    "title" text NOT NULL,
    "data" text DEFAULT '{"nodes":[], "edges":[]}'::text,
    "user_id" text,
    "project_id" int4,
    "is_deleted" bool DEFAULT false,
    "deleted_at" timestamp(3),
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "_version" int4 DEFAULT 0,
    "is_public" bool DEFAULT false,
    "share_token" text,
    "expiry_date" timestamp(3),
    "published_at" timestamp(3),
    PRIMARY KEY ("id")
);

CREATE SEQUENCE IF NOT EXISTS notes_id_seq;

CREATE TABLE "public"."notes" (
    "id" int4 NOT NULL DEFAULT nextval('notes_id_seq'::regclass),
    "uid" text,
    "title" text NOT NULL,
    "content" text DEFAULT ''::text,
    "user_id" text,
    "project_id" int4,
    "is_deleted" bool DEFAULT false,
    "deleted_at" timestamp(3),
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "_version" int4 DEFAULT 0,
    "is_public" bool DEFAULT false,
    "share_token" text,
    "expiry_date" timestamp(3),
    "published_at" timestamp(3),
    PRIMARY KEY ("id")
);

CREATE SEQUENCE IF NOT EXISTS projects_id_seq;

CREATE TABLE "public"."projects" (
    "id" int4 NOT NULL DEFAULT nextval('projects_id_seq'::regclass),
    "uid" text,
    "name" text NOT NULL,
    "user_id" text,
    "color" text DEFAULT '#6366f1'::text,
    "is_deleted" bool DEFAULT false,
    "deleted_at" timestamp(3),
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "_version" int4 DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."relationships" (
    "id" text NOT NULL,
    "diagram_id" int4,
    "source_entity_id" text,
    "target_entity_id" text,
    "source_column_id" text,
    "target_column_id" text,
    "type" text DEFAULT 'one-to-many'::text,
    "source_handle" text,
    "target_handle" text,
    "label" text,
    "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."sessions" (
    "id" text NOT NULL,
    "token" text NOT NULL,
    "user_id" text NOT NULL,
    "email" text NOT NULL,
    "name" text,
    "created_at" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE SEQUENCE IF NOT EXISTS user_ai_configs_id_seq;

CREATE TABLE "public"."user_ai_configs" (
    "id" int4 NOT NULL DEFAULT nextval('user_ai_configs_id_seq'::regclass),
    "user_id" text NOT NULL,
    "provider_id" int4,
    "selected_model_id" int4,
    "api_key" text,
    "is_enabled" bool DEFAULT true,
    "updated_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."user_ai_rules" (
    "id" text NOT NULL,
    "user_id" text NOT NULL,
    "view_type" text NOT NULL,
    "content" text NOT NULL DEFAULT ''::text,
    "is_enabled" bool NOT NULL DEFAULT true,
    "created_at" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."user_preferences" (
    "id" text NOT NULL,
    "user_id" text NOT NULL,
    "backup_folder" text,
    "created_at" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."users" (
    "id" text NOT NULL,
    "email" text NOT NULL,
    "name" text,
    "password" text NOT NULL,
    "is_super_admin" bool,
    "created_at" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

-- Foreign Keys
ALTER TABLE "public"."ai_chat_messages" ADD FOREIGN KEY ("session_id") REFERENCES "public"."ai_chat_sessions"("id") ON DELETE CASCADE;
ALTER TABLE "public"."ai_chat_sessions" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE SET NULL;
ALTER TABLE "public"."ai_chat_sessions" ADD FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE SET NULL;

-- Indices
CREATE UNIQUE INDEX ai_chat_sessions_uid_key ON public.ai_chat_sessions USING btree (uid);
CREATE INDEX idx_ai_chat_sessions_entity ON public.ai_chat_sessions USING btree (entity_type, entity_uid);
CREATE INDEX idx_ai_chat_sessions_project_id ON public.ai_chat_sessions USING btree (project_id);
ALTER TABLE "public"."ai_models" ADD FOREIGN KEY ("provider_id") REFERENCES "public"."ai_providers"("id") ON DELETE CASCADE;

-- Indices
CREATE UNIQUE INDEX ai_providers_code_key ON public.ai_providers USING btree (code);
ALTER TABLE "public"."ai_system_prompts" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE SET NULL;
ALTER TABLE "public"."backups" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE CASCADE;
ALTER TABLE "public"."columns" ADD FOREIGN KEY ("entity_id") REFERENCES "public"."entities"("id") ON DELETE CASCADE;

-- Indices
CREATE INDEX idx_db_accounts_user ON public.db_accounts USING btree (user_id);
ALTER TABLE "public"."db_catalogs" ADD FOREIGN KEY ("account_id") REFERENCES "public"."db_accounts"("id") ON DELETE CASCADE;

-- Indices
CREATE INDEX idx_db_catalogs_account ON public.db_catalogs USING btree (account_id);
ALTER TABLE "public"."diagrams" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE SET NULL;
ALTER TABLE "public"."diagrams" ADD FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE SET NULL;

-- Indices
CREATE UNIQUE INDEX diagrams_uid_key ON public.diagrams USING btree (uid);
CREATE INDEX idx_diagrams_project_deleted ON public.diagrams USING btree (project_id, is_deleted);
CREATE INDEX idx_diagrams_updated_at ON public.diagrams USING btree (updated_at);
CREATE INDEX idx_diagrams_version ON public.diagrams USING btree (_version);
ALTER TABLE "public"."drawings" ADD FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE SET NULL;
ALTER TABLE "public"."drawings" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE SET NULL;

-- Indices
CREATE UNIQUE INDEX drawings_uid_key ON public.drawings USING btree (uid);
CREATE INDEX idx_drawings_project_deleted ON public.drawings USING btree (project_id, is_deleted);
CREATE INDEX idx_drawings_updated_at ON public.drawings USING btree (updated_at);
CREATE INDEX idx_drawings_version ON public.drawings USING btree (_version);
ALTER TABLE "public"."entities" ADD FOREIGN KEY ("diagram_id") REFERENCES "public"."diagrams"("id") ON DELETE CASCADE;
ALTER TABLE "public"."entity_changes" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE SET NULL;

-- Indices
CREATE INDEX idx_entity_changes_lookup ON public.entity_changes USING btree (entity_type, entity_id, version);
CREATE INDEX idx_entity_changes_retention ON public.entity_changes USING btree (created_at);
CREATE INDEX idx_entity_changes_user ON public.entity_changes USING btree (user_id, created_at);
ALTER TABLE "public"."flowcharts" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE SET NULL;
ALTER TABLE "public"."flowcharts" ADD FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE SET NULL;

-- Indices
CREATE UNIQUE INDEX flowcharts_uid_key ON public.flowcharts USING btree (uid);
CREATE INDEX idx_flowcharts_project_deleted ON public.flowcharts USING btree (project_id, is_deleted);
CREATE INDEX idx_flowcharts_updated_at ON public.flowcharts USING btree (updated_at);
CREATE INDEX idx_flowcharts_version ON public.flowcharts USING btree (_version);
ALTER TABLE "public"."notes" ADD FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE SET NULL;
ALTER TABLE "public"."notes" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE SET NULL;

-- Indices
CREATE UNIQUE INDEX notes_uid_key ON public.notes USING btree (uid);
CREATE INDEX idx_notes_project_deleted ON public.notes USING btree (project_id, is_deleted);
CREATE INDEX idx_notes_updated_at ON public.notes USING btree (updated_at);
CREATE INDEX idx_notes_version ON public.notes USING btree (_version);
ALTER TABLE "public"."projects" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE SET NULL;

-- Indices
CREATE UNIQUE INDEX projects_uid_key ON public.projects USING btree (uid);
CREATE INDEX idx_projects_user_deleted ON public.projects USING btree (user_id, is_deleted);
CREATE INDEX idx_projects_updated_at ON public.projects USING btree (updated_at);
ALTER TABLE "public"."relationships" ADD FOREIGN KEY ("source_entity_id") REFERENCES "public"."entities"("id") ON DELETE CASCADE;
ALTER TABLE "public"."relationships" ADD FOREIGN KEY ("target_entity_id") REFERENCES "public"."entities"("id") ON DELETE CASCADE;
ALTER TABLE "public"."relationships" ADD FOREIGN KEY ("diagram_id") REFERENCES "public"."diagrams"("id") ON DELETE CASCADE;
ALTER TABLE "public"."sessions" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE CASCADE;

-- Indices
CREATE UNIQUE INDEX sessions_token_key ON public.sessions USING btree (token);
CREATE INDEX sessions_token_idx ON public.sessions USING btree (token);
ALTER TABLE "public"."user_ai_configs" ADD FOREIGN KEY ("provider_id") REFERENCES "public"."ai_providers"("id") ON DELETE CASCADE;
ALTER TABLE "public"."user_ai_configs" ADD FOREIGN KEY ("selected_model_id") REFERENCES "public"."ai_models"("id") ON DELETE SET NULL;
ALTER TABLE "public"."user_ai_configs" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE CASCADE;

-- Indices
CREATE UNIQUE INDEX user_ai_configs_user_id_provider_id_key ON public.user_ai_configs USING btree (user_id, provider_id);
ALTER TABLE "public"."user_ai_rules" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE CASCADE;

-- Indices
CREATE UNIQUE INDEX user_ai_rules_user_id_view_type_key ON public.user_ai_rules USING btree (user_id, view_type);
ALTER TABLE "public"."user_preferences" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE CASCADE;

-- Indices
CREATE UNIQUE INDEX user_preferences_user_id_key ON public.user_preferences USING btree (user_id);

-- Indices
CREATE UNIQUE INDEX users_email_key ON public.users USING btree (email);

Main Table Summary

Table	Schema	Category	Description
`users`	Supabase: `auth` / Pure: `public`	Auth	User data (Supabase: `auth.users` via Supabase Auth; Pure: local model with email/password)
`sessions`	`public` (Pure only)	Auth	Session token for local auth (Pure PostgreSQL only)
`projects`	`public`	Project	Main container for all files (diagrams, notes, drawings, flowcharts)
`diagrams`	`public`	Diagram	Parent table for ERD files, stores viewport, sharing metadata, and source settings
`entities`	`public`	ERD	Stores individual tables within an ERD diagram
`columns`	`public`	ERD	Stores fields/columns of each entity
`relationships`	`public`	ERD	Stores relationship lines between entities (one-to-one, one-to-many, many-to-many)
`entity_changes`	`public`	Audit	Entity change history for version history / snapshot feature
`notes`	`public`	Content	Rich text notes with soft-delete and sharing
`drawings`	`public`	Content	Freeform drawings based on JSON
`flowcharts`	`public`	Content	Flowcharts with nodes/edges data in JSON format
`backups`	`public`	Utility	Database backup history per user
`ai_providers`	`public`	AI	List of AI providers (OpenAI, Anthropic, etc.)
`ai_models`	`public`	AI	AI models available per provider
`user_ai_configs`	`public`	AI	Per-user AI configuration (provider, model, API key)
`ai_chat_sessions`	`public`	AI	AI Chat conversation sessions
`ai_chat_messages`	`public`	AI	Individual messages within an AI Chat session
`ai_system_prompts`	`public`	AI	System prompts for AI (built-in & custom)
`user_ai_rules`	`public`	AI	Per-view AI rules (e.g., rules for ERD, flowchart, etc.)
`user_preferences`	`public`	Utility	Desktop app user preferences

Key Differences Between Supabase and Pure PostgreSQL

Aspect	Supabase	Pure PostgreSQL
User model	`auth.users` (from Supabase)	Local `users` (UUID, email, password)
Auth tables	~30 auth tables (identities, sessions, mfa, sso, etc.)	1 `sessions` table
ID type	`BIGSERIAL` / `BigInt`	`SERIAL` / `Int`
Timestamps	`TIMESTAMPTZ(6)` with precision	`TIMESTAMP(3)` / `TIMESTAMPTZ`
Project	no `color`, `updatedAt`, `version`	has `color`, `updated_at`, `_version`
EntityChange.changes	`JSONB`	`TEXT` (serialized JSON)
Backup.file_size	`BIGINT`	`INTEGER`
Best for	Production with Supabase	Local development / self-hosted

Database Structure Visualization​

Full SQL (DDL) Code​

Main Table Summary​

Key Differences Between Supabase and Pure PostgreSQL​

Database Structure Visualization

Full SQL (DDL) Code

Main Table Summary

Key Differences Between Supabase and Pure PostgreSQL