From 2faba0388f93d8e4fb86219eba7883b331d501ff Mon Sep 17 00:00:00 2001
From: soryu <soryu@soryu.co>
Date: Wed, 24 Dec 2025 05:45:22 +0000
Subject: Add versioning to files

---
 .../20241225000000_add_file_versions.sql           | 49 ++++++++++++++++++++++
 1 file changed, 49 insertions(+)
 create mode 100644 makima/migrations/20241225000000_add_file_versions.sql

(limited to 'makima/migrations/20241225000000_add_file_versions.sql')

diff --git a/makima/migrations/20241225000000_add_file_versions.sql b/makima/migrations/20241225000000_add_file_versions.sql
new file mode 100644
index 0000000..5074eaa
--- /dev/null
+++ b/makima/migrations/20241225000000_add_file_versions.sql
@@ -0,0 +1,49 @@
+-- Create file_versions table to store version history
+CREATE TABLE IF NOT EXISTS file_versions (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    file_id UUID NOT NULL REFERENCES files(id) ON DELETE CASCADE,
+    version INTEGER NOT NULL,
+    name VARCHAR(255) NOT NULL,
+    description TEXT,
+    summary TEXT,
+    body JSONB NOT NULL DEFAULT '[]'::jsonb,
+    source VARCHAR(32) NOT NULL DEFAULT 'user', -- 'user', 'llm', or 'system'
+    change_description TEXT,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+
+    -- Each file can only have one record per version number
+    UNIQUE(file_id, version)
+);
+
+-- Index for efficient version lookups
+CREATE INDEX idx_file_versions_file_id ON file_versions(file_id);
+CREATE INDEX idx_file_versions_file_version ON file_versions(file_id, version DESC);
+
+-- Function to save a version snapshot before file updates
+CREATE OR REPLACE FUNCTION save_file_version()
+RETURNS TRIGGER AS $$
+BEGIN
+    -- Save the current state as a version before the update
+    INSERT INTO file_versions (file_id, version, name, description, summary, body, source, change_description, created_at)
+    VALUES (
+        OLD.id,
+        OLD.version,
+        OLD.name,
+        OLD.description,
+        OLD.summary,
+        OLD.body,
+        COALESCE(current_setting('app.version_source', true), 'user'),
+        current_setting('app.change_description', true),
+        OLD.updated_at
+    )
+    ON CONFLICT (file_id, version) DO NOTHING;
+
+    RETURN NEW;
+END;
+$$ language 'plpgsql';
+
+-- Trigger to automatically save versions on update
+CREATE TRIGGER save_file_version_trigger
+    BEFORE UPDATE ON files
+    FOR EACH ROW
+    EXECUTE FUNCTION save_file_version();
-- 
cgit v1.2.3