3c: Attachments DB table + disk storage

[ajianaz]

Parent: #233 · Blocked by: #244

Scope

Database schema and disk storage for attachment binary data. Attachments are linked to events (an event can have 0..N attachments).

Design Decision: Hybrid storage

Why not store binary in DB? SQLite/Postgres handle blobs poorly at scale. Attachments can be up to 10MB — storing thousands in DB bloats it and hurts backup/migration.

Strategy: DB stores metadata only; binary data goes to filesystem. Disk path is derived from attachment UUID for O(1) lookup without directory traversal.

DB Table: attachments

Column	Type	Notes
id	TEXT PK	UUID v4
event_id	TEXT FK→events	CASCADE delete
project_id	TEXT FK→projects
filename	TEXT NOT NULL	original filename
content_type	TEXT	"image/png", "text/plain", null
attachment_type	TEXT	"event.attachment" (default), "minidump", etc.
size_bytes	INTEGER NOT NULL	for display + quota
disk_path	TEXT NOT NULL	relative path under storage dir
created_at	TEXT NOT NULL

Indexes: (event_id), (project_id)

Disk Layout

data/attachments/
  {project_id}/
    {attachment_id[:2]}/
      {attachment_id}        ← full binary file

Example: data/attachments/proj_abc/ab/cdef1234...full_uuid

Why nested dirs? Prevents single-directory with 100K+ files (ext4 inode limits).

API Endpoints

• GET /api/events/:event_id/attachments — list metadata
• GET /api/attachments/:id/download — serve binary (Content-Disposition)
• DELETE /api/attachments/:id — remove DB row + disk file

Cleanup Policy

• Attachments auto-deleted when parent event is deleted (CASCADE)
• Optional: attachment_ttl_days config (default: 90 days) → cron cleanup
• Max storage per project: configurable quota

Tasks

• SQLite migration: 005_attachments.sql
• Postgres migration: same
• Insert attachment metadata on envelope ingest
• Write binary to disk using nested directory layout
• GET endpoint: list attachments for an event
• GET endpoint: download single attachment (stream from disk)
• DELETE endpoint: remove DB + disk file
• Test: upload attachment via envelope → verify DB + disk file exist
• Test: download attachment → matches original bytes
• Test: delete event → attachments cascade deleted (DB + disk)

Effort: ~2.5 hours