1 files changed, 25 insertions, 0 deletions
diff --git a/internal/db/migrations/blog/00003_files.sql b/internal/db/migrations/blog/00003_files.sql
new file mode 100644
index 0000000..2ed8820
--- /dev/null
+++ b/internal/db/migrations/blog/00003_files.sql
@@ -0,0 +1,25 @@
+-- +goose Up
+-- The image library becomes a file library: any type is accepted and files are
+-- served in slices (substring), so a download never loads the whole blob.
+ALTER TABLE images RENAME TO files;
+ALTER TABLE files RENAME CONSTRAINT images_pkey TO files_pkey;
+ALTER INDEX images_created RENAME TO files_created;
+-- Out of line and uncompressed, so substring() reads only the chunks it needs
+-- (uploads are mostly jpeg/zip/pdf, which pglz would not shrink anyway).
+ALTER TABLE files ALTER COLUMN data SET STORAGE EXTERNAL;
+ALTER TABLE files
+    ADD COLUMN kind text NOT NULL DEFAULT 'image'
+        CHECK (kind IN ('image', 'document', 'audio', 'video', 'archive', 'other')),
+    ALTER COLUMN size TYPE bigint;
+ALTER TABLE files ALTER COLUMN kind DROP DEFAULT; -- everything so far was an image
+CREATE INDEX files_kind_created ON files (kind, created_at DESC);
+-- SET STORAGE only applies to values written from now on; re-store the existing
+-- rows (`data = data` would keep the old TOAST pointer, the concatenation does not).
+UPDATE files SET data = data || ''::bytea;
+
+-- +goose Down
+DROP INDEX files_kind_created;
+ALTER TABLE files DROP COLUMN kind, ALTER COLUMN size TYPE integer, ALTER COLUMN data SET STORAGE EXTENDED;
+ALTER INDEX files_created RENAME TO images_created;
+ALTER TABLE files RENAME CONSTRAINT files_pkey TO images_pkey;
+ALTER TABLE files RENAME TO images;