path: root/makima/migrations/20250116000000_create_repository_history.sql

-- Repository history table for storing and suggesting previously used repositories
-- Used to track repositories across contracts and provide suggestions when adding new repos

CREATE TABLE IF NOT EXISTS repository_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id UUID NOT NULL REFERENCES owners(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    repository_url VARCHAR(512),            -- For remote repos
    local_path VARCHAR(512),                -- For local repos
    source_type VARCHAR(32) NOT NULL,       -- 'remote' or 'local'
    use_count INTEGER NOT NULL DEFAULT 1,
    last_used_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for efficient owner lookups
CREATE INDEX idx_repository_history_owner_id ON repository_history(owner_id);

-- Index for filtering by source type
CREATE INDEX idx_repository_history_source_type ON repository_history(source_type);

-- Index for ordering by use_count and last_used_at
CREATE INDEX idx_repository_history_usage ON repository_history(owner_id, use_count DESC, last_used_at DESC);

-- Unique constraint per owner + source_type + (url or path)
-- This prevents duplicates while allowing the same URL to be tracked by different owners
CREATE UNIQUE INDEX idx_repository_history_unique_remote
    ON repository_history(owner_id, repository_url) WHERE source_type = 'remote' AND repository_url IS NOT NULL;

CREATE UNIQUE INDEX idx_repository_history_unique_local
    ON repository_history(owner_id, local_path) WHERE source_type = 'local' AND local_path IS NOT NULL;