Add versioning to files

1 files changed, 49 insertions, 0 deletions

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();