feat(db): add file storage metadata columns #128

New Issue

forbes · 2026-02-17T16:10:11Z

forbes commented

2026-02-17 16:10:11 +00:00

Summary

Add storage metadata columns to the item_files table to support dual-running MinIO and filesystem backends during migration.

Context

The existing item_files table (migration 011_item_files.sql) stores:

CREATE TABLE item_files (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
  filename TEXT NOT NULL,
  content_type TEXT NOT NULL DEFAULT 'application/octet-stream',
  size BIGINT NOT NULL DEFAULT 0,
  object_key TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The object_key column stores the MinIO key (e.g. items/{itemID}/files/{uuid}/{filename}). For the filesystem backend, we need to track which backend each file uses and the filesystem path.

Additionally, the revisions table stores file metadata in columns file_key, file_version, file_checksum, file_size — these also need a storage_backend indicator.

Requirements

Migration `017_file_storage_metadata.sql`

-- Track which storage backend holds each attached file
ALTER TABLE item_files
  ADD COLUMN IF NOT EXISTS storage_backend TEXT NOT NULL DEFAULT 'minio';

-- Track which storage backend holds each revision file
ALTER TABLE revisions
  ADD COLUMN IF NOT EXISTS file_storage_backend TEXT NOT NULL DEFAULT 'minio';

Why these columns

storage_backend on item_files: During migration, existing rows stay 'minio' while new uploads write 'filesystem'. The application reads this to know which backend to use for downloads/deletes.
file_storage_backend on revisions: Same dual-running support for revision files (uploaded via POST /api/items/{partNumber}/file).

What we do NOT need

storage_path column: The existing object_key / file_key columns already serve this purpose — the filesystem backend uses the same key as a relative path under its root directory.
content_type on item_files: Already exists.
file_size on item_files: Already exists as size.

DB repository changes

Update internal/db/item_files.go:

ItemFile struct: add StorageBackend string field
Create(): accept and insert storage_backend
ListByItem() and Get(): include storage_backend in SELECT

Update internal/db/revisions.go (or wherever Revision is defined):

Revision struct: add FileStorageBackend string field
Include in INSERT and SELECT queries

Acceptance criteria

Migration applies cleanly on existing database with data
Existing rows default to 'minio' — no data loss
ItemFile and Revision structs updated with backend field
Repository methods read/write the new columns
go build ./... passes

Priority

P0 — blocks data migration and dual-running

Part of

Storage Migration: MinIO → PostgreSQL + Filesystem

## Summary Add storage metadata columns to the `item_files` table to support dual-running MinIO and filesystem backends during migration. ## Context The existing `item_files` table (migration `011_item_files.sql`) stores: ```sql CREATE TABLE item_files ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE, filename TEXT NOT NULL, content_type TEXT NOT NULL DEFAULT 'application/octet-stream', size BIGINT NOT NULL DEFAULT 0, object_key TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); ``` The `object_key` column stores the MinIO key (e.g. `items/{itemID}/files/{uuid}/{filename}`). For the filesystem backend, we need to track which backend each file uses and the filesystem path. Additionally, the `revisions` table stores file metadata in columns `file_key`, `file_version`, `file_checksum`, `file_size` — these also need a `storage_backend` indicator. ## Requirements ### Migration `017_file_storage_metadata.sql` ```sql -- Track which storage backend holds each attached file ALTER TABLE item_files ADD COLUMN IF NOT EXISTS storage_backend TEXT NOT NULL DEFAULT 'minio'; -- Track which storage backend holds each revision file ALTER TABLE revisions ADD COLUMN IF NOT EXISTS file_storage_backend TEXT NOT NULL DEFAULT 'minio'; ``` ### Why these columns - `storage_backend` on `item_files`: During migration, existing rows stay `'minio'` while new uploads write `'filesystem'`. The application reads this to know which backend to use for downloads/deletes. - `file_storage_backend` on `revisions`: Same dual-running support for revision files (uploaded via `POST /api/items/{partNumber}/file`). ### What we do NOT need - `storage_path` column: The existing `object_key` / `file_key` columns already serve this purpose — the filesystem backend uses the same key as a relative path under its root directory. - `content_type` on `item_files`: Already exists. - `file_size` on `item_files`: Already exists as `size`. ### DB repository changes Update `internal/db/item_files.go`: - `ItemFile` struct: add `StorageBackend string` field - `Create()`: accept and insert `storage_backend` - `ListByItem()` and `Get()`: include `storage_backend` in SELECT Update `internal/db/revisions.go` (or wherever `Revision` is defined): - `Revision` struct: add `FileStorageBackend string` field - Include in INSERT and SELECT queries ## Acceptance criteria - [ ] Migration applies cleanly on existing database with data - [ ] Existing rows default to `'minio'` — no data loss - [ ] `ItemFile` and `Revision` structs updated with backend field - [ ] Repository methods read/write the new columns - [ ] `go build ./...` passes ## Priority P0 — blocks data migration and dual-running ## Part of Storage Migration: MinIO → PostgreSQL + Filesystem

forbes referenced this issue

2026-02-17 16:11:27 +00:00

feat(cli): data migration script — MinIO to filesystem #130

forbes referenced this issue

2026-02-17 16:13:12 +00:00

chore(storage): remove MinIO dependency and clean up #133

forbes referenced this issue from a commit

2026-02-17 18:30:31 +00:00

feat(db): add storage backend metadata columns

forbes referenced a pull request that will close this issue

2026-02-17 18:30:50 +00:00

feat(db): add storage backend metadata columns #135