geo/docs/superpowers/specs/2026-04-15-image-management-design.md

Image Management Design

Date: 2026-04-15
Status: Approved

Overview

A standalone image asset management system for the geo-rankly admin platform. Supports independent upload, folder organization, fuzzy search, rename, and deletion. Images are reusable from the article editor via a picker modal.

---

Requirements

#	Requirement
R1	Images are stored as tenant-scoped assets independent of articles
R2	Users can organize images into single-level folders
R3	All uploaded images are converted to WebP (quality=82) before storage
R4	Image names are user-editable and support fuzzy search
R5	Deletion supports reference warning + explicit force delete; final terminal state is soft-delete metadata + hard-delete OSS file
R6	Article editor gains a "select from library" picker
R7	Tenant isolation follows existing multi-tenant patterns
R8	Storage quota enforced per plan: free=100 MB, regular=1 GB, premium=2 GB
R9	Cover image picker modal: "AI封面" tab replaced with "本地素材" tab (image library)
R10	Deleting a referenced image returns a usage summary; the user may still force delete
R11	Quota enforcement is concurrency-safe on the hot path and does not rely on SUM() scans
R12	Upload/delete consistency uses pending states + event-driven compensation, not a periodic full-table sweeper

---

Data Model

image_folders

CREATE TABLE image_folders (
  id         BIGSERIAL PRIMARY KEY,
  tenant_id  BIGINT NOT NULL,
  name       VARCHAR(100) NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT uq_image_folders_tenant_name UNIQUE (tenant_id, name),
  CONSTRAINT uq_image_folders_tenant_id UNIQUE (tenant_id, id)
);

CREATE INDEX idx_image_folders_tenant ON image_folders (tenant_id);

• Single-level only (no nested folders)
• Name is unique per tenant

image_assets

CREATE TABLE image_assets (
  id           BIGSERIAL PRIMARY KEY,
  tenant_id    BIGINT NOT NULL,
  folder_id    BIGINT,
  object_key   TEXT NOT NULL UNIQUE,
  name         VARCHAR(255) NOT NULL,   -- user-editable display name
  size_bytes   BIGINT NOT NULL,         -- size after WebP conversion
  status       VARCHAR(20) NOT NULL DEFAULT 'active', -- pending_upload / active / pending_delete / deleted / upload_failed
  created_by   BIGINT NOT NULL,
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at   TIMESTAMPTZ,            -- terminal soft delete timestamp
  CONSTRAINT fk_image_assets_folder
    FOREIGN KEY (tenant_id, folder_id)
    REFERENCES image_folders(tenant_id, id),
  CONSTRAINT uq_image_assets_tenant_id UNIQUE (tenant_id, id)
);

CREATE INDEX idx_image_assets_tenant_folder ON image_assets (tenant_id, folder_id) WHERE status = 'active' AND deleted_at IS NULL;
CREATE INDEX idx_image_assets_tenant_created ON image_assets (tenant_id, created_at DESC) WHERE status = 'active' AND deleted_at IS NULL;
CREATE INDEX idx_image_assets_tenant_status ON image_assets (tenant_id, status, created_at DESC);
CREATE INDEX idx_image_assets_name_trgm ON image_assets USING GIN (name gin_trgm_ops) WHERE status = 'active' AND deleted_at IS NULL;

• content_type is always image/webp (omitted from table, implied)
• folder_id = NULL means root / uncategorized
• Active queries must enforce status = 'active' AND deleted_at IS NULL
• object_key path: tenants/{tenant_id}/images/{uuid}.webp

image_asset_references

Used only for library-backed references so delete warnings are cheap and precise.

CREATE TABLE image_asset_references (
  id             BIGSERIAL PRIMARY KEY,
  tenant_id      BIGINT NOT NULL,
  image_asset_id BIGINT NOT NULL,
  article_id     BIGINT NOT NULL,
  ref_scope      VARCHAR(20) NOT NULL, -- article_body / article_cover
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_image_asset_references_asset
    FOREIGN KEY (tenant_id, image_asset_id)
    REFERENCES image_assets(tenant_id, id)
    ON DELETE CASCADE,
  CONSTRAINT uq_image_asset_reference UNIQUE (image_asset_id, article_id, ref_scope)
);

CREATE INDEX idx_image_asset_references_asset ON image_asset_references (tenant_id, image_asset_id);
CREATE INDEX idx_image_asset_references_article ON image_asset_references (tenant_id, article_id);

• Only images inserted from the library create reference rows
• Local uploads and arbitrary external URLs do not create reference rows
• article_body refs are deduped per article, not per occurrence count

tenant_image_storage_usage

Hot-path storage usage counter. This is the authoritative used_bytes source for quota checks.

CREATE TABLE tenant_image_storage_usage (
  tenant_id   BIGINT PRIMARY KEY,
  used_bytes  BIGINT NOT NULL DEFAULT 0,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

• One row per tenant, updated atomically
• Avoids SUM(image_assets.size_bytes) on every upload
• SUM() remains a migration/backfill/reconciliation tool only

---

Storage Quota

Storage quota limit remains in the existing plans.quota_policy_json JSONB field. Hot-path usage is tracked in tenant_image_storage_usage.

Plan configuration (added key):

{ "article_generation": 100, "image_storage_bytes": 104857600 }

Plan	image_storage_bytes
Free	104,857,600 (100 MB)
Regular	1,073,741,824 (1 GB)
Premium	2,147,483,648 (2 GB)

If a legacy plan row does not yet contain image_storage_bytes, runtime fallback is 104857600 until the migration/seed backfill is applied.

Atomic quota reserve on upload:

INSERT INTO tenant_image_storage_usage (tenant_id, used_bytes)
VALUES ($1, 0)
ON CONFLICT (tenant_id) DO NOTHING;

WITH plan AS (
  SELECT COALESCE((p.quota_policy_json ->> 'image_storage_bytes')::BIGINT, 104857600) AS quota_bytes
  FROM tenant_plan_subscriptions s
  JOIN plans p ON p.id = s.plan_id
  WHERE s.tenant_id = $1
    AND s.status = 'active'
    AND s.deleted_at IS NULL
  LIMIT 1
)
UPDATE tenant_image_storage_usage u
SET used_bytes = u.used_bytes + $2,
    updated_at = NOW()
FROM plan
WHERE u.tenant_id = $1
  AND u.used_bytes + $2 <= plan.quota_bytes
RETURNING u.used_bytes, plan.quota_bytes;

If no row is returned, reject with HTTP 409 image_storage_quota_exceeded.

Quota release on rollback/delete finalization:

UPDATE tenant_image_storage_usage
SET used_bytes = GREATEST(0, used_bytes - $2),
    updated_at = NOW()
WHERE tenant_id = $1;

New API endpoint:

GET /api/tenant/images/storage-usage
→ { used_bytes, quota_bytes, used_pct }

GET /api/tenant/images/storage-usage reads:

1. used_bytes from tenant_image_storage_usage
2. quota_bytes from the active plan

Used to display the storage progress bar in the UI.

---

Image Upload Pipeline

User uploads file (PNG / JPG / GIF / WebP, ≤ 10 MB raw)
    ↓
Validate file size and MIME type
    ↓
image.DecodeConfig() with registered decoders
  - reject oversized pixel bombs before full decode
    ↓
image.Decode() → in-memory image.Image
GIF: first frame only (no animated WebP)
    ↓
webp.Encode(quality=82) → []byte
    ↓
BEGIN TX
  - reserve quota via atomic counter update
  - INSERT INTO image_assets (..., status='pending_upload')
COMMIT
    ↓
Generate object_key: tenants/{id}/images/{uuid}.webp
    ↓
ObjectStorage.PutBytes(objectKey, content, "image/webp")
    ↓
Success:
  UPDATE image_assets SET status='active', updated_at=NOW()
Failure:
  release quota + mark status='upload_failed'
    ↓
Return { id, url, name, size_bytes }

Decoder registration: _ "image/gif", _ "image/jpeg", _ "image/png", _ "golang.org/x/image/webp"

Encoder: github.com/chai2010/webp — pure Go, no CGO, quality parameter 0–100.

---

Asset State Machine & Compensation

image_assets.status is an explicit operation state machine:

• pending_upload: quota reserved, DB row committed, OSS object not yet finalized
• active: visible in list/picker/editor
• pending_delete: hidden from UI, physical delete in progress
• deleted: terminal state, deleted_at populated
• upload_failed: terminal failure state, hidden from UI

Compensation is event-driven and idempotent, using the existing RabbitMQ infrastructure. There is no periodic full-table sweeper.

Upload finalize / compensation:

1. Request thread creates pending_upload
2. Request thread attempts PutBytes
3. If request thread can finalize DB immediately, asset becomes active
4. If request thread fails after OSS success or OSS failure, it publishes an idempotent finalize_upload command with asset_id
5. Consumer behavior:
   • row pending_upload + object exists → mark active
   • row pending_upload + object missing → mark upload_failed and release reserved quota
   • row already terminal → no-op

Delete finalize / compensation:

1. Request thread first checks references
2. If references exist and force != true, return 409 with usage summary
3. If delete proceeds, mark asset pending_delete so it disappears from active lists immediately
4. Request thread attempts ObjectStorage.Delete
5. On success, finalize DB delete (status='deleted', deleted_at=NOW()) and release quota
6. If any post-step fails, publish idempotent finalize_delete command with asset_id
7. Consumer retries only that asset operation; no tenant/global scan is required

This is the same pattern used in large systems for inventory/quota and external side effects: DB state machine + atomic counter + idempotent async compensation.

---

API Design

All endpoints under /api/tenant/images, protected by existing auth middleware + tenant scope.

Folders

Method	Path	Description
GET	/api/tenant/images/folders	List all folders with image count
POST	/api/tenant/images/folders	Create folder { name }
PUT	/api/tenant/images/folders/:id	Rename folder { name }
GET	/api/tenant/images/folders/:id/delete-preview	Return image count + reference summary for folder deletion
DELETE	/api/tenant/images/folders/:id	Delete folder, supports ?force=1; contained images follow the same delete state machine

Images

Method	Path	Description
GET	/api/tenant/images	List images ?folder_id=&q=&page=&page_size=
POST	/api/tenant/images	Upload image (multipart, optional folder_id)
PUT	/api/tenant/images/:id	Update { name?, folder_id? }
GET	/api/tenant/images/:id/references	Return reference summary for delete confirmation
DELETE	/api/tenant/images/:id	Delete image, supports ?force=1

Search (?q=): uses ILIKE '%keyword%', applied across all folders (folder_id filter is ignored when q is present).

Delete image sequence:

1. Query image_asset_references
2. If references exist and force != true → return HTTP 409 image_asset_in_use
3. UPDATE image_assets SET status = 'pending_delete', updated_at = NOW() WHERE id = :id
4. ObjectStorage.Delete(objectKey) — hard delete from OSS
5. UPDATE image_assets SET status = 'deleted', deleted_at = NOW(), updated_at = NOW() WHERE id = :id
6. UPDATE tenant_image_storage_usage SET used_bytes = used_bytes - size_bytes

Delete warning payload:

{
  "code": "image_asset_in_use",
  "references": {
    "total": 3,
    "article_body": 2,
    "article_cover": 1,
    "articles": [
      { "article_id": 101, "title": "Spring Launch", "scopes": ["article_body"] },
      { "article_id": 204, "title": "Baidu Guide", "scopes": ["article_body", "article_cover"] }
    ]
  }
}

• UI shows this summary and a destructive "仍然删除" button
• If the user confirms, client retries with ?force=1
• Force delete is allowed even if it will break historical body/cover URLs

Delete folder sequence:

1. Load all active images in folder
2. Aggregate reference summary
3. If references exist and force != true → return HTTP 409 with summary
4. Mark contained assets pending_delete and set folder_id = NULL
5. Delete the folder row
6. Each image finalizes through the same ObjectStorage.Delete + DB finalize path

Article Integration Contract

To support fast reference warnings without parsing article markdown on every delete:

• Images inserted from the library carry asset_id in editor-side metadata, while still rendering with normal src
• PUT /api/tenant/articles/:id adds optional referenced_image_asset_ids: number[]
• wizard_state_json keeps cover_asset_url as today, plus optional cover_image_asset_id
• On every article save, backend upserts image_asset_references for:
  • article_body from referenced_image_asset_ids
  • article_cover from cover_image_asset_id
• Direct local upload / external URL remains supported and simply produces no library reference row

---

Backend Structure

Follows existing layered architecture in server/internal/tenant/.

server/internal/tenant/
├── app/
│   ├── image_service.go          -- ImageService (upload, list, update, delete)
│   └── image_folder_service.go   -- ImageFolderService (CRUD)
├── repository/
│   ├── image_repo.go             -- DB queries for image_assets
│   ├── image_reference_repo.go   -- DB queries for image_asset_references
│   ├── image_usage_repo.go       -- atomic quota counter updates
│   └── queries/
│       ├── image.sql             -- sqlc queries
│       ├── image_folder.sql      -- sqlc queries
│       ├── image_reference.sql   -- sqlc queries
│       └── image_usage.sql       -- sqlc queries
└── transport/
    └── image_handler.go          -- HTTP handlers for all image endpoints

ImageService accepts:

• objectstorage.Client
• the existing AssetHandler.BuildArticleImageURL() for generating signed URLs
• workspace/plan lookup for image_storage_bytes
• an image-asset operation dispatcher backed by the existing RabbitMQ infrastructure for finalize/compensation commands

No new external infrastructure is introduced.

---

Migration

New migration file: 20260415xxxxxx_create_image_tables.up.sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE image_folders ( ... );
CREATE TABLE image_assets ( ... );
CREATE TABLE image_asset_references ( ... );
CREATE TABLE tenant_image_storage_usage ( ... );
-- indexes as above

UPDATE plans
SET quota_policy_json = quota_policy_json ||
  CASE plan_code
    WHEN 'free' THEN '{"image_storage_bytes":104857600}'::jsonb
    WHEN 'regular' THEN '{"image_storage_bytes":1073741824}'::jsonb
    WHEN 'premium' THEN '{"image_storage_bytes":2147483648}'::jsonb
    ELSE '{}'::jsonb
  END
WHERE deleted_at IS NULL;

Existing article images (path tenants/{id}/articles/{id}/images/...) are not migrated. They continue to be served via the existing /api/public/assets/:token mechanism unchanged.

Seed data must also be updated so the existing default free plan includes image_storage_bytes = 104857600.

---

Frontend

New Page: /images

• Route added to router/index.ts under the "内容管理" nav group (alongside Knowledge)
• Nav label: nav.images (i18n key)

Layout:

┌─────────────────────────────────────────────────────┐
│  图片管理                          [+ 上传图片]      │
├──────────────┬──────────────────────────────────────┤
│ 文件夹  [+]  │  🔍 搜索图片名...                    │
│──────────────│──────────────────────────────────────│
│ 📁 未分类(12)│  ┌────┐ ┌────┐ ┌────┐ ┌────┐        │
│ 📁 产品图(8) │  │img │ │img │ │img │ │img │        │
│ 📁 博客配图  │  └────┘ └────┘ └────┘ └────┘        │
│              │                                       │
│              │  文件名 · 大小 · 日期 [改名] [移动]  │
└──────────────┴──────────────────────────────────────┘

• "文件夹" + [+] on the same row, [+] right-aligned
• Search input at top of grid area, 300ms debounce
• When ?q= is active: folder sidebar is disabled, search results shown across all folders
• Image grid: fixed 4-column grid with lazy loading
• Hover on image card: show [改名] [移动] [删除] action buttons
• Clicking [删除] first requests /api/tenant/images/:id/references; if references exist, show a warning modal with article titles/counts and a destructive "仍然删除" action

Article Editor Picker

• Existing toolbar image button gets a dropdown: "本地上传" | "从图片库选择"
• "从图片库选择" opens a Modal containing the image list (folder filter + search), click to insert
• Library insert stores { src, asset_id } in editor-side metadata; plain local upload remains { src }

Cover Image Modal Integration

The existing "选择封面图" modal has two tabs: "正文/本地上传" and "AI封面".

Change: Replace the "AI封面" tab with "本地素材" tab.

┌─────────────────────────────────────────┐
│ 选择封面图                           ✕  │
├─────────────────────────────────────────┤
│ [正文/本地上传]  [本地素材]             │  ← "AI封面" → "本地素材"
├─────────────────────────────────────────┤
│ 🔍 搜索...          文件夹: [全部 ▾]   │
│                                         │
│ ┌────┐ ┌────┐ ┌────┐ ┌────┐            │
│ │img │ │img │ │img │ │img │            │
│ └────┘ └────┘ └────┘ └────┘            │
│                                         │
│              [取消]  [确认]             │
└─────────────────────────────────────────┘

• "本地素材" tab reuses the same image list API (GET /api/tenant/images)
• Single-select mode: clicking an image highlights it
• If current primary platform requires crop (currently Baijiahao only), selecting a library image routes into the existing crop workspace before confirmation
• If no crop is required, "确认" uses the original library image URL directly and stores cover_image_asset_id
• Folder dropdown filter + search input for discovery
• Storage usage bar shown at bottom of the tab
• Result:
  • Baijiahao: library image acts as crop source, final cover is the cropped export
  • Other platforms: use original library asset without re-upload/re-encode

---

Constraints & Decisions

Decision	Rationale
Single-level folders	Avoids recursive tree complexity; sufficient for this use case
WebP quality=82	Good perceptual quality, ~30–50% smaller than JPEG at equivalent quality
GIF → static WebP (first frame)	Animated WebP adds significant complexity, deferred
Force delete is allowed after reference warning	Users may intentionally trade historical render integrity for storage recovery
image_asset_references is explicit	Delete warning stays cheap and precise; no markdown scan on delete
Atomic tenant counter for quota	High-concurrency safe and O(1) on hot path
Pending states + MQ compensation	External side effects stay recoverable without a periodic full-table scan
Delete hides first, finalizes second	UI reacts immediately while compensation remains idempotent
Baijiahao only enters cropper	Preserves current mandatory 16:9 cover behavior without adding unnecessary steps for other platforms
WebP decode uses golang.org/x/image/webp	Standard, simple, no CGO, works with image.Decode() after decoder registration
Tenant-aware composite foreign keys	DB enforces folder/image tenant isolation without adding a complex model
ILIKE for search	Dataset is tenant-scoped and small; trigram index added as optimization
No historical image migration	Avoids risky bulk migration; old paths still served correctly