supabase_schema.sql

-- Supabase/PostgreSQL Schema for Code Graph Database
-- This schema converts the SQLite schema to PostgreSQL-compatible syntax

-- Enable UUID extension for better ID generation (optional)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Repositories table - tracks analyzed codebases
CREATE TABLE IF NOT EXISTS repositories (
    id BIGSERIAL PRIMARY KEY,
    repository_url TEXT,
    repository_path TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT unique_repository_path UNIQUE(repository_path)
);

-- Add comment to table
COMMENT ON TABLE repositories IS 'Stores metadata about analyzed code repositories';
COMMENT ON COLUMN repositories.repository_url IS 'GitHub URL or remote repository URL';
COMMENT ON COLUMN repositories.repository_path IS 'Local or unique identifier path';

-- Graphs table - tracks different graph types for each repository
CREATE TABLE IF NOT EXISTS graphs (
    id BIGSERIAL PRIMARY KEY,
    repository_id BIGINT NOT NULL,
    graph_type TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    total_nodes INTEGER DEFAULT 0,
    total_edges INTEGER DEFAULT 0,
    CONSTRAINT fk_repository
        FOREIGN KEY (repository_id)
        REFERENCES repositories(id)
        ON DELETE CASCADE,
    CONSTRAINT unique_repository_graph_type UNIQUE(repository_id, graph_type)
);

-- Add comment to table
COMMENT ON TABLE graphs IS 'Stores different graph representations (combined, call, declaration)';
COMMENT ON COLUMN graphs.graph_type IS 'Type: combined, call, or declaration';

-- Nodes table - stores all code entities
CREATE TABLE IF NOT EXISTS nodes (
    id BIGSERIAL PRIMARY KEY,
    graph_id BIGINT NOT NULL,
    node_id TEXT NOT NULL,
    node_type TEXT NOT NULL,
    name TEXT NOT NULL,
    qualified_name TEXT,
    file_path TEXT,
    line_number INTEGER,
    parent_id TEXT,
    metadata JSONB,  -- PostgreSQL JSONB for better performance
    CONSTRAINT fk_graph_nodes
        FOREIGN KEY (graph_id)
        REFERENCES graphs(id)
        ON DELETE CASCADE,
    CONSTRAINT unique_graph_node UNIQUE(graph_id, node_id)
);

-- Add comment to table
COMMENT ON TABLE nodes IS 'Stores code entities (functions, classes, methods, etc.)';
COMMENT ON COLUMN nodes.node_type IS 'Type: FUNCTION, CLASS, METHOD, PROPERTY, IMPORT, FILE';
COMMENT ON COLUMN nodes.metadata IS 'Additional node attributes stored as JSON';

-- Edges table - stores relationships between nodes
CREATE TABLE IF NOT EXISTS edges (
    id BIGSERIAL PRIMARY KEY,
    graph_id BIGINT NOT NULL,
    source_id TEXT NOT NULL,
    target_id TEXT NOT NULL,
    edge_type TEXT NOT NULL,
    metadata JSONB,  -- PostgreSQL JSONB for better performance
    CONSTRAINT fk_graph_edges
        FOREIGN KEY (graph_id)
        REFERENCES graphs(id)
        ON DELETE CASCADE
);

-- Add comment to table
COMMENT ON TABLE edges IS 'Stores relationships between code entities';
COMMENT ON COLUMN edges.edge_type IS 'Type: calls, contains, inherits, etc.';

-- Indexes for fast queries

-- Index for finding nodes by parent (for subtree queries)
CREATE INDEX IF NOT EXISTS idx_nodes_parent
ON nodes(graph_id, parent_id);

-- Index for finding nodes by type
CREATE INDEX IF NOT EXISTS idx_nodes_type
ON nodes(graph_id, node_type);

-- Index for finding nodes by file
CREATE INDEX IF NOT EXISTS idx_nodes_file
ON nodes(graph_id, file_path);

-- Index for finding nodes by name (for search)
CREATE INDEX IF NOT EXISTS idx_nodes_name
ON nodes(name);

-- GIN index for JSONB metadata search
CREATE INDEX IF NOT EXISTS idx_nodes_metadata
ON nodes USING GIN(metadata);

-- Index for finding edges by source
CREATE INDEX IF NOT EXISTS idx_edges_source
ON edges(graph_id, source_id);

-- Index for finding edges by target
CREATE INDEX IF NOT EXISTS idx_edges_target
ON edges(graph_id, target_id);

-- Composite index for edge lookups
CREATE INDEX IF NOT EXISTS idx_edges_source_target
ON edges(graph_id, source_id, target_id);

-- GIN index for edge metadata search
CREATE INDEX IF NOT EXISTS idx_edges_metadata
ON edges USING GIN(metadata);

-- Index for repository lookups
CREATE INDEX IF NOT EXISTS idx_repositories_path
ON repositories(repository_path);

-- Index for repository URL lookups
CREATE INDEX IF NOT EXISTS idx_repositories_url
ON repositories(repository_url);

-- Trigger to automatically update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_repositories_updated_at
    BEFORE UPDATE ON repositories
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Row Level Security (RLS) policies for Supabase
-- Enable RLS on all tables
ALTER TABLE repositories ENABLE ROW LEVEL SECURITY;
ALTER TABLE graphs ENABLE ROW LEVEL SECURITY;
ALTER TABLE nodes ENABLE ROW LEVEL SECURITY;
ALTER TABLE edges ENABLE ROW LEVEL SECURITY;

-- Example RLS policies (adjust based on your authentication needs)

-- Allow authenticated users to read all data
CREATE POLICY "Allow authenticated read on repositories"
ON repositories FOR SELECT
TO authenticated
USING (true);

CREATE POLICY "Allow authenticated read on graphs"
ON graphs FOR SELECT
TO authenticated
USING (true);

CREATE POLICY "Allow authenticated read on nodes"
ON nodes FOR SELECT
TO authenticated
USING (true);

CREATE POLICY "Allow authenticated read on edges"
ON edges FOR SELECT
TO authenticated
USING (true);

-- Allow authenticated users to insert their own data
CREATE POLICY "Allow authenticated insert on repositories"
ON repositories FOR INSERT
TO authenticated
WITH CHECK (true);

CREATE POLICY "Allow authenticated insert on graphs"
ON graphs FOR INSERT
TO authenticated
WITH CHECK (true);

CREATE POLICY "Allow authenticated insert on nodes"
ON nodes FOR INSERT
TO authenticated
WITH CHECK (true);

CREATE POLICY "Allow authenticated insert on edges"
ON edges FOR INSERT
TO authenticated
WITH CHECK (true);

-- Allow authenticated users to update their own data
CREATE POLICY "Allow authenticated update on repositories"
ON repositories FOR UPDATE
TO authenticated
USING (true)
WITH CHECK (true);

CREATE POLICY "Allow authenticated update on graphs"
ON graphs FOR UPDATE
TO authenticated
USING (true)
WITH CHECK (true);

-- Allow authenticated users to delete their own data
CREATE POLICY "Allow authenticated delete on repositories"
ON repositories FOR DELETE
TO authenticated
USING (true);

-- Useful views for common queries

-- View to get graph statistics
CREATE OR REPLACE VIEW graph_statistics AS
SELECT
    r.id as repository_id,
    r.repository_path,
    r.repository_url,
    g.id as graph_id,
    g.graph_type,
    g.total_nodes,
    g.total_edges,
    g.created_at,
    r.updated_at
FROM repositories r
JOIN graphs g ON r.id = g.repository_id
ORDER BY r.updated_at DESC;

-- View to get node counts by type per graph
CREATE OR REPLACE VIEW node_type_counts AS
SELECT
    g.id as graph_id,
    r.repository_path,
    g.graph_type,
    n.node_type,
    COUNT(*) as count
FROM graphs g
JOIN repositories r ON g.repository_id = r.id
JOIN nodes n ON g.id = n.graph_id
GROUP BY g.id, r.repository_path, g.graph_type, n.node_type
ORDER BY g.id, count DESC;

-- View to get top-level nodes (nodes with no parent)
CREATE OR REPLACE VIEW top_level_nodes AS
SELECT
    g.id as graph_id,
    g.graph_type,
    n.*
FROM nodes n
JOIN graphs g ON n.graph_id = g.id
WHERE n.parent_id IS NULL OR n.parent_id = ''
ORDER BY g.id, n.name;

-- Helper function for recursive subtree queries
CREATE OR REPLACE FUNCTION get_subtree(
    p_graph_id BIGINT,
    p_root_node_id TEXT DEFAULT NULL,
    p_max_depth INTEGER DEFAULT NULL
)
RETURNS TABLE (
    id BIGINT,
    graph_id BIGINT,
    node_id TEXT,
    node_type TEXT,
    name TEXT,
    qualified_name TEXT,
    file_path TEXT,
    line_number INTEGER,
    parent_id TEXT,
    metadata JSONB,
    depth INTEGER
) AS $$
BEGIN
    RETURN QUERY
    WITH RECURSIVE subtree AS (
        -- Base case
        SELECT
            n.*,
            0 as depth
        FROM nodes n
        WHERE n.graph_id = p_graph_id
        AND (
            p_root_node_id IS NULL AND (n.parent_id IS NULL OR n.parent_id = '')
            OR n.node_id = p_root_node_id
        )

        UNION ALL

        -- Recursive case
        SELECT
            n.*,
            s.depth + 1
        FROM nodes n
        INNER JOIN subtree s ON n.parent_id = s.node_id AND n.graph_id = s.graph_id
        WHERE p_max_depth IS NULL OR s.depth < p_max_depth
    )
    SELECT * FROM subtree;
END;
$$ LANGUAGE plpgsql;

-- Helper function to get edges for a subtree
CREATE OR REPLACE FUNCTION get_subtree_edges(
    p_graph_id BIGINT,
    p_node_ids TEXT[]
)
RETURNS TABLE (
    id BIGINT,
    graph_id BIGINT,
    source_id TEXT,
    target_id TEXT,
    edge_type TEXT,
    metadata JSONB
) AS $$
BEGIN
    RETURN QUERY
    SELECT e.*
    FROM edges e
    WHERE e.graph_id = p_graph_id
    AND e.source_id = ANY(p_node_ids)
    AND e.target_id = ANY(p_node_ids);
END;
$$ LANGUAGE plpgsql;

-- Grant necessary permissions (adjust based on your needs)
-- GRANT USAGE ON SCHEMA public TO authenticated;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO authenticated;
-- GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO authenticated;