From a32dc56d2e5447ef8988cb98b8686476cc94e70c Mon Sep 17 00:00:00 2001 From: soryu Date: Tue, 23 Dec 2025 02:14:58 +0000 Subject: Add Postgres for persistence and File cabinet Migrations are local only currently, and must be run manually by setting POSTGRES_CONNECTION_URI --- .../20241222000000_create_files_table.sql | 31 ++++++++++++++++++++++ 1 file changed, 31 insertions(+) create mode 100644 makima/migrations/20241222000000_create_files_table.sql (limited to 'makima/migrations') diff --git a/makima/migrations/20241222000000_create_files_table.sql b/makima/migrations/20241222000000_create_files_table.sql new file mode 100644 index 0000000..cf6f76c --- /dev/null +++ b/makima/migrations/20241222000000_create_files_table.sql @@ -0,0 +1,31 @@ +-- Create files table for storing transcription records +CREATE TABLE IF NOT EXISTS files ( + id UUID PRIMARY KEY DEFAULT gen_random_uuid(), + owner_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000002', + name VARCHAR(255) NOT NULL, + description TEXT, + transcript JSONB NOT NULL DEFAULT '[]'::jsonb, + location VARCHAR(512), + created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), + updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() +); + +-- Create index on owner_id for efficient filtering +CREATE INDEX idx_files_owner_id ON files(owner_id); + +-- Create index on created_at for sorting +CREATE INDEX idx_files_created_at ON files(created_at DESC); + +-- Create trigger to auto-update updated_at +CREATE OR REPLACE FUNCTION update_updated_at_column() +RETURNS TRIGGER AS $$ +BEGIN + NEW.updated_at = NOW(); + RETURN NEW; +END; +$$ language 'plpgsql'; + +CREATE TRIGGER update_files_updated_at + BEFORE UPDATE ON files + FOR EACH ROW + EXECUTE FUNCTION update_updated_at_column(); -- cgit v1.2.3