Data Model: Reaction Image Board v1

Date: 2026-05-02

Entities

Image

Represents a single uploaded image file.

Field	Type	Constraints	Notes
`id`	UUID	PK, not null	Generated on insert
`hash`	VARCHAR(64)	UNIQUE, not null	SHA-256 hex digest of file bytes
`filename`	VARCHAR	not null	Original filename; display only
`mime_type`	VARCHAR(20)	not null	`image/jpeg`, `image/png`, `image/gif`, `image/webp`
`size_bytes`	BIGINT	not null, > 0	File size
`width`	INTEGER	not null, > 0	Pixel width
`height`	INTEGER	not null, > 0	Pixel height
`storage_key`	VARCHAR(64)	not null	S3 object key; equals `hash` in v1
`created_at`	TIMESTAMPTZ	not null, default now()	Set on insert; never updated

Indexes:

images_hash_idx UNIQUE on hash — supports fast duplicate detection

Validation rules:

mime_type MUST be one of: image/jpeg, image/png, image/gif, image/webp
size_bytes MUST be > 0 and ≤ MAX_UPLOAD_BYTES (default 52 428 800)
hash MUST be a 64-character lowercase hex string (SHA-256)

Tag

Represents a single normalised tag string.

Field	Type	Constraints	Notes
`id`	UUID	PK, not null	Generated on insert
`name`	VARCHAR(64)	UNIQUE, not null	Normalised: lowercase, trimmed
`created_at`	TIMESTAMPTZ	not null, default now()	Set on insert

Indexes:

tags_name_idx UNIQUE on name — supports upsert by name
tags_name_prefix_idx on name with varchar_pattern_ops — supports prefix search (LIKE 'prefix%')

Validation rules:

name MUST match ^[a-z0-9_-]{1,64}$ (after normalisation)
Normalisation applied before validation: lowercase + whitespace trim

Lifecycle:

Tags are created implicitly on first use; no explicit creation endpoint
Tag records are never deleted even when all image associations are removed

ImageTag (join)

Many-to-many association between Image and Tag.

Field	Type	Constraints	Notes
`image_id`	UUID	FK → images.id ON DELETE CASCADE
`tag_id`	UUID	FK → tags.id ON DELETE RESTRICT

Primary key: composite (image_id, tag_id)

Notes:

Deleting an image cascades to all its ImageTag rows
Deleting a tag is RESTRICT (not permitted while image associations exist)
In practice, tags are never deleted in v1 so RESTRICT is never triggered

Relationships

Image ──< ImageTag >── Tag
  (1)         (M:M)     (1)

One Image has zero or more Tags (through ImageTag)
One Tag is applied to zero or more Images (through ImageTag)

State Transitions

Image lifecycle

[upload received]
      │
      ▼
  Validate MIME + size
      │
  Compute SHA-256
      │
  Existing hash? ──yes──► return existing record (duplicate: true)
      │
      no
      │
  Write to S3
      │
  Insert images row
      │
  Upsert tags + insert image_tag rows
      │
  Return new record (duplicate: false)
      │
  [user deletes image]
      │
  Delete image_tag rows (cascade)
  Delete images row
  Delete S3 object
      │
  [gone]

Tag set update (PATCH)

[PATCH /api/v1/images/{id}/tags with new_tags=[...]]
      │
  Validate each tag name (post-normalisation)
      │
  Fetch current tag set for image
      │
  Compute removed = current \ new_tags
  Compute added   = new_tags \ current
      │
  Delete ImageTag rows for removed tags
  Upsert Tag records for added tags
  Insert ImageTag rows for added tags
      │
  Return full updated image record

Database Migration Strategy

Alembic manages all schema changes
Migration files are committed to api/alembic/versions/
Schema is applied on API startup (alembic upgrade head)
M0: initial empty migration (no tables)
M1: images table
M2: tags table + image_tags table + indexes

3.9 KiB Raw Permalink Blame History