451 lines
18 KiB
PL/PgSQL
451 lines
18 KiB
PL/PgSQL
-- Canonical SQL Schema v1
|
|
-- Unified model for Gitgram + AITimeMachine + Governance
|
|
-- Source document: NORTH_STAR_PRD_ARCHITECTURE_V1.md
|
|
|
|
BEGIN;
|
|
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
CREATE EXTENSION IF NOT EXISTS vector;
|
|
|
|
-- =========================
|
|
-- Core Identity / Tenancy
|
|
-- =========================
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email TEXT NOT NULL UNIQUE,
|
|
handle TEXT NOT NULL UNIQUE,
|
|
display_name TEXT,
|
|
avatar_url TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS organizations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
created_by_user_id UUID REFERENCES users(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS organization_memberships (
|
|
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (organization_id, user_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS api_keys (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
key_hash TEXT NOT NULL UNIQUE,
|
|
scopes JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
last_used_at TIMESTAMPTZ,
|
|
expires_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CHECK ((user_id IS NOT NULL) OR (organization_id IS NOT NULL))
|
|
);
|
|
|
|
-- =========================
|
|
-- Repository / Project Map
|
|
-- =========================
|
|
|
|
CREATE TABLE IF NOT EXISTS repositories (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
github_node_id TEXT UNIQUE,
|
|
github_owner TEXT NOT NULL,
|
|
github_name TEXT NOT NULL,
|
|
full_name TEXT NOT NULL UNIQUE,
|
|
is_private BOOLEAN NOT NULL DEFAULT false,
|
|
default_branch TEXT,
|
|
description TEXT,
|
|
stars_count BIGINT NOT NULL DEFAULT 0,
|
|
forks_count BIGINT NOT NULL DEFAULT 0,
|
|
imported_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
|
|
owner_user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
local_path TEXT,
|
|
is_archived BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CHECK ((organization_id IS NOT NULL) OR (owner_user_id IS NOT NULL))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS project_repositories (
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
repository_id UUID NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (project_id, repository_id)
|
|
);
|
|
|
|
-- =========================
|
|
-- Gitgram Social Layer
|
|
-- =========================
|
|
|
|
CREATE TABLE IF NOT EXISTS repo_profiles (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
repository_id UUID NOT NULL UNIQUE REFERENCES repositories(id) ON DELETE CASCADE,
|
|
headline TEXT,
|
|
bio TEXT,
|
|
hero_media_url TEXT,
|
|
visibility TEXT NOT NULL DEFAULT 'public' CHECK (visibility IN ('public', 'unlisted', 'private')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS posts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
author_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
repository_id UUID REFERENCES repositories(id) ON DELETE SET NULL,
|
|
project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
|
|
type TEXT NOT NULL CHECK (type IN ('post', 'reel', 'story', 'change_story', 'snippet')),
|
|
caption TEXT,
|
|
code_snippet TEXT,
|
|
language TEXT,
|
|
linked_checkpoint_id TEXT,
|
|
visibility TEXT NOT NULL DEFAULT 'public' CHECK (visibility IN ('public', 'followers', 'private')),
|
|
published_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS post_assets (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
|
|
media_type TEXT NOT NULL CHECK (media_type IN ('image', 'video', 'gif')),
|
|
storage_key TEXT NOT NULL,
|
|
mime_type TEXT,
|
|
width INTEGER,
|
|
height INTEGER,
|
|
duration_ms INTEGER,
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS comments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
|
|
author_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
parent_comment_id UUID REFERENCES comments(id) ON DELETE CASCADE,
|
|
body TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS reactions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
target_type TEXT NOT NULL CHECK (target_type IN ('post', 'comment', 'repository')),
|
|
target_id UUID NOT NULL,
|
|
reaction TEXT NOT NULL CHECK (reaction IN ('like', 'love', 'fire', 'clap', 'rocket')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (user_id, target_type, target_id, reaction)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS follows (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
follower_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
target_type TEXT NOT NULL CHECK (target_type IN ('user', 'repository')),
|
|
target_user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
target_repository_id UUID REFERENCES repositories(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CHECK (
|
|
(target_type = 'user' AND target_user_id IS NOT NULL AND target_repository_id IS NULL) OR
|
|
(target_type = 'repository' AND target_repository_id IS NOT NULL AND target_user_id IS NULL)
|
|
)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS trend_scores (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
repository_id UUID NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
|
|
window_start TIMESTAMPTZ NOT NULL,
|
|
window_end TIMESTAMPTZ NOT NULL,
|
|
score NUMERIC(12,4) NOT NULL,
|
|
signals JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (repository_id, window_start, window_end)
|
|
);
|
|
|
|
-- =========================
|
|
-- AITM Time Layer
|
|
-- =========================
|
|
|
|
CREATE TABLE IF NOT EXISTS blobs (
|
|
hash TEXT PRIMARY KEY,
|
|
content BYTEA NOT NULL,
|
|
size BIGINT NOT NULL,
|
|
compression TEXT NOT NULL DEFAULT 'brotli',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS trees (
|
|
hash TEXT PRIMARY KEY,
|
|
entries JSONB NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS checkpoints (
|
|
id TEXT PRIMARY KEY,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
parent_id TEXT REFERENCES checkpoints(id) ON DELETE SET NULL,
|
|
linked_checkpoint_id TEXT REFERENCES checkpoints(id) ON DELETE SET NULL,
|
|
tree_hash TEXT NOT NULL REFERENCES trees(hash),
|
|
trigger_type TEXT NOT NULL CHECK (trigger_type IN ('manual', 'auto', 'before_action', 'after_action', 'restore')),
|
|
trigger_tool TEXT,
|
|
description TEXT,
|
|
session_id TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'fk_posts_linked_checkpoint'
|
|
) THEN
|
|
ALTER TABLE posts
|
|
ADD CONSTRAINT fk_posts_linked_checkpoint
|
|
FOREIGN KEY (linked_checkpoint_id)
|
|
REFERENCES checkpoints(id)
|
|
ON DELETE SET NULL;
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE TABLE IF NOT EXISTS checkpoint_files (
|
|
checkpoint_id TEXT NOT NULL REFERENCES checkpoints(id) ON DELETE CASCADE,
|
|
file_path TEXT NOT NULL,
|
|
blob_hash TEXT REFERENCES blobs(hash),
|
|
size BIGINT,
|
|
mode INTEGER,
|
|
mtime_ms BIGINT,
|
|
change_type TEXT NOT NULL CHECK (change_type IN ('added', 'modified', 'deleted', 'unchanged')),
|
|
PRIMARY KEY (checkpoint_id, file_path)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS diffs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
from_checkpoint_id TEXT NOT NULL REFERENCES checkpoints(id) ON DELETE CASCADE,
|
|
to_checkpoint_id TEXT NOT NULL REFERENCES checkpoints(id) ON DELETE CASCADE,
|
|
file_path TEXT NOT NULL,
|
|
diff_content TEXT NOT NULL,
|
|
stats JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
algorithm TEXT NOT NULL DEFAULT 'histogram',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =========================
|
|
-- AITM Context Layer
|
|
-- =========================
|
|
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
session_id TEXT NOT NULL,
|
|
checkpoint_id TEXT REFERENCES checkpoints(id) ON DELETE SET NULL,
|
|
role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
|
|
content TEXT NOT NULL,
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tool_calls (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
message_id UUID REFERENCES messages(id) ON DELETE SET NULL,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
session_id TEXT NOT NULL,
|
|
checkpoint_before_id TEXT REFERENCES checkpoints(id) ON DELETE SET NULL,
|
|
checkpoint_after_id TEXT REFERENCES checkpoints(id) ON DELETE SET NULL,
|
|
tool_name TEXT NOT NULL,
|
|
arguments JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
result JSONB,
|
|
success BOOLEAN,
|
|
error TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS contexts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
checkpoint_id TEXT NOT NULL REFERENCES checkpoints(id) ON DELETE CASCADE,
|
|
session_id TEXT NOT NULL,
|
|
summary TEXT,
|
|
active_files JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
cursor_position JSONB,
|
|
current_task TEXT,
|
|
goals JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS restore_events (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
actor_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
from_checkpoint_id TEXT REFERENCES checkpoints(id) ON DELETE SET NULL,
|
|
to_checkpoint_id TEXT REFERENCES checkpoints(id) ON DELETE SET NULL,
|
|
scope_type TEXT NOT NULL CHECK (scope_type IN ('file', 'folder', 'project')),
|
|
scope_path TEXT,
|
|
status TEXT NOT NULL CHECK (status IN ('requested', 'applied', 'failed')),
|
|
details JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =========================
|
|
-- AITM Augmented Layer
|
|
-- =========================
|
|
|
|
CREATE TABLE IF NOT EXISTS embeddings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
checkpoint_id TEXT REFERENCES checkpoints(id) ON DELETE SET NULL,
|
|
source_type TEXT NOT NULL CHECK (source_type IN ('code', 'message', 'doc')),
|
|
file_path TEXT,
|
|
chunk_text TEXT NOT NULL,
|
|
chunk_start_line INTEGER,
|
|
chunk_end_line INTEGER,
|
|
embedding vector(1536) NOT NULL,
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS indexed_docs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
url TEXT NOT NULL,
|
|
title TEXT,
|
|
content TEXT NOT NULL,
|
|
embedding vector(1536),
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
indexed_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =========================
|
|
-- Governance / Compliance
|
|
-- =========================
|
|
|
|
CREATE TABLE IF NOT EXISTS consents (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
|
|
project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
|
|
granted_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
consent_type TEXT NOT NULL CHECK (consent_type IN ('operational_capture', 'training_export')),
|
|
granted BOOLEAN NOT NULL,
|
|
effective_from TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
effective_to TIMESTAMPTZ,
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CHECK ((organization_id IS NOT NULL) OR (project_id IS NOT NULL))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS retention_policies (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
|
|
project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
|
|
data_class TEXT NOT NULL CHECK (data_class IN ('checkpoints', 'messages', 'tool_calls', 'social_media', 'exports', 'audit_logs')),
|
|
retention_days INTEGER NOT NULL CHECK (retention_days > 0),
|
|
hard_delete BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CHECK ((organization_id IS NOT NULL) OR (project_id IS NOT NULL))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS export_jobs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
requested_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
consent_id UUID REFERENCES consents(id) ON DELETE SET NULL,
|
|
export_type TEXT NOT NULL CHECK (export_type IN ('training', 'compliance', 'backup')),
|
|
status TEXT NOT NULL CHECK (status IN ('queued', 'running', 'completed', 'failed', 'cancelled')),
|
|
redaction_profile TEXT NOT NULL DEFAULT 'default',
|
|
artifact_uri TEXT,
|
|
summary JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
completed_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS data_delete_requests (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
requester_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
|
|
project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
|
|
target_type TEXT NOT NULL CHECK (target_type IN ('user_data', 'project_data', 'export_data')),
|
|
status TEXT NOT NULL CHECK (status IN ('requested', 'approved', 'processing', 'completed', 'rejected')),
|
|
reason TEXT,
|
|
requested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
completed_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
|
|
project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
|
|
actor_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
actor_api_key_id UUID REFERENCES api_keys(id) ON DELETE SET NULL,
|
|
action TEXT NOT NULL,
|
|
target_type TEXT NOT NULL,
|
|
target_id TEXT,
|
|
ip_address INET,
|
|
user_agent TEXT,
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =========================
|
|
-- Indexes
|
|
-- =========================
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_projects_org ON projects(organization_id);
|
|
CREATE INDEX IF NOT EXISTS idx_projects_owner ON projects(owner_user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_repo_repo ON project_repositories(repository_id);
|
|
CREATE INDEX IF NOT EXISTS idx_posts_repo_created ON posts(repository_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_posts_author_created ON posts(author_user_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_comments_post_created ON comments(post_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_reactions_target ON reactions(target_type, target_id);
|
|
CREATE INDEX IF NOT EXISTS idx_follows_target_user ON follows(target_user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_follows_target_repo ON follows(target_repository_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_follows_user_target
|
|
ON follows(follower_user_id, target_user_id)
|
|
WHERE target_type = 'user';
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_follows_repo_target
|
|
ON follows(follower_user_id, target_repository_id)
|
|
WHERE target_type = 'repository';
|
|
CREATE INDEX IF NOT EXISTS idx_trend_scores_repo_window ON trend_scores(repository_id, window_end DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_checkpoints_project_created ON checkpoints(project_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_checkpoints_session ON checkpoints(session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_checkpoint_files_path ON checkpoint_files(file_path);
|
|
CREATE INDEX IF NOT EXISTS idx_diffs_from_to ON diffs(from_checkpoint_id, to_checkpoint_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_project_session ON messages(project_id, session_id, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_checkpoint ON messages(checkpoint_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tool_calls_project_session ON tool_calls(project_id, session_id, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_contexts_checkpoint ON contexts(checkpoint_id);
|
|
CREATE INDEX IF NOT EXISTS idx_restore_events_project_created ON restore_events(project_id, created_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_embeddings_project_checkpoint ON embeddings(project_id, checkpoint_id);
|
|
CREATE INDEX IF NOT EXISTS idx_embeddings_vector ON embeddings USING ivfflat (embedding vector_cosine_ops);
|
|
CREATE INDEX IF NOT EXISTS idx_indexed_docs_project ON indexed_docs(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_consents_scope ON consents(organization_id, project_id, consent_type, effective_from DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_retention_scope ON retention_policies(organization_id, project_id, data_class);
|
|
CREATE INDEX IF NOT EXISTS idx_export_jobs_project_created ON export_jobs(project_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_delete_requests_status ON data_delete_requests(status, requested_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_project_created ON audit_logs(project_id, created_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messages_fts
|
|
ON messages USING gin (to_tsvector('english', content));
|
|
|
|
COMMIT;
|