1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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();
|
