-- Add version column for optimistic locking
ALTER TABLE files ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

-- Create trigger function to increment version on update
CREATE OR REPLACE FUNCTION increment_file_version()
RETURNS TRIGGER AS $$
BEGIN
    NEW.version = OLD.version + 1;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create trigger that fires before each update
CREATE TRIGGER increment_files_version
    BEFORE UPDATE ON files
    FOR EACH ROW
    EXECUTE FUNCTION increment_file_version();