agatha
61d923d5be
Short IDs become the canonical identifier in URLs (/i/:short_id), MinIO/R2 storage keys, and all API responses. Hash-based deduplication is preserved. Includes two-phase Alembic migration (003 adds nullable column, 004 enforces NOT NULL) with a backfill script to copy storage objects and populate short_id for existing images. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
78 lines
3.2 KiB
Markdown
78 lines
3.2 KiB
Markdown
# Data Model: Short Image IDs
|
|
|
|
## Changed Entity: Image
|
|
|
|
### New Column
|
|
|
|
| Column | Type | Constraints | Notes |
|
|
|------------|--------------|------------------------------|-------------------------------------------|
|
|
| `short_id` | VARCHAR(8) | UNIQUE, NOT NULL (post-migration), INDEX | Base62 alphanumeric, 8 characters |
|
|
|
|
### Updated Columns (values change, types unchanged)
|
|
|
|
| Column | Old values | New values |
|
|
|-----------------|-----------------------------------------|-----------------------------------|
|
|
| `storage_key` | SHA-256 hash (64 hex chars) | short_id (8 base62 chars) |
|
|
| `thumbnail_key` | `{hash}-thumb` (69 chars) | `{short_id}-thumb` (13 chars) |
|
|
|
|
### Unchanged Columns
|
|
|
|
| Column | Notes |
|
|
|------------|-----------------------------------------------------------------------|
|
|
| `id` | UUID primary key — unchanged, retained as internal identifier |
|
|
| `hash` | SHA-256 content hash — unchanged, still used for deduplication |
|
|
| `filename` | Unchanged |
|
|
| `mime_type`| Unchanged |
|
|
| `size_bytes`, `width`, `height` | Unchanged |
|
|
| `created_at` | Unchanged |
|
|
|
|
### Validation Rules
|
|
|
|
- `short_id`: exactly 8 characters, matching `[a-zA-Z0-9]{8}` — generated on insert, never updated
|
|
- `short_id` must be unique across all image records
|
|
- On collision (rare), a new value is generated and retried (up to 10 attempts)
|
|
|
|
---
|
|
|
|
## Alembic Migrations
|
|
|
|
### Migration 003 — Add `short_id` column (nullable)
|
|
|
|
```
|
|
ALTER TABLE images ADD COLUMN short_id VARCHAR(8) NULL;
|
|
CREATE UNIQUE INDEX ix_images_short_id ON images (short_id);
|
|
```
|
|
|
|
Run immediately on deploy. Existing rows get `short_id = NULL`. New uploads will populate `short_id` on insert (application-level).
|
|
|
|
### Migration Script — Backfill existing rows
|
|
|
|
`api/scripts/migrate_to_short_ids.py`
|
|
|
|
For each image where `short_id IS NULL`:
|
|
1. Generate 8-char base62 short_id (retry on collision)
|
|
2. Copy storage object: `{hash}` → `{short_id}` (S3 copy)
|
|
3. Copy thumbnail if present: `{hash}-thumb` → `{short_id}-thumb`
|
|
4. Verify new objects exist (S3 head_object)
|
|
5. Update DB row: `short_id = {short_id}`, `storage_key = {short_id}`, `thumbnail_key = {short_id}-thumb` (or NULL)
|
|
6. Delete old storage objects
|
|
|
|
### Migration 004 — Add NOT NULL constraint
|
|
|
|
```
|
|
ALTER TABLE images ALTER COLUMN short_id SET NOT NULL;
|
|
```
|
|
|
|
Run only after the migration script completes successfully with zero `short_id IS NULL` rows remaining.
|
|
|
|
---
|
|
|
|
## Storage Object Naming Convention
|
|
|
|
| Object type | Key pattern | Example |
|
|
|-------------|---------------------|-------------------|
|
|
| Original | `{short_id}` | `xK7mN2pQ` |
|
|
| Thumbnail | `{short_id}-thumb` | `xK7mN2pQ-thumb` |
|
|
|
|
No folder structure. Flat bucket layout (unchanged from current convention).
|