1 files changed, 31 insertions, 0 deletions
diff --git a/makima/migrations/20250116000000_create_repository_history.sql b/makima/migrations/20250116000000_create_repository_history.sql
new file mode 100644
index 0000000..7fb6920
--- /dev/null
+++ b/makima/migrations/20250116000000_create_repository_history.sql
@@ -0,0 +1,31 @@
+-- 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;