# 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