Add Postgres for persistence and File cabinet

Migrations are local only currently, and must be run manually by setting POSTGRES_CONNECTION_URI

1 files changed, 31 insertions, 0 deletions

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