Outlands SkinForge

Outlands SkinForge — Product Specification #

A web tool for designing, previewing and sharing UO Outlands character skins.

Outlands SkinForge lets a UO Outlands player pick a body, a skin hue, and a cosmetic item and hue for every paperdoll slot, see the result two ways — a live-composited paperdoll and per-slot static previews — and share the exact combination through a permanent link that carries its own social preview image. Behind the public site sits a staff-only pipeline that discovers and extracts art and hue data from the operator's own copy of the game client, an admin console for reviewing and publishing those assets, and server-side hosting of every rendered image. There are no user accounts, no adverts, and no reason for a player to point a game directory at anything.

This document is written to be executed cold by an AI coding agent or a development team. Every decision it needs has been made. Each concern has exactly one owning section; every other section refers to that section by number rather than restating it.

Table of Contents #

• 1. Before You Start — Customization Decisions
• 1. Project Overview & Vision
• 1. Domain Primer — UO Outlands Paperdolls, Layers, Hues & Skins
• 1. Technology Stack & Architecture
• 1. Repository Layout, Conventions & Coding Standards
• 1. Data Model & Database Schema
• 1. Asset Discovery & Extraction Pipeline
• 1. Rendering Engine — Hue Math, Compositing & Encoding
• 1. Asset Ingestion, Versioning & Patch Re-Sync
• 1. Public Web Application — Information Architecture & Routing
• 1. Designer UI — Components, State & Interaction
• 1. Preview Surfaces — Live Paperdoll & Per-Slot Static Previews
• 1. Permalinks & Design Encoding
• 1. Open Graph Image Generation
• 1. Catalog Browse, Search & Filtering
• 1. Public HTTP API
• 1. Admin Console
• 1. Design System, Visual Language & Accessibility
• 1. Performance, Caching & CDN Strategy
• 1. Security, Privacy & Legal Posture
• 1. Observability — Logging, Metrics & Analytics
• 1. Testing Strategy & Quality Gates
• 1. Deployment, Infrastructure & Operations
• 1. Configuration & Environment Variables
• 1. Error Catalog & Edge Case Register
• 1. Milestones & Execution Plan
• 1. Executor Instructions
• 1. Appendices

1. Before You Start — Customization Decisions #

1.1 Purpose #

This section lists every point in this specification where the executing agent may substitute its own value for a working default without breaking the rest of the document. Every other section is written as if the defaults below are already chosen. Nothing downstream is blocked on a decision: if a row is left untouched, the build proceeds exactly as specified elsewhere. If a row is changed, the "Blast radius" column names every section that must be re-read and, where applicable, every test suite in Section 22 that must be re-run before the build is considered done.

No decision in this document is deferred to "later," left as an open question, or handed back to a human for clarification. Every choice below already has a working default. Sections 2 through 28 are written against these defaults and never repeat the phrase "the customer should decide" — that phrasing does not appear anywhere in this specification outside this sentence.

1.2 Decision table #

1.3 Statement of completeness #

Every default in Section 1.2 is already applied throughout Sections 2–28. The executing agent may build the entire product by implementing this document literally, changing nothing. No decision in this table blocks any other section: every working default is self-sufficient, every env var referenced has a default value in Section 24, and every schema referenced in the "Used in" column already supports both the default and the commonly anticipated alternative (for example, D-02's storage driver and D-05's curation method).

1.4 How to change a default safely #

Follow this procedure for any row in Section 1.2 the executor wants to change:

1. Locate the owning sections. Read every section number listed in that row's "Used in" column, in order, in full.
2. Change the value at its single source of truth. Most defaults resolve to exactly one of: an environment variable default in Section 24, a constant in core/config.ts (Section 5.1), or a row in a lookup table seeded by a migration (Section 6). Change it in that one place. Do not duplicate the value elsewhere — every other section that mentions the concept references the owning section instead of restating the value, so there is nothing else to edit.
3. Re-run the affected automated checks. Cross-reference the section numbers touched against Section 22's test map:
   • Changing D-01, D-02, D-10, or D-11 (infrastructure/storage) → re-run the storage and deployment integration suite (Section 22.4) and the backup/restore drill (Section 23.6).
   • Changing D-06 or D-07 (render scales/formats) → re-run the golden-image tests (Section 22.3) at every scale/format combination now offered.
   • Changing D-13 (rate limits) → re-run the rate-limit contract tests (Section 22.4) and confirm the SF-3002 error responses in Section 25 still match the new thresholds in any updated fixtures.
   • Changing D-05 or D-21 (curation/seed data) → re-run deno task db:fixtures and the catalog integration tests (Section 22.4).
   • Changing D-14, D-15, D-16, D-17 (brand/visual/locale) → re-run the accessibility checks (Section 22.6) and confirm core/i18n/en.ts still resolves every key used by the changed components.
4. Update the changelog. Record the change in the developer's own CHANGELOG.md per the Conventional Commits convention in Section 5.3; this document itself is not versioned per-decision.
5. Do not introduce a second default. If a decision needs a per-environment override (for example, a different rate limit in staging versus production), express it as a different value for the same environment variable in each environment's .env file (Section 24), never as new code branching on environment name inside core/.

1.5 Decisions explicitly not offered #

The following are constraints from the hard scope boundaries and are not customization points. They are listed here once so the executor does not mistake their absence from Section 1.2 for an oversight: elf or gargoyle bodies, animated or multi-directional previews, user accounts or login, a full equipment/armor catalog, in-game account integration, native mobile apps, advertising, and paid tiers. Each is covered as an explicit non-goal in Section 2.5 and, where relevant, as a boundary check in Section 25's edge-case register.

1.6 How decisions interact #

Most rows in Section 1.2 are independent: changing one has no effect on any other row's default. A small number interact, and the executor should read the related row before changing either one:

• D-01 (deployment target) and D-02 (storage driver). The fs storage driver assumes the application container and its storage volume are co-located, which is only guaranteed under the single-VPS default in D-01. Moving to a multi-host deployment without also switching D-02 to s3 will leave later web process replicas unable to see renders written by earlier ones; Section 4.8 states this dependency as part of the horizontal-scaling path.
• D-06 (render scales) and D-13 (rate limits). Offering more render scales increases the CPU cost of a cache-miss burst (Section 19.5); if D-06 is widened beyond 1, 2, 3, re-check the render rate limit in D-13 against the new worst-case cost per unique design rather than assuming the existing number still protects the single-VPS CPU budget in Section 4.10.
• D-07 (default format) and D-19 (analytics). These are independent in mechanism but both feed the same performance metric in Section 2.6 (time to first preview); changing D-07 to png as the default changes the baseline that metric is measured against, so the target number in Section 2.6 should be re-validated, not just the code path.
• D-10 (CDN) and D-11 (backup retention). A CDN in front of the origin reduces load on the object storage backing D-02, which in turn changes how urgently a storage restore (Section 23.6) needs to complete during an incident, because cached copies at the CDN edge continue serving reads while the origin recovers. This does not change the backup retention numbers themselves, only the operational urgency of a restore, which is documented in Section 23.6's runbook rather than in this table.
• D-08 (admin user count) and D-09 (TOTP enforcement). These are listed as separate rows because one is adjustable and one is not, but they are provisioned together: every admin user created via the CLI (Section 17.2), regardless of how many there are per D-08, is created with mandatory TOTP per D-09 — there is no code path that creates an admin account without it.
• D-21 (seed data size) and D-05 (curation policy). Local development seed data always uses the manual curation method for its seeded skin hue group members (Section 6), regardless of which method D-05 designates for production, so that deno task db:seed never depends on the heuristic classifier being present or tuned.

No other rows in Section 1.2 have a dependency relationship; every remaining row may be changed in isolation following the procedure in Section 1.4.

1.7 Quick reference by concern #

For an executor who wants to find every customization point touching one area of the system without re-reading the full table in Section 1.2, the rows group as follows:

This grouping is a navigation aid only; the authoritative default, usage, and blast-radius for every row remains the table in Section 1.2.

1.8 What "no decision blocks the build" means in practice #

An executor reading this specification top to bottom, applying every default in Section 1.2 without changing a single value, produces a complete, deployable, correctly functioning product. This is verified structurally: every default resolves to a concrete value already written into its owning section (an env var default in Section 24, a schema default in Section 6, a constant in Section 5.1), never to a placeholder, a range without a chosen point, or a cross-reference back to this section asking the executor to pick. Section 1 is read exactly once, at the start of the build, specifically so the executor can decide up front whether any default needs to change before writing the first line of code — not because any later section is incomplete without that decision.

2. Project Overview & Vision #

2.1 The problem, in the players' words #

UO Outlands sells cosmetic customizations — skin hues, hair and beard styles, tattoos, backpack dyes, and clothing dyes — through in-game currency (doubloons) and cosmetic vendors (the Wig Stand and related NPCs). A player deciding whether to spend that currency faces three concrete, recurring frustrations:

• Buying blind. The in-game purchase flow shows a swatch or a name, not the player's own character wearing the result. A player cannot see "my human female in hue 1402 skin with hair style X in hue 1102" before spending currency that, once spent, is not refunded.
• No way to compare combinations. A player who owns three hair styles and is considering a fourth cannot see all four side by side on their own body shape without changing their live character repeatedly and taking screenshots by hand, then flipping between image files.
• No way to share a look. When a player finds a combination they like, or wants a second opinion from a guildmate before spending doubloons, the only sharing mechanism is a screenshot posted to a chat channel or forum — lossy, hard to reproduce exactly, and impossible for the recipient to open and remix into their own variant.

Quantified in gameplay terms: a full cosmetic wardrobe covers 18 choosable slots (Section 3.3) each with potentially dozens of asset choices and thousands of possible hues (Section 3.6). The combinatorial space a player might want to preview before buying is in the hundreds of thousands of distinct looks. No in-game UI attempts to browse that space; the game's own interface is built for selecting one item at a time from a vendor list, not for comparison shopping.

2.2 What SkinForge is #

Outlands SkinForge is a free, public, no-login web tool that lets a UO Outlands player assemble a character's cosmetic appearance — body, skin hue, and up to 18 choosable hueable slots — see the result as both a live composited paperdoll and individual per-slot preview images, and generate a permanent, shareable link that reproduces that exact combination for anyone who opens it, complete with a social preview image for chat and forum links.

Capabilities:

• A designer interface for choosing a body (male/female), a skin hue, and an asset plus hue for each of the 18 choosable cosmetic slots (Section 11).
• A live client-side composited paperdoll preview that updates instantly as choices change (Section 12).
• Static per-slot preview images showing each chosen asset in isolation (Section 12).
• A permanent, immutable link per exact combination, with no account required to create or open one (Section 13).
• An automatically generated Open Graph preview image for every permalink, so links posted in Discord or forums show the character, not a blank card (Section 14).
• A browsable catalog of every available asset and hue, searchable and filterable, for players who want to explore before designing (Section 15).
• A staff-only pipeline that discovers, extracts, and classifies art and hue data from the operator's own local copy of the game client files (Section 7), and an admin console to review, approve, and publish that data, including re-import after game patches (Sections 9, 17).
• A single optional donation link; no ads, no paid tiers, no purchases of any kind inside the tool itself (Section 2.6, Section 1 D-04).

2.3 Personas #

2.4 Primary user journeys #

Journey A — Design a look from scratch.

1. Visitor opens / (Section 10). No login prompt, no interstitial.
2. Visitor picks body (male/female) and a skin hue from a swatch picker (Section 11.6).
3. Visitor opens a slot (e.g. Hair), browses available assets for that slot and gender, picks one, then picks a hue for it (Section 11.6). The live paperdoll preview updates immediately (Section 12.2), composited entirely in the browser.
4. Visitor repeats step 3 for any of the other 17 choosable slots, in any order, any number of times.
5. Visitor clicks Share. The current combination is canonicalized, hashed, and persisted server-side (Section 13); the browser navigates to the resulting permalink URL, /d/:code.
6. Visitor copies the URL or uses a native share sheet; the page itself already carries Open Graph tags pointing at the generated preview image (Section 14).

Journey B — Open a shared permalink and remix it.

1. Recipient clicks a /d/:code link shared in chat. The link unfurls with a preview image and title before it is even opened, because the OG image and tags are pre-generated (Section 14).
2. The page server-renders the full paperdoll composite immediately, so the look is visible even before any client-side JavaScript loads (Section 8.10, Section 10.3).
3. Recipient clicks "Open in Designer." The permalink's design JSON (Section 13) hydrates the designer island's state; the recipient is now editing a copy, never the original — the original permalink is immutable (Section 13.7).
4. Recipient changes one or more slots and shares a new permalink of their own.

Journey C — Browse the catalog, then jump into the designer.

1. Visitor opens /catalog (Section 15) and filters by slot, then by gender, then by hue group.
2. Visitor finds an asset they like, opens its detail view showing it on both bodies across a spread of hues.
3. Visitor clicks "Try this in the designer," which opens the designer with that slot pre-filled and every other slot at its default empty state.
4. Visitor continues as in Journey A from step 3.

Journey D — Staff import after a game patch.

1. Outlands ships a client patch. Staff curator runs the discovery stage of the CLI (Section 7, Stage D1) against their local, updated client directory.
2. The CLI reports newly discovered or changed source files and stages an import run (Section 9.1).
3. Staff curator reviews extraction candidates in the admin console (Section 17.6): new assets, changed hues, anything flagged needs_operator_input (Section 7's degrade-gracefully rule).
4. Staff curator approves the candidates that are correct, corrects classification for anything ambiguous, and publishes the new build (Section 9.5).
5. Every existing permalink continues to render exactly as before, because designs pin a build_id (Section 13.7) and retired assets stay renderable (Section 6's soft-delete rule); only new designs or edits can pick up the newly published assets.

2.5 Goals and explicit non-goals #

Goals:

• Let any UO Outlands player preview any combination of the two bodies, one skin hue, and the 18 choosable cosmetic slots, each with its own hue, before spending in-game currency.
• Produce a permanent, exact, remixable record of any combination, shareable as a single URL.
• Make that URL look good when unfurled in chat apps and forums, with zero manual effort from the player.
• Give staff a repeatable, low-effort process for keeping the catalog in sync with the live game client after patches, without ever breaking an already-shared link.
• Stay free, ad-free, and account-free indefinitely, funded only by an optional donation link.

Non-goals (explicit, matching Section 1.5's decisions-not-offered list exactly):

• Elf or gargoyle bodies. UO Outlands has no elf or gargoyle player bodies; this is not a limitation of SkinForge, it is a fact about the game.
• Animated previews or multiple facing directions. The paperdoll is a single static pose; SkinForge ships exactly that one view, at multiple render scales (Section 8.6), never an animation.
• User accounts, login, or saved galleries. Every visitor is anonymous; the only persistence mechanism is the permalink itself.
• A full clothing, armor, or equipment catalog. SkinForge models exactly the layers needed to render the visible cosmetic look (Section 3.3, Section 3.5); it is not an equipment or stat planner.
• In-game integration, purchasing, or applying a skin to a real account. SkinForge never connects to the game client, the game server, or any Outlands account system.
• Native mobile apps. The product is a single responsive website (Section 1 D-24).
• Advertising or paid subscription tiers. The only monetization surface is the optional donation link (Section 2.6 constraint, Section 1 D-04).

2.6 Success metrics #

All metrics are computed from first-party server-side data only — no third-party analytics, per the no-cookies-for-visitors, no-PII architecture decision (Section 1 D-19) and the observability model in Section 21.

2.7 Constraints and assumptions #

• The operator has, or will obtain, a local copy of the UO Outlands client files. SkinForge never distributes, downloads, or bundles any Outlands game asset itself; it only reads from a local directory the operator supplies to the CLI, and only server-side, only for staff.
• The exact on-disk layout, file formats, and compression schemes used by that client install are unknown at specification time. This document does not assume a specific MUL/UOP/other layout. Section 7 defines a staged discovery capability — inventory, identify, probe, extract, classify, normalize — specifically because the layout must be discovered against the real files, not assumed from a stale reference.
• UO Outlands may change its client format, add new hue ranges, or restructure asset packaging in a future patch. The pipeline's staged design (Section 7) and the immutable-build model (Section 9, Section 13.7) are built so a format change degrades to a review queue, never a crash and never a silent data loss.
• The tool has no relationship with, and is not endorsed or sponsored by, UO Outlands, Broadsword Online Games, or Electronic Arts. It exists as a fan-made companion tool. See Section 2.8.
• Traffic is expected to be modest and bursty (spikes around patch announcements and popular shared links), well within the single-VPS scaling posture described in Section 4.8.

2.8 Unofficial fan-project posture #

Outlands SkinForge is an unofficial, fan-made tool built by and for the UO Outlands player community. Section 10.4 owns the exact disclaimer text rendered on every public page: "Outlands SkinForge is an unofficial fan project. It is not affiliated with, endorsed by, or sponsored by UO: Outlands, Broadsword Online Games, or Electronic Arts." It reads only from art and data files the operator already possesses through their own legitimate client installation, hosts only derived preview imagery necessary to serve its stated purpose, and makes no claim of ownership over any underlying game asset. This posture is stated persistently on every public page (Section 1 D-22) and detailed in full, including takedown procedure, in Section 20.8.

2.9 Glossary #

Every UO-specific and SkinForge-specific term used in this document — gump, layer, tiledata, MUL, IDX, UOP, hue, partial hue, paperdoll, doll art, ClassicUO, doubloon, wig stand, and others — is defined once, precisely, in Section 3.9, with a consolidated glossary appendix in Section 28.

2.10 Risks and mitigations #

The product's overall risk posture favors mechanisms that degrade gracefully — a review queue instead of a crash, a retired asset instead of a broken link, a cached render instead of a compute spike — over mechanisms that require perfect upfront knowledge of the game client's internals, because that knowledge is explicitly unavailable at specification time (Section 2.7).

2.11 Why a dedicated tool, not an existing alternative #

Players currently approximate SkinForge's job with tools and habits that each fall short in a way that motivates building a dedicated product rather than recommending a workaround:

SkinForge's contribution is specifically the combination of: zero setup for the visitor, full compositing across every hueable slot at once, and a permanent shareable record — none of the existing approaches provide all three together.

2.12 North star statement #

If SkinForge succeeds, a UO Outlands player never spends doubloons on a cosmetic without first seeing, on their own body shape, exactly what it will look like combined with everything else they already wear — and every combination worth remembering has a link that still works, unchanged, years later.

3. Domain Primer — UO Outlands Paperdolls, Layers, Hues & Skins #

This section teaches an executor with no prior Ultima Online knowledge everything needed to build SkinForge correctly. Read it before Sections 6 through 14; those sections assume this vocabulary and these rules without re-explaining them.

3.1 What a paperdoll is #

In Ultima Online (and every client built on its art conventions, including the ClassicUO-based Outlands client), a character's on-screen inventory and appearance summary is called the paperdoll. It is a flat, front-facing, static 2D illustration of the character standing in a single neutral pose, built by stacking pre-drawn sprite images — one per equipped or worn item — on top of a base body sprite, in a fixed drawing order. There is no 3D model, no skeleton, no animation frames involved in a paperdoll: it is layered 2D art, the same technique used by paper doll toys, hence the name.

The paperdoll art area is 260 pixels wide by 330 pixels tall (Section 8.1 fixes this as the composite canvas). Every sprite involved in a paperdoll — the body, and every worn item — is pre-drawn at a size and offset that positions it correctly inside that fixed canvas. SkinForge does not scale, rotate, or reposition sprites relative to each other; it reproduces exactly what the in-game paperdoll shows, at integer pixel scales (Section 8.6).

Because the entire visual is 2D sprite compositing, SkinForge's rendering problem reduces to: pick the right sprite for each occupied layer, recolor it if a hue applies, and stack the results in the correct order. There is no lighting, no perspective, and no 3D geometry anywhere in the render path (Section 8 owns the full algorithm).

3.2 Bodies #

UO Outlands has exactly two player character bodies available for cosmetic design purposes:

These are the only two bodies SkinForge supports. UO as a franchise has other playable races in some rule sets (elf, gargoyle) but UO Outlands' player character bodies for the purposes this tool covers are human only, male and female — this is a fact about the game, not a limitation SkinForge imposes (Section 2.5 states this as a non-goal boundary, not an omission).

The base gump number (0x000C / 0x000D) is the identifier the original client art format uses for the nude/base body sprite that every other layer draws on top of. SkinForge's own code never needs to special-case these numbers outside the asset extraction pipeline (Section 7), where they are the seed values used to locate body art during classification (Stage D5).

Gender-specific art variants. Some cosmetic assets have separate artwork for male and female bodies (because the sprite must fit differently over each body shape); others share one sprite drawn to fit both. The asset_variants table (Section 6) records, per asset, which bodies it has art for. Rule when an asset has no variant for the visitor's chosen gender: that asset is not offered in the picker for that slot when the visitor's body is set to the gender lacking a variant (Section 11.6 filters the asset list by body before rendering it). If a permalink was created for one body and the design is later inspected in the designer with the other body selected, a slot whose chosen asset has no variant for the new body is retained but flagged incompatible: it contributes no layer to the composite while incompatible, and switching back to the original body restores it automatically, because nothing was cleared. This is the rule for gender/body mismatches; Section 11.13 owns the exact recovery behavior and warning copy, and Section 13 owns dropping a still-incompatible entry from the canonical document at Share time.

3.3 The canonical slot registry #

SkinForge composites exactly 19 layers per character: one required body layer and 18 choosable cosmetic slots the visitor can leave empty or fill, backpack included. backpack is always visible in the rendered output, but it is a choosable slot exactly like any other — the visitor may pick its asset and hue — it simply falls back to a default asset when the design carries no entry for it, rather than disappearing the way any other empty slot does. The table below is the canonical registry — every other section that needs slot order, display names, or gender rules references this table by section number rather than repeating it.

Layers 24 and 13 are the classic client's alternate ids for the same visual slot as legs_inner and torso_inner respectively; SkinForge records only the primary id shown above, which is the one classification (Section 7.6) matches on.

That is 19 rows total: one required body slot (row z=10) and 18 choosable cosmetic slots — the 17 rows a visitor may leave empty, plus the always-visible backpack slot (row z=190), whose asset and hue are visitor-changeable exactly like any other choosable slot. A choosable slot left empty contributes nothing to the composite — it is simply skipped during compositing (Section 8.4 owns the composite order), not rendered as a transparent placeholder. backpack is the one choosable slot that never actually renders empty, because it falls back to the default asset backpack.default at hue 0 when the design carries no entry for it.

3.4 Composite order and why z-order matters #

The z column in Section 3.3 is the literal paint order: the renderer draws slot body first, then tattoo_body, and so on up through backpack last, alpha-compositing each non-empty layer's sprite on top of everything drawn so far (Section 8.4 owns the exact pixel algorithm). Getting this order wrong produces a character that looks structurally wrong even though every individual sprite is correct — for example, if torso_outer (a robe) were drawn before torso_middle (a chest piece), the chest piece would incorrectly appear to float on top of the robe instead of being covered by it.

Worked example — five slots stacking, human female, reading bottom to top:

1. body (z=10): the base female nude sprite, recolored to skin hue 1002.
2. legs_inner (z=40): plain trousers, hue 0 (as-drawn).
3. torso_inner (z=50): a linen shirt, hue 1302.
4. torso_outer (z=110): a hooded robe, hue 1802, drawn over the shirt — the shirt's sleeves remain visible below the robe's shorter sleeve art because the sprites are drawn to the correct relative proportions by the original artists; SkinForge does not need any collision logic, only correct order.
5. hair (z=130): a long braid style, hue 1102, drawn last in this example — visible because no head slot is occupied to cover it.

If a head slot were added at z=170, it would draw after hair and would visually cover the top of the braid where a hat naturally would, without SkinForge writing any per-slot occlusion rule; the z-order table alone produces correct results because the original sprites were drawn with this convention in mind.

3.5 Excluded layers #

SkinForge intentionally does not model the following classic UO layers: one-handed weapon (layer 1), two-handed weapon (layer 2), ring (layer 8), bracelet (layer 14), talisman (layer 9), and it does not model any stat-bearing armor or weapon catalog beyond the cosmetic slots in Section 3.3.

Rationale. SkinForge is a skin designer, not an equipment or combat-loadout planner. Weapons and jewelry in UO Outlands are primarily functional items chosen for combat statistics, not cosmetic identity, and including them would expand scope into balance-sensitive game data (damage, resists, enchantments) that is out of date the moment it is captured and is irrelevant to the "what does my character look like" question SkinForge answers. Excluding them keeps the asset pipeline (Section 7) focused on purely cosmetic art and keeps the catalog (Section 15) free of stat comparisons that would require constant balance-patch maintenance.

3.6 Hues #

A hue in UO is a recoloring instruction applied to a grayscale-shaded sprite at render time, rather than a separate full-color image per color variant. This is why one piece of art (say, "long braid hair") can be offered in hundreds of colors without hundreds of separate sprite files: the artist draws the sprite once, using shades of gray to represent light and shadow, and the hue system remaps those gray shades to a chosen color ramp at draw time.

The hues.mul structure. The classic hue data file is organized as 375 blocks of 8 hues each (3,000 hues total, indices 1 through 3000; index 0 is reserved, see below). Each of the 8 hues within a block contains:

• A 32-entry color table: 32 packed 16-bit colors forming a ramp from dark to light, used to remap a sprite's gray shades to this hue's color ramp.
• A start/end byte pair: metadata the original client used for certain rendering effects (partial application boundaries); SkinForge's importer records these values for provenance but the render algorithm in Section 8.3 uses only the 32-entry table.
• A 20-byte name field: a fixed-width ASCII name for the hue, used for display in the catalog (Section 15) and admin console (Section 17).

Do not confuse a hue block — the 8-hue grouping in the file layout described above, a fact about how hues.mul is laid out on disk — with a hue group, the curated, staff-maintained grouping of hues used for browsing and filtering (hue_groups, Section 6.14). The two are unrelated concepts that happen to share a word.

ARGB1555 packing. Each of the 32 colors in a hue's table is a 16-bit value in ARGB1555 format: 1 bit alpha, 5 bits red, 5 bits green, 5 bits blue. The exact bit layout, most significant bit first, is:

bit:   15  14 13 12 11 10  9  8  7  6  5  4  3  2  1  0
field:  A   R  R  R  R  R  G  G  G  G  G  B  B  B  B  B

Unpacking a 16-bit ARGB1555 value into 8-bit RGB channels, and the transparency convention that applies when a packed value is exactly 0x0000, are specified once, with a single reference implementation, in Section 8.3.1 — this section introduces the bit layout conceptually but does not restate the unpacking function.

Hue index 0 means "unhued." A hue value of 0 is not a lookup into the table at all; it is the sentinel meaning "draw the sprite's own original colors, unmodified." Every choosable slot in Section 3.3 accepts hue 0 as a valid choice with this meaning, and the design JSON (Section 13.1) encodes it the same way. The renderer's exact handling of hue 0 is owned by Section 8.3.3.

Full hue vs. partial hue. UO's hue system has two application modes:

• Full hue remaps every non-transparent pixel of the sprite: each pixel is looked up in the 32-entry table by table index, and that entry's color replaces the pixel entirely, regardless of the pixel's original color.
• Partial hue applies the same table-lookup process but only to pixels that are already gray/neutral in the source art (used for effects like "just recolor the metal parts of an item, leave the leather parts alone" in the original game). SkinForge's asset importer records, per sprite, whether a partial-hue flag was present in the source data (Section 7's provenance record); this specification does not require SkinForge to expose "partial vs full" as a visitor-facing choice — it is a property of the asset, decided by the original artist, not by the visitor.

How a pixel's table index is derived — the exact bit arithmetic, the grey test that gates partial hue, and the reference implementation — is owned entirely by Section 8.3; this section supplies only the conceptual model above, so a reader who has not yet reached Section 8 understands what a hue is before Section 8 defines exactly how one is applied to a pixel.

3.7 UO Outlands specifics #

UO Outlands runs a client based on ClassicUO, an open-source, modernized UO client, but ships its own art assets and its own custom hue additions layered on top of (or alongside) the base UO hue space described in Section 3.6. SkinForge must treat everything Outlands-specific as data the pipeline discovers, never as a hard-coded assumption, because the exact custom hue index ranges, the exact set of Outlands-added cosmetic slots such as tattoo_body, and the exact asset ids are not known at specification time (Section 2.7 states this constraint explicitly; Section 7 is the staged discovery capability that resolves it against the real files at import time).

What is known and modeled as fact, because it comes from the game's documented cosmetic economy rather than from undiscovered binary layouts:

• Outlands sells skin hues as a cosmetic customization (through the Wig Stand and related cosmetic vendors, paid for with doubloons, the game's cosmetic currency). SkinForge models this as the skin hue group (Section 3.6, Section 6), a curated subset of the hue space tagged during import per the curation policy in Section 1 D-05.
• Outlands sells hair and beard styles as cosmetics through the same vendors; the game does not publish a fixed enumerated list of style ids anywhere outside the client art itself. SkinForge stores every hair or beard style as a plain catalog asset (Section 6) with a slot_key of hair or facial_hair respectively, discovered and classified by the pipeline (Section 7) exactly like any other cosmetic asset — no different in structure or handling from a footwear or cloak asset.
• Tattoos and backpack dyes are sold the same way, cosmetically, mapping to the tattoo_body and backpack slots respectively (Section 3.3).
• The wardrobe and in-game paperdoll preview UI that inspired this tool already show gender-restricted items — for example, beard styles are only ever shown for male characters. This is the precedent SkinForge follows exactly for the facial_hair slot's male-only rule (Section 3.3, Section 11.5).

SkinForge models these facts about the cosmetic economy in its taxonomy and admin workflows; it does not model or replicate Outlands' purchasing flow, doubloon balances, or vendor inventory — that would be in-game integration, an explicit non-goal (Section 2.5).

3.8 What "a skin" means in SkinForge terms #

Within this specification, "a skin" (the noun, matching the product name) means exactly this tuple: one body code (m or f), one skin hue applied to the body slot, and a set of zero-or-more entries for the 18 choosable cosmetic slots (Section 3.3, backpack included), each entry naming one asset and one hue. This tuple, canonicalized and hashed, is the entire content of a permalink. The exact JSON shape, canonicalization rule, and hashing algorithm that turn this concept into a storable, shareable, immutable record are owned by Section 13; this section defines only the concept, Section 13 defines its encoding.

3.9 Terminology table #

3.10 Worked example: unpacking and applying a hue end to end #

This subsection intentionally carries no worked example of its own. The hue algorithm — table-index derivation, unpacking, and pixel application — is specified exactly once, in Section 8.3, and its one worked example lives in Section 8.3.6, with additional worked examples in Appendix B (Section 28.2); publishing a second, independently-computed set of numbers here would risk the two drifting apart, so this section does not duplicate them. Read Section 8.3 for the full algorithm and its worked arithmetic; this section's role is limited to the conceptual introduction to hues in Section 3.6.

3.11 Gender and slot interaction edge cases #

Beyond the base rule in Section 3.2 (an asset with no variant for the visitor's chosen body is not offered, and is retained-but-flagged if the body changes underneath an existing choice), three further cases are decided here so the executor never has to invent behavior at build time:

• Switching body with facial_hair occupied. Because facial_hair (Section 3.3) is male-only, switching the body from m to f leaves the slot's stored selection untouched but incompatible — it contributes no layer to the composite while body is f, and Section 11.13 shows the same warning-badge treatment as any other gender-incompatible slot, even though the underlying reason (a slot restriction, not a missing art variant) is different from the general case in Section 3.2. Switching back to m restores it automatically, because nothing was cleared.
• An asset published for only one gender is later given an art variant for the other gender in a subsequent build. The existing asset_variants row set (Section 6) simply gains a new row for the newly available body; no retroactive change is made to any design that was created before the new variant existed, because a design's rendering is pinned to its build_id (Section 13.7) and its original choice (or lack of one, if the slot was previously incompatible) is preserved exactly as made.
• A slot with a gender-neutral display name but gendered art (e.g. hair). The slot itself is offered identically for both genders (Section 3.3 marks hair as "both, different style sets"); the asset picker (Section 11.6) filters the list of selectable assets to only those with a variant for the current body, so a visitor never sees a hairstyle they cannot actually select — this differs from facial_hair, where the entire slot control is shown disabled with an explanatory tooltip (Section 11.5) rather than filtering a list, because facial_hair is not offered for female bodies at all, not merely absent for the visitor's current asset choice.

3.12 Why hue math and slot rules are decided here, not left to the pipeline #

An executor might expect the discovery pipeline (Section 7) to determine rules like "beard is male only" or "hue table has 32 entries" from the source data at import time, since the pipeline is explicitly designed to discover unknown facts (Section 2.7). These particular facts are different in kind from what the pipeline discovers: they are stable properties of the UO art format and of UO Outlands' own game design that do not vary between client builds or patches, unlike asset ids, hue index ranges, or on-disk file layout, which do vary and are exactly what Section 7's staged discovery resolves. Hard-coding the stable facts in this section, while leaving the genuinely variable facts to discovery, keeps the pipeline focused on what actually changes patch to patch.

3.13 Domain facts quick-reference table #

The following table consolidates the fixed numeric facts introduced throughout this section, for an executor who needs to check a constant while implementing Sections 6 through 14 without re-reading the full prose.

3.14 Summary of what this section hands to later sections #

Section 3 is a reference, not an implementation. The tables and rules above are consumed directly by: the slot registry and z-order by Section 6 (schema) and Section 8 (render algorithm); the hue structure and bit-packing math by Section 8.3; the body/gender rules by Section 11.6 (asset picker filtering) and Section 13 (design encoding); the excluded-layer rationale by Section 15 (catalog scope) and Section 28 (appendix reference tables); and the terminology table by every other section that uses a UO-specific term, none of which re-define it.

4. Technology Stack & Architecture #

4.1 Stack table #

Section 4 is the ONLY place this specification states dependency versions. Every other section refers to "the version in Section 4" rather than repeating a number.

Put this paragraph, verbatim, wherever this specification is read as the authoritative statement on dependency freshness:

These version lines are a known-good floor, not a lockfile. At build time, install the current stable release of each dependency (deno add jsr:@fresh/core, npm install <pkg>@latest, or your ecosystem's equivalent), confirm the major line still matches, and let the lockfile record the exact resolved versions.

4.2 Why Deno + Fresh here #

Four properties of this specific product favor Deno + Fresh over alternative stacks:

1. One language, one runtime, two processes. The public web app and the staff CLI (Section 4.3) share the entire core/ domain library (hue math, compositing, format parsers, schema). A single TypeScript codebase running on a single runtime means the compositing algorithm used for live client-side preview, server-side permalink rendering, and staff-side extraction validation is provably the same code, not three reimplementations that can drift.
2. Islands avoid a heavyweight client bundle for pages that do not need one. Most of SkinForge's traffic is people opening a permalink (Journey B, Section 2.4) or browsing the catalog (Journey C). Those pages are almost entirely static content once rendered; Fresh ships them with no JavaScript beyond what a specific interactive widget (e.g. a hue swatch hover) needs, as an isolated island. Only /, which hosts the full designer, loads the larger designer island bundle; /design is not a separate page — it permanently redirects to / (Section 10.5).
3. Server-side rendering by default keeps permalinks crawlable and fast. Chat-app and forum link unfurlers (Journey B, step 1) and search engine crawlers never execute JavaScript reliably; Fresh renders the full page, including the composited paperdoll's <img> tag pointing at a server-rendered PNG/WebP (Section 8.10), on the server for every request, so the content is present in the initial HTML response.
4. A single language for CLI and web removes a translation layer. The staff import pipeline (Section 7) is not a separate service in a separate language calling into the web app's API; it is a Deno CLI in the same repository, importing the same core/ modules the web app imports, so a change to the hue math (Section 8.3) or the compositing algorithm (Section 8.4) cannot silently go out of sync between "how the CLI validates an extracted asset" and "how the web app renders it."

4.3 Process topology #

Three processes, one database, one storage target, in front of a reverse proxy and (optionally) a CDN:

                              ┌─────────────────────┐
                    (optional)│         CDN          │  (Section 1 D-10, Section 19.3)
                              └──────────┬───────────┘
                                         │
                              ┌──────────▼───────────┐
                              │   Caddy (reverse      │  TLS termination, HTTP/2,
                              │   proxy, TLS)         │  routes to the web process
                              └──────────┬───────────┘
                                         │
                    ┌────────────────────▼────────────────────┐
                    │         Deno process: web app             │
                    │  Fresh routes (public + admin) + JSON API  │
                    │  + render endpoints (Section 8, 16)         │
                    │  + in-process job runner (Section 4.7)      │
                    └───────┬───────────────────────┬───────────┘
                            │                         │
                 ┌──────────▼─────────┐   ┌──────────▼──────────┐
                 │   PostgreSQL 18     │   │   Object storage      │
                 │   (metadata,        │   │   (fs or S3-compat,   │
                 │   jobs, sessions)   │   │   Section 4.7)        │
                 └──────────▲──────────┘   └──────────▲───────────┘
                            │                          │
                    ┌───────┴──────────────────────────┴────────┐
                    │   Deno process: skinforge-cli (staff-only)  │
                    │   discovery, extraction, import, publish    │
                    │   (Section 7, Section 9), same core/ library │
                    └──────────────────────────────────────────────┘

The web process and the CLI process never run in the same OS process, but they import the exact same core/ package from the same repository checkout, so there is exactly one implementation of every domain algorithm (Section 4.5).

4.4 Request flow walkthroughs #

Cold load of /d/:code (a shared permalink, no cache warm anywhere):

Browser              Caddy/CDN         Fresh web process        Postgres      Object storage
  │  GET /d/abc123def4    │                    │                    │               │
  │───────────────────────▶ (cache miss)       │                    │               │
  │                        │───────────────────▶ route handler      │               │
  │                        │                    │  look up design ──▶ SELECT design │
  │                        │                    │◀───────────────────  row + build   │
  │                        │                    │  check render cache ──────────────▶ HEAD renders/../hash.webp
  │                        │                    │◀────────────────────────────────── 404 (cache miss)
  │                        │                    │  composite server-side (Section 8) │
  │                        │                    │  write render ─────────────────────▶ PUT renders/../hash.webp
  │                        │                    │  render full HTML page             │
  │                        │◀───────────────────  200 HTML (img src = render URL)     │
  │◀───────────────────────  200 HTML             │                    │               │
  │  GET render URL from <img src>                │                    │               │
  │───────────────────────▶ (cache miss) ────────▶ render route ──────────────────────▶ GET renders/../hash.webp
  │                        │                    │◀──────────────────────────────────── 200 image bytes
  │◀───────────────────────  200 image, immutable cache headers (Section 8.9)          │

Designer slot change with client-side compositing (no network round trip for the preview itself):

Visitor action        Designer island (browser)                  In-memory state
  │ picks hair asset+hue │                                            │
  │──────────────────────▶ update signal for slot `hair`  ────────────▶ slots.hair = {asset, hue}
  │                       │ recompute composite on <canvas>:           │
  │                       │   for each occupied slot in z-order:       │
  │                       │     fetch cached sprite bytes (already      │
  │                       │     preloaded per Section 12.2)             │
  │                       │     apply hue table lookup (Section 8.3)   │
  │                       │     alpha-composite onto canvas             │
  │                       │ canvas repaints, visible within one frame  │

Cache-miss server render (triggered by any first request for a given design/scale/format combination, whether from a browser, a crawler, or the OG image generator):

Requester             Fresh render route            core/hue+composite (Section 8)      Object storage
  │ GET /render/d/:code@2.webp │                          │                        │
  │──────────────────────────────▶ compute cache key         │                        │
  │                              │  (Section 8.9) ───────────▶ check existence         │
  │                              │                            │───────────────────────▶ HEAD
  │                              │                            │◀─────────────────────── 404
  │                              │  load design + asset refs  │                        │
  │                              │  from Postgres              │                        │
  │                              │  run composite pipeline ───▶ decode sprites, hue,   │
  │                              │                              alpha-composite, encode │
  │                              │◀──────────────────────────  bytes                    │
  │                              │  write to object storage ─────────────────────────────▶ PUT
  │◀──────────────────────────────  200 image, immutable cache headers                    │

OG image fetch by a crawler (e.g. Discord's link unfurler):

Crawler                Fresh /og route              core/codec/og.ts (Section 14)         Object storage
  │ GET /og/d/:code.png  │                               │                          │
  │────────────────────────▶ check og_images cache row      │                          │
  │                        │───────────────────────────────▶ SELECT og_images         │
  │                        │◀─────────────────────────────── row absent (first fetch) │
  │                        │  render composite PNG at OG scale (calls Section 8)       │
  │                        │  build satori SVG layout, rasterize with resvg-wasm       │
  │                        │  store PNG ───────────────────────────────────────────────▶ PUT
  │                        │  insert og_images row                                     │
  │◀────────────────────────  200 PNG, immutable cache headers                          │

Staff import run (Journey D, Section 2.4):

Staff (CLI)            cli/main.ts                 core/import (Section 7, 9)   Postgres / storage
  │ skinforge-cli import discover --client-dir <path> │  │                          │
  │─────────────────────────────────────────▶ Stage D1–D3 (walk, identify, probe)     │
  │                                          │  insert source_files, import_runs rows ▶ INSERT
  │ skinforge-cli import extract --run <id> │              │                          │
  │─────────────────────────────────────────▶ Stage D4–D6 (extract, classify,          │
  │                                          │  normalize) ───────────────────────────▶ INSERT candidates,
  │                                          │                                          write normalized images
  │  (staff reviews candidates in admin console — separate flow, Section 17.6)          │
  │ skinforge-cli builds publish --build <id> │            │                          │
  │─────────────────────────────────────────▶ promote approved candidates to assets ──▶ INSERT/UPDATE
  │                                          │  new game_build row, invalidate CDN     │
  │                                          │  (Section 9.5) if configured             │

4.5 Module boundaries #

core/       Pure domain logic. No HTTP, no filesystem access except through injected
            interfaces, no framework imports. Sub-modules: formats/ (MUL/IDX/UOP readers),
            hue/ (Section 8.3 math), composite/ (Section 8.4), codec/ (PNG/WebP encode via
            jsquash), schema/ (Zod modules, Section 5.4), design/ (canonical JSON + hashing,
            Section 13), db/ (hand-written SQL repository modules).
web/        Fresh application. routes/ (pages + API + render + admin, following Fresh's file
            routing convention), islands/ (interactive components: designer, hue picker,
            catalog filters), components/ (server-rendered, non-interactive UI pieces).
cli/        skinforge-cli commands: import discover/identify/probe/extract/classify/normalize/
            run/verify, builds submit-review/publish/rollback/diff, db migrate/seed/fixtures,
            admin create-user/reset-credentials.
db/         migrations/ (Section 6.1 numbered SQL files), seed data (Section 1 D-21).

Dependency direction rule, enforced by lint (Section 5.4) and CI (Section 22.11): core/ imports nothing from web/ or cli/. web/ and cli/ may both import from core/. web/ never imports from cli/ and cli/ never imports from web/ — the only shared code path between them is core/. This guarantees the compositing and hue-math code paths used by the live browser preview, the server-side permalink render, and the staff-side extraction validation step are the literal same functions, not parallel reimplementations.

4.6 Rendering architecture decision record #

Decision: all pixel-level work — sprite decoding, hue table application, alpha compositing, and final image encoding — is pure TypeScript plus WASM codecs (@jsquash/png, @jsquash/webp, @resvg/resvg-wasm). No ImageMagick, no sharp, no other native image-processing binary, and no headless browser anywhere in the render path.

Why:

• No native binary dependency. sharp and ImageMagick require native shared libraries that must match the host OS/CPU architecture and be kept patched independently of the application's own dependency updates; a pure TS/WASM path runs identically wherever Deno runs, including inside the minimal Docker image described in Section 23.1.
• Deterministic, portable pixel math. UO's hue algorithm (Section 8.3) is a simple table lookup, not a general image-processing operation; implementing it directly is less code than adapting a general-purpose library's color-manipulation API to replicate a proprietary game's exact recoloring rule, and it guarantees pixel-perfect parity with the original client rather than an approximation.
• Same code client- and server-side. @jsquash/* codecs and the hand-written hue/composite code run unmodified in both the Deno server process and, compiled for the browser, inside the designer island (Section 12.2), which is what makes the golden-image parity test in Section 22.3 possible in the first place — a native server-only library could never run in the browser to be compared against.
• No headless browser for OG images. satori + resvg-wasm produce the Open Graph PNG (Section
  1. without launching a Chromium instance, which would be an order of magnitude heavier in both memory and startup latency for a single-VPS deployment (Section 4.8).

Trade-offs accepted:

• WASM codecs are somewhat slower per-image than a native sharp call. This is acceptable because SkinForge's images are small (260×330 source canvas, Section 8.1) and render outputs are cached aggressively and immutably (Section 8.9); the cost is paid once per unique design/scale/format combination, never per request.
• The team maintains its own hue/compositing implementation rather than delegating to a general-purpose library; this is intentional, per the determinism argument above, and the surface area is small (a handful of pure functions, Section 8.3–8.4), not a general image-processing engine.

Measured expectations: a single composite-and-encode operation (all 19 potential slots, WebP output, scale 1) is expected to complete in under 50ms on typical single-VPS-class CPU hardware; the performance budget and how it is enforced in CI is detailed in Section 19.1 and Section 22.7.

4.7 Data storage decisions #

• PostgreSQL for all metadata. Every table in Section 6 — catalog data, designs, jobs, admin accounts, audit log, rate-limit state, counters — lives in Postgres. One database technology to operate, back up (Section 23.6), and query, with strong consistency for the operations that need it (e.g. the design short-code collision check in Section 13.2 relies on a transactional read-check- insert).
• Object storage for pixels only. Rendered images, normalized source sprites, and OG images are never stored as database bytea columns; they live in the pluggable object storage driver (fs or s3, Section 1 D-02) addressed by content-hash keys (Section 8.9), keeping the database small and fast to back up, and letting a CDN (Section 19.3) serve image bytes directly from storage or its cache without touching the application process.
• No Redis, no message broker, in v1. Background work (rendering a permalink's first request, running an import stage, generating an OG image) is modeled as rows in a Postgres jobs table, claimed with SELECT ... FOR UPDATE SKIP LOCKED so multiple job-runner loop iterations (up to SKINFORGE_RENDER_MAX_CONCURRENCY, Section 1 D-25) never claim the same job twice. Rate limiting (Section 16.7) uses a Postgres-backed token-bucket table for durability across process restarts, fronted by an in-process LRU cache so the hot path for a well-behaved client never touches the database.
• Exact reasons this is sufficient at SkinForge's scale: job volume is bounded by request volume to a single small application (Section 2.6's target metrics imply low hundreds of jobs per day at launch scale), Postgres's SKIP LOCKED pattern is a well-established substitute for a dedicated queue at this volume, and removing Redis/a broker removes an entire additional stateful service to operate, back up, and secure on the single-VPS deployment target (Section 1 D-01).

4.8 Scaling posture #

What a single VPS handles. A modestly sized VPS (4 vCPU / 8GB RAM class) comfortably serves the target metrics in Section 2.6: the render path is CPU-bound but cached immutably per unique design/scale/format (Section 8.9), so steady-state traffic after a design has been viewed once is almost entirely served from object storage or the CDN with no compositing work at all. Postgres, object storage (if using the fs driver), and the web process share the one host under the default deployment (Section 1 D-01).

First bottleneck. The first bottleneck under load is CPU contention between (a) render-cache-miss compositing for many distinct new designs at once and (b) a concurrent staff import run's extraction and normalization work (Section 7, Stage D4/D6), because both are CPU-bound WASM/TS work on the same process pool. Section 1 D-25's job-runner concurrency cap exists specifically to bound this.

Horizontal-scaling path. The web process is stateless (all state is in Postgres and object storage), so the path to handling more traffic is: run multiple web process replicas behind the existing reverse proxy/CDN, point every replica at the same Postgres instance and the same object storage bucket (switching the storage driver to s3, Section 1 D-02, becomes necessary once more than one host needs to read/write the same files), and keep the job runner's SKIP LOCKED claiming model, which already works correctly with multiple concurrent claimers because it is a database-level lock, not an in-process one. Postgres itself would move to a managed or replicated instance before it became the bottleneck, given the read-heavy, small-row-size workload described in Section 6.

What would have to change to scale further: move the staff import/extraction workload (CPU-heavy, bursty, staff-triggered) to run on a separate host or a separate container from the public-facing web replicas, so a large import run never competes with public request latency; this requires no code change, only a deployment topology change, because cli/ already runs as an independent process from web/ (Section 4.3).

4.9 Alternatives considered and rejected #

4.10 Non-functional requirements #

These figures are the targets Section 19's caching and performance design is built to meet, and the thresholds Section 22's performance test gate (Section 22.7) checks in CI before a release is considered shippable. They are not aspirational — every other architectural decision in Section 4 (pure TS/WASM rendering, immutable content-addressed caching, a stateless web tier) is chosen specifically because it makes these numbers achievable on the single-VPS deployment target in Section 1 D-01 without requiring a larger infrastructure footprint at launch.

5. Repository Layout, Conventions & Coding Standards #

5.1 Full repository tree #

skinforge/
├── deno.json                      # tasks, imports map, compilerOptions, lint/fmt config (Section 5.2)
├── deno.lock                      # generated, committed
├── main.ts                        # production entrypoint: starts the Fresh web app + job runner
├── dev.ts                         # development entrypoint: Fresh dev server with hot reload
├── fresh.config.ts                # Fresh 2.x App builder: middleware, Tailwind plugin registration,
│                                   # island discovery (Section 4.1)
├── .env.example                   # every env var from Section 24, with the working defaults
├── .gitignore
├── README.md                      # developer's own project readme (not this specification)
├── CHANGELOG.md                   # Conventional-Commits-derived changelog
├── docker/
│   ├── Dockerfile                 # multi-stage: deno cache -> deno compile-free runtime image
│   ├── docker-compose.yml         # app + postgres + optional minio + caddy (Section 23.1)
│   └── Caddyfile                  # reverse proxy + TLS config
├── .github/
│   └── workflows/
│       ├── ci.yml                 # lint, fmt check, type check, unit + golden-image + e2e tests
│       └── deploy.yml             # build + push image + trigger deploy (Section 23)
├── core/                          # pure domain logic, no HTTP/framework imports (Section 4.5)
│   ├── config.ts                  # typed env var loader, single source of truth for defaults (Section 24)
│   ├── errors.ts                  # AppError hierarchy + error code registry (Section 25)
│   ├── hue-preview-reference.ts    # static hue-swatch preview data (Section 10.3.7, Section 15.5)
│   ├── formats/
│   │   ├── mul.ts                 # MUL/IDX reader (Section 7 Stage D2/D4)
│   │   ├── uop.ts                 # UOP/MythicPackage reader
│   │   ├── tiledata.ts            # tiledata.mul parser
│   │   ├── hues.ts                # hues.mul parser (Section 3.6)
│   │   └── detect.ts              # format fingerprinting (Section 7 Stage D2/D3)
│   ├── hue/
│   │   ├── unpack.ts              # ARGB1555 -> RGB8 (Section 3.6, Section 8.3.1)
│   │   └── apply.ts               # full/partial hue application (Section 8.3)
│   ├── composite/
│   │   ├── canvas.ts              # RGBA buffer compositing primitives (Section 8.4)
│   │   ├── render-design.ts       # full design -> composited buffer (Section 8.4)
│   │   └── scale.ts               # nearest-neighbour integer upscaling (Section 8.6)
│   ├── codec/
│   │   ├── png.ts                 # @jsquash/png wrapper
│   │   ├── webp.ts                # @jsquash/webp wrapper
│   │   └── og.ts                  # satori + resvg-wasm wrapper (Section 14)
│   ├── domain/
│   │   └── slots.ts               # the slot registry, z-order and the SlotKey type; exports the
│   │                               # Zod slot-key validator (Section 3.3) — the single owner of all
│   │                               # three; nothing else in core/ redeclares them
│   ├── schema/
│   │   ├── design.ts              # Zod schema for design JSON (Section 13.2.5); imports the
│   │   │                          # slot-key validator from core/domain/slots.ts rather than
│   │   │                          # redeclaring it
│   │   ├── asset.ts               # Zod schema for asset/variant records
│   │   ├── hue.ts                 # Zod schema for hue records
│   │   └── admin.ts               # Zod schema for admin console forms
│   ├── http/
│   │   └── security-headers.ts    # CSP and the other security response headers (Section 20.4)
│   ├── design/
│   │   ├── canonicalize.ts        # sort keys, strip empties (Section 13.3)
│   │   └── short-code.ts          # Crockford Base32 hashing (Section 13.4)
│   ├── db/
│   │   ├── client.ts              # postgres.js pool setup
│   │   ├── assets.ts              # hand-written SQL repository module
│   │   ├── hues.ts
│   │   ├── designs.ts
│   │   ├── jobs.ts
│   │   └── ...                    # one module per table group (Section 6)
│   ├── storage/
│   │   ├── driver.ts              # pluggable object-storage interface (Section 1 D-02)
│   │   ├── fs.ts                  # local-disk driver
│   │   └── s3.ts                  # S3-compatible driver
│   ├── auth/
│   │   ├── session.ts             # admin session issuance/validation (Section 17.1)
│   │   └── totp.ts                # TOTP enrollment/verification (Section 17.2)
│   ├── api/
│   │   └── cursor.ts              # opaque, HMAC-signed pagination cursor encode/decode (Section 16.5)
│   ├── search/
│   │   └── tsvector.ts            # catalog full-text search query building (Section 15.6)
│   ├── util/
│   │   └── rng.ts                 # seeded PRNG for the Randomize feature (Section 11.8)
│   ├── jobs/
│   │   ├── runner.ts              # SKIP LOCKED claim loop (Section 4.7)
│   │   └── handlers/              # one file per job type (render, og-generate, import-stage)
│   ├── i18n/
│   │   └── en.ts                  # every user-facing string (Section 5.8, Section 1 D-17)
│   └── import/
│       ├── discover.ts            # Stage D1
│       ├── identify.ts            # Stage D2
│       ├── probe.ts               # Stage D3
│       ├── extract.ts             # Stage D4
│       ├── classify.ts            # Stage D5
│       └── normalize.ts           # Stage D6
├── web/
│   ├── routes/
│   │   ├── index.tsx               # `/` (Section 10)
│   │   ├── design.tsx               # `/design` (301 redirect to `/`)
│   │   ├── d/[code].tsx             # `/d/:code`
│   │   ├── catalog/
│   │   │   ├── index.tsx            # `/catalog`
│   │   │   ├── [slotKey]/
│   │   │   │   ├── index.tsx        # `/catalog/:slotKey`
│   │   │   │   └── [assetKey].tsx   # `/catalog/:slotKey/:assetKey`
│   │   ├── hues/
│   │   │   ├── index.tsx            # `/hues`
│   │   │   └── [hueIndex].tsx       # `/hues/:hueIndex`
│   │   ├── about.tsx
│   │   ├── faq.tsx
│   │   ├── legal.tsx
│   │   ├── support.tsx
│   │   ├── changelog.tsx
│   │   ├── sitemap.xml.ts
│   │   ├── robots.txt.ts
│   │   ├── opensearch.xml.ts
│   │   ├── render/                  # Section 8 render endpoints
│   │   ├── og/                      # Section 14 OG endpoints
│   │   ├── api/v1/                  # Section 16 JSON API
│   │   └── admin/                   # Section 17.17 admin API + admin console routes (`/admin/api/*`)
│   ├── islands/
│   │   ├── designer/                # the main interactive designer island tree (Section 11)
│   │   │   ├── actions.ts           # designer state-mutation actions over Preact Signals (Section 11.3)
│   │   │   └── virtual-list.ts      # option-panel grid windowing helper (Section 11.15)
│   │   ├── hue-picker/
│   │   ├── catalog-filters/
│   │   └── admin/                   # interactive admin console widgets
│   ├── components/
│   │   ├── ui/                      # server-rendered design-system primitives (Section 18.5)
│   │   │   ├── button.tsx
│   │   │   └── use-focus-trap.ts
│   │   └── ...                      # other server-rendered, non-interactive UI (Section 18)
│   ├── styles/
│   │   └── theme.css                # design token custom properties (Section 18.2, Section 18.4)
│   └── static/                      # non-generated static assets
│       ├── favicon.ico
│       ├── manifest.json
│       ├── social-card.png          # site-wide OG fallback card, 1200×630 (Section 14.9)
│       └── fonts/
│           └── PixelifySans-Regular.ttf   # OG image generator only (Section 14.4); satori cannot
│                                           # parse WOFF2, so the OG pipeline needs a TTF
├── scripts/
│   └── build-og-fallback.ts         # regenerates web/static/social-card.png (Section 14.9.1)
├── cli/
│   ├── main.ts                      # skinforge-cli entrypoint
│   └── commands/
│       ├── import-discover.ts
│       ├── import-identify.ts
│       ├── import-probe.ts
│       ├── import-extract.ts
│       ├── import-classify.ts
│       ├── import-normalize.ts
│       ├── import-run.ts            # `import run --build <id>`, runs D1-D6 against an existing build
│       ├── import-all.ts            # `import all --client-dir <path> --build-label <label>`, creates the build then runs D1-D6
│       ├── import-verify.ts
│       ├── builds-submit-review.ts
│       ├── builds-publish.ts
│       ├── builds-rollback.ts
│       ├── builds-diff.ts
│       ├── db-migrate.ts
│       ├── db-seed.ts               # `db seed`: reference/taxonomy data (hue_groups, tags)
│       ├── db-fixtures.ts           # `db fixtures`: local-dev sample assets/hues (Section 1 D-21)
│       └── admin-user.ts
├── db/
│   ├── migrations/                  # Section 6.1, numbered SQL files
│   │   ├── 0001_init.sql
│   │   └── 0002_seed.sql            # reference/taxonomy seed rows: hue_groups, tags (Section 6.31)
│   └── seed/
│       └── dev-fixtures.ts          # local-dev sample data, never a numbered migration (Section 1 D-21)
├── tools/
│   └── lint-plugin.ts               # custom `deno lint` plugin: floating-promise and JSX-text-node
│                                     # rules (Section 5.4, Section 5.8)
└── tests/                          # cross-cutting suites only — see the note below the tree
    ├── golden/                      # golden-image fixtures + comparison tests (Section 22.3)
    ├── integration/                 # db + storage integration tests (Section 22.4)
    ├── e2e/                         # Playwright specs (Section 22.5)
    ├── contract/
    │   └── api-v1/                  # OpenAPI contract fixtures (Section 16.10, Section 16.12)
    ├── fixtures/                    # committed regression fixtures (e.g. captured UOP hash vectors, Section 7.3.4)
    └── load/
        └── k6/                     # k6 load/performance test scripts (Section 19.11, Section 22.7)

Every path shown above is created by the developer in their own repository as part of executing this specification.

Section 5.1 is the sole authority on repository file and directory paths. Every module path, script path, or test path named anywhere else in this specification — including Sections 15, 16, 20, 22, 24, 26 and 27 — refers to a path listed in this tree. Where another section's prose and this tree ever disagree, this tree wins and the other section's text is the one that is wrong.

Unit tests are co-located, not gathered into a tests/unit/ tree. Every unit test lives beside the module it tests, inside core/ or cli/, named <module>_test.ts — the underscore-suffix form Deno's own test runner discovers natively, e.g. core/hue/apply_test.ts next to core/hue/apply.ts. Nothing under core/ or cli/ is ever tested from a parallel tests/unit/ or __tests__/ directory. The tests/ tree above holds only the suites that are not one-to-one with a single module: golden-image comparisons, integration tests, end-to-end specs, API contract fixtures, regression fixtures, and load tests.

5.2 deno.json contents #

Section 4 is the ONLY place this specification states dependency versions. The <major line from Section 4.1> placeholders below are filled in at scaffold time from Section 4.1's table, using the caret form for the stated major line; this block is a rendering of Section 4.1's table, and if the two ever disagree, Section 4.1 wins and this block must be regenerated. The test:e2e task's Playwright version is likewise pinned from Section 4.1 so it never floats independently of the E2E suite it drives.

{
  "tasks": {
    "dev": "deno run -A --watch=web/,core/ dev.ts",
    "build": "deno run -A dev.ts build",
    "start": "deno run -A main.ts",
    "check": "deno fmt --check && deno lint && deno check main.ts cli/main.ts",
    "test": "deno test -A core/ cli/ tests/integration",
    "test:golden": "deno test -A tests/golden",
    "test:e2e": "deno run -A npm:playwright@<major line from Section 4.1> test",
    "lint": "deno lint",
    "fmt": "deno fmt",
    "db:migrate": "deno run -A cli/main.ts db migrate",
    "db:seed": "deno run -A cli/main.ts db seed",
    "db:fixtures": "deno run -A cli/main.ts db fixtures",
    "import:discover": "deno run -A cli/main.ts import discover",
    "import:extract": "deno run -A cli/main.ts import extract",
    "builds:publish": "deno run -A cli/main.ts builds publish"
  },
  "imports": {
    "@fresh/core": "jsr:@fresh/core@^<major line from Section 4.1>",
    "@fresh/plugin-tailwind": "jsr:@fresh/plugin-tailwind@^<major line from Section 4.1>",
    "preact": "npm:preact@^<major line from Section 4.1>",
    "@preact/signals": "npm:@preact/signals@^<major line from Section 4.1>",
    "lucide-preact": "npm:lucide-preact@^<major line from Section 4.1>",
    "@std/http": "jsr:@std/http@^<major line from Section 4.1>",
    "@std/path": "jsr:@std/path@^<major line from Section 4.1>",
    "@std/ulid": "jsr:@std/ulid@^<major line from Section 4.1>",
    "@std/assert": "jsr:@std/assert@^<major line from Section 4.1>",
    "postgres": "npm:postgres@^<major line from Section 4.1>",
    "zod": "npm:zod@^<major line from Section 4.1>",
    "@jsquash/png": "npm:@jsquash/png@^<major line from Section 4.1>",
    "@jsquash/webp": "npm:@jsquash/webp@^<major line from Section 4.1>",
    "satori": "npm:satori@^<major line from Section 4.1>",
    "@resvg/resvg-wasm": "npm:@resvg/resvg-wasm@^<major line from Section 4.1>",
    "$core/": "./core/",
    "$web/": "./web/",
    "$cli/": "./cli/"
  },
  "compilerOptions": {
    "strict": true,
    "noUncheckedIndexedAccess": true,
    "exactOptionalPropertyTypes": true,
    "jsx": "precompile",
    "jsxImportSource": "preact"
  },
  "lint": {
    "plugins": ["./tools/lint-plugin.ts"],
    "rules": {
      "tags": ["recommended"],
      "include": ["no-unused-vars", "no-explicit-any"]
    },
    "exclude": ["tests/golden/fixtures/"]
  },
  "fmt": {
    "lineWidth": 100,
    "indentWidth": 2,
    "singleQuote": false,
    "exclude": ["tests/golden/fixtures/"]
  }
}

Import specifiers use the version-range caret (^) form shown above so deno.lock resolves to the current patch within each major line at install time, consistent with the "known-good floor, not a lockfile" rule in Section 4.1.

5.3 Naming conventions #

5.4 TypeScript standards #

• Strict mode is mandatory (strict: true, noUncheckedIndexedAccess: true, exactOptionalPropertyTypes: true, Section 5.2). CI fails the build on any type error (deno task check, Section 5.9).
• No any policy. no-explicit-any is an enforced lint rule (Section 5.2). The escape hatch is unknown plus an explicit narrowing function (a Zod .parse() call, a type predicate, or an exhaustive switch), never a type assertion (as SomeType) except at the single boundary where external, already-validated data enters the system (e.g. immediately after postgres.js returns a row whose shape is guaranteed by the migration that created the table).
• Error handling pattern. Domain code in core/ returns a typed Result<T, E> union ({ ok: true; value: T } | { ok: false; error: E }) for expected failure modes (validation failure, asset not found, format-detection failure) rather than throwing. Route handlers in web/ and command handlers in cli/ are the boundary: they call domain functions, inspect the Result, and either proceed or construct and throw an AppError (Section 25) carrying an SF- error code, which a single top-level error-handling middleware catches and converts into the error envelope (Section 16.4). Domain code never throws for an expected failure; it may throw only for a genuine programmer error (e.g. an assertion that should be unreachable).
• Async rules. Every async function that can fail is awaited inside a try/catch at the nearest boundary that can produce a typed Result or AppError; unhandled promise rejections are treated as a bug and fail CI (Section 5.9) via a global unhandledrejection listener in tests. Floating promises are caught by deno check's strict mode where possible and by the custom lint plugin described in Section 5.8; require-await is enabled via the recommended tag (Section 5.2).
• Typed-array reads under noUncheckedIndexedAccess. Every code block in this specification is illustrative: under Section 5.2's noUncheckedIndexedAccess, every array/typed-array index read has type T | undefined, so a shipped module must narrow each read (px[o] ?? 0) or open with // deno-lint-ignore-file plus a local const at = (a: Uint8Array, i: number) => a[i] as number; helper before the indexed reads compile as written.
• Logging rules. core/ and cli/ log through a single injected structured logger interface (never console.log directly in library code); web/ route handlers use the same interface, configured per Section 21.1's log schema. Log calls never include secrets (session tokens, TOTP codes, S3 credentials) — Section 20.4 lists the exact fields that must never appear in a log line.
• Import ordering. Three groups, blank line between each, enforced by deno fmt: (1) standard library and JSR imports, (2) npm imports, (3) local $core/, $web/, $cli/ imports. Within a group, alphabetical by specifier.
• Barrel-file policy. No barrel files (index.ts re-exporting an entire directory) inside core/; every module is imported by its direct path so bundlers and deno check can tree-shake and so circular-import risk stays visible. web/components/ and web/islands/ may use a single barrel file per top-level component group purely for import ergonomics in route files, never nested more than one level.

5.5 Fresh-specific conventions #

• Island vs. server component. A component is an island only if it needs client-side interactivity that cannot be expressed as a plain HTML form submission or a link — for example, the live composited canvas preview (Section 12.2), the hue swatch picker with keyboard navigation (Section 11.6), and the catalog's live filter-as-you-type (Section 15.3). Anything that only displays server-computed data, or that only needs a plain <form method="post">, is a server component, never an island.
• Props serialization limits. Island props must be JSON-serializable (no functions, no class instances, no Map/Set — plain objects, arrays, strings, numbers, booleans, null) because Fresh serializes island props into the initial HTML payload for hydration; any richer client-side state is constructed inside the island from those primitive props using signals (Section 4.1), never passed in pre-constructed.
• Signals usage. Each independently-changing piece of designer state (Section 11.2: body, skin hue, and one entry per occupied slot) is its own signal, not one large object signal, so a change to one slot does not trigger a recompute of unrelated slot UI; the composite canvas subscribes to a computed signal that depends on all occupied-slot signals and re-renders only when that computed value changes.
• Partials/streaming usage. Fresh partials (out-of-band HTML swaps) are used for the catalog's filter results (Section 15.3) so filtering re-renders only the results grid, not the full page; streaming responses are used for the admin console's import-run progress view (Section 17.5) so long-running extraction stages show live progress without client-side polling.
• Progressive enhancement. The designer requires JavaScript; its no-JS behaviour is the read-only fallback in Section 10.7. Catalog browsing and filtering work with plain link/GET-form navigation when JavaScript is unavailable, falling back from partial-swap to full page loads.

5.6 SQL conventions #

• Migration file naming and immutability. db/migrations/NNNN_snake_case_description.sql, four- digit zero-padded sequence number, strictly increasing, never renumbered. Once merged, a migration file is never edited; a mistake is corrected by a new, later migration, never by amending history.
• Up-only policy with explicit rollback scripts. Each migration file contains only the forward ("up") change. A corresponding db/migrations/NNNN_snake_case_description.down.sql file is written alongside it for local development rollback convenience; the down script is never executed automatically in any deployed environment (Section 23 has no automatic-rollback step) — recovery in production goes through the restore runbook in Section 23.6, not a down migration.
• Index naming: <table>_<column_or_columns>_idx (Section 5.3). Every foreign key column gets an index unless the migration comment explicitly justifies why not (e.g. a column that is always queried together with a more selective leading column in a composite index).
• Constraint naming: pk_<table>, fk_<table>_<column>, uq_<table>_<column_or_columns>, ck_<table>_<short_description> (Section 5.3).
• Enum-vs-lookup-table policy. A fixed, small, code-referenced set of values (e.g. a job's status) uses a Postgres CHECK constraint against a literal string list, not a native ENUM type, so adding a value is a new migration that alters the constraint rather than a harder-to-manage ALTER TYPE. A set of values that grows through normal application/admin activity (e.g. tags, hue groups) is a real lookup table with a foreign key, never a CHECK-constrained string column.
• Transaction boundaries. Every multi-statement write that must be atomic (e.g. the design short-code collision check plus insert, Section 13.4.2; an import-run's candidate promotion, Section 9.4) is wrapped in a single postgres.js transaction (sql.begin(...)). Read-only multi-statement operations do not open a transaction unless they require a consistent snapshot across statements.

5.7 Code documentation #

• JSDoc on every exported symbol in core/. Every exported function, type, and class in core/ carries a JSDoc comment stating its purpose, parameters, return value, and any error Result variants it can produce. web/ and cli/ code is documented where non-obvious but does not require exhaustive JSDoc, since route handlers and CLI commands are thin wrappers over documented core/ functions.
• Module headers. Every file in core/ begins with a one-paragraph comment stating what the module owns and which specification section it implements (e.g. // Implements the hue application algorithm from Section 8.3.), so a reader can trace code back to the specification section that defines its behavior.
• ADR file convention. Architectural decisions made during actual implementation (not already captured by Section 4.9's alternatives-considered table) are recorded by the developer in their own repository at docs/adr/NNNN-title.md, numbered sequentially, using a short "Context / Decision / Consequences" format. This is a convention for the developer's own repository, not a deliverable of this specification.

5.8 Accessibility and i18n conventions #

Every component, island or server-rendered, follows the WCAG 2.2 AA rules detailed in full in Section 18: semantic HTML elements before ARIA roles, visible focus states on every interactive element, hue swatches identified by name (not color alone) for colorblind users, minimum touch target sizes, and color-contrast minimums for all text. Every user-facing string is imported from core/i18n/en.ts (Section 5.1) rather than inlined in a component, so a component never hard-codes English text directly — this is what keeps the locale decision (Section 1 D-17) a data change rather than a refactor, and it is enforced by a project lint plugin at tools/lint-plugin.ts, registered in deno.json as "lint": { "plugins": ["./tools/lint-plugin.ts"] } (Section 5.2), which flags any JSX text node containing a non-whitespace string literal and any promise-returning expression used as a statement.

5.9 Definition of done #

A unit of work (a pull request implementing one feature, fix, or section of this specification) is done only when all of the following hold:

1. Types, lint and format. deno task check passes with zero findings (it runs format check, lint, and type check).
2. Tests. New behavior has unit test coverage as <module>_test.ts beside the changed core/ or cli/ module (Section 5.1); any change to rendering, compositing, or hue math additionally has or updates a golden-image fixture in tests/golden/ (Section 22.3); any change to a public route or API endpoint has or updates an integration or e2e test (Section 22.4, Section 22.5). deno task test, deno task test:golden, and the relevant deno task test:e2e suite all pass.
3. Lint and format. deno task lint and deno fmt --check both pass with zero findings.
4. Migration. Any schema change ships as a new numbered migration file (Section 5.6) plus its .down.sql counterpart, and deno task db:migrate runs cleanly against a fresh database.
5. Documentation. New exported core/ symbols carry JSDoc (Section 5.7); any behavior that deviates from or extends this specification is recorded as a new ADR in the developer's own docs/adr/ (Section 5.7).
6. Changelog. An entry is added to CHANGELOG.md under an "Unreleased" heading, following Conventional Commits categorization (Added, Changed, Fixed, Removed).
7. CI green. The full CI workflow (Section 22.11) passes on the pull request before merge; no step is skipped or force-merged.

6. Data Model & Database Schema #

6.1 Entity-Relationship Overview #

All persistent state lives in PostgreSQL (the major version line is fixed in Section 4.1). The schema has five clusters: build provenance and import pipeline, catalog (slots/assets/hues/tags), designs and renders, admin/auth, and operational tables (jobs, rate limits, stats, settings, takedowns).

 game_builds ──< source_files ──< import_run_items >── import_runs
      │                                                      │
      │                                              extraction_candidates
      │
      ├──< assets ──< asset_variants ──< asset_images
      │       │            │
      │       │            └── build_id ── game_builds   (build-scoped, Section 6.11)
      │       │
      │       ├──< asset_tags >── tags
      │       │
      │       └── slot_key ── slots
      │
      ├──< hues ──< hue_group_members >── hue_groups
      │
      └──< designs ──< design_renders
                 │
                 ├──< og_images
                 └──< takedown_requests

 admin_users ──< admin_sessions
      │
      ├──< admin_recovery_codes
      ├──< admin_pending_mfa
      └──< audit_log (actor_id, nullable FK)

 admin_login_failures, app_settings, jobs, rate_limit_buckets, stat_counters,
 page_view_daily, schema_migrations                                    (standalone)

One-line purpose of every table in the schema:

6.2 Conventions Recap #

All identifiers are snake_case; every table except schema_migrations, slots, hue_group_members, asset_tags, rate_limit_buckets, stat_counters, page_view_daily and app_settings uses a CHAR(26) ULID surrogate primary key named id, with stable natural keys (asset_key, slot_key, short_code) used everywhere else in the system, per the SQL and naming conventions in Sections 5.3 and 5.6. A ULID is 26 characters of Crockford Base32 (0123456789ABCDEFGHJKMNPQRSTVWXYZ — no I, L, O or U), generated by the application, never by the database.

Timestamps are TIMESTAMPTZ named created_at / updated_at / published_at / retired_at; catalog rows are soft-retired via retired_at, never hard-deleted, so permalinks never break. No catalog or design table carries a deleted_at column: there is no soft-delete concept distinct from retirement, and no hard-delete path outside the takedown process in Section 20.8.

Index names use the suffix form <table>_<columns>_idx, matching Section 5.6's convention; unique constraints declared inline on a table use PostgreSQL's own generated <table>_<columns>_key names.

Status vocabularies are defined once, here, and every screen, API response and CLI message uses these exact strings:

6.3 schema_migrations #

Tracks applied migrations. Managed exclusively by the migration runner in Section 6.32; application code never writes to it.

Primary key: filename. No foreign keys. No additional indexes. Typical row count: tens (one per migration file, growing slowly over the project's life).

6.4 game_builds #

The build lifecycle root. Every catalog row and hue row belongs to exactly one build.

Primary key: id. Unique: label. Foreign keys: created_by → admin_users(id) ON DELETE SET NULL; published_by → admin_users(id) ON DELETE SET NULL. Check: status IN ('draft','in_review', 'published','archived','rolled_back').

build_hash is sha256 over the concatenation, in asset_key order, of every asset_variants.sha256 belonging to this build, followed by, in hue_index order, every hues.colors array belonging to this build. It is computed and written once inside the publish transaction (Section 9.5) and never changes thereafter, which is what makes it safe to use as the salt of the render cache key in Section 8.9. It is nullable because a draft or in_review build has no fingerprint yet; a designs row can only reference a build that has been published (Section 9.1), so every build a render resolves against always has one.

archived and rolled_back are both terminal, non-current states. archived means the build was superseded by a normal later publish; rolled_back means it was explicitly withdrawn by the rollback procedure in Section 9.6. The distinction exists solely so the builds screen and the audit trail can tell an ordinary supersession from a withdrawal.

CREATE UNIQUE INDEX game_builds_published_singleton
  ON game_builds ((1)) WHERE status = 'published';

This partial unique index enforces at most one published build at a time; publishing a new build must archive the previous one in the same transaction (Section 9.5). Typical row count: dozens over the product's lifetime (one per game patch re-import).

6.5 source_files #

Every file the discovery stage (Section 7.2.1, D1) found on the operator's client directory for a build. No file bytes are stored in the database; only metadata and provenance.

Primary key: id. Foreign key: build_id → game_builds(id) ON DELETE CASCADE. Unique: (build_id, relative_path).

CREATE INDEX source_files_build_type_idx
  ON source_files (build_id, file_type);
-- Supports the source-file list on the import detail screen (Section 17.5), filtered by classification.

Typical row count: 500–5,000 per build (client asset directories are large but bounded).

6.6 import_runs #

One row per execution of a pipeline stage (Section 7.2) against a build.

Primary key: id. Foreign keys: build_id → game_builds(id) ON DELETE CASCADE; triggered_by → admin_users(id) ON DELETE SET NULL. Check: stage and status constrained to the enumerations above.

The granularity is one row per stage, not one row per import. A screen that wants a single progress indicator for a whole build rolls up the latest row per stage for that build_id (Section 17.5); the stage identifiers used by every screen, every metric label and every log event are exactly the eight values in the stage CHECK above, never a D1…D6 abbreviation.

D1…D6 are the prose shorthand Section 7 uses when discussing the extraction pipeline as a narrative; they are never written to this column, never rendered in a UI and never used as a metric label. The mapping is fixed and total:

Eight stage values, six of which the extraction pipeline produces and two of which ingestion adds.

CREATE INDEX import_runs_build_stage_idx
  ON import_runs (build_id, stage, created_at DESC);
-- Lets the admin imports screen (Section 17.5) list the latest run per stage for a build.

Typical row count: 8–16 per build (one per stage, occasionally re-run).

6.7 import_run_items #

Per-item outcome inside an import run, used for progress reporting and failure triage.

Primary key: id. Foreign keys: import_run_id → import_runs(id) ON DELETE CASCADE; source_file_id → source_files(id) ON DELETE SET NULL. Check: status IN ('ok','skipped','failed', 'needs_operator_input').

CREATE INDEX import_run_items_run_status_idx
  ON import_run_items (import_run_id, status);
-- Supports the per-run failure/needs-input count shown in the admin imports detail screen (Section 17.5).

Typical row count: thousands per run (one row per file or extracted unit); the largest table in the pipeline cluster.

6.8 extraction_candidates #

A sprite or hue candidate proposed by classification (Section 7.6), awaiting or having received staff review.

Primary key: id. Foreign keys: import_run_id → import_runs(id) ON DELETE CASCADE; source_file_id → source_files(id) ON DELETE SET NULL; suggested_slot_key → slots(slot_key) ON DELETE SET NULL; reviewed_by → admin_users(id) ON DELETE SET NULL. Check: confidence BETWEEN 0 AND 1; suggested_body IN ('m','f') or null; status constrained to the enumeration above. Unique: (import_run_id, candidate_key), which is the conflict target every D3/D5 write uses so re-running a stage is idempotent (Section 9.9).

CREATE INDEX extraction_candidates_status_confidence_idx
  ON extraction_candidates (status, confidence DESC);
-- Drives the staff review queue (Section 17.6): pending items ordered by ascending confidence surface first.

Typical row count: thousands per build; the queue drains toward zero pending rows as staff work through it, but historical approved/rejected rows are retained for audit.

6.9 slots #

The fixed registry of designable slots from Section 3.3. Rows are seeded once (Section 6.31) and never change afterwards: z_order participates in the canonicalization that produces a design's short code (Section 13.3), so editing it would silently change the meaning of every existing permalink. The admin slots screen (Section 17.9) is therefore read-only with respect to z_order.

Primary key: slot_key. Unique: z_order; display_order. Check: gender_scope IN ('both','male', 'female').

There is no stored choosable column. The choosable field returned by the API (Section 16.8.1) is the derived value NOT is_required: exactly one slot (body) is required, and the other 18 — backpack included — are choosable. gender_scope is the single name and the single vocabulary for the gender rule; the API exposes it as genderScope with the same three values. There is no genderRule field and no male_only/female_only spelling anywhere in the system.

visible is a presentation switch and nothing more. visible = false hides a slot from the slot rail (Section 11.5), the catalog overview and slot pages (Sections 15.2–15.3), and the /api/v1/slots listing (Section 16.8.1). It never changes CHOOSABLE_SLOT_KEYS (Section 13.2.5), never invalidates or re-canonicalizes an existing design, and never affects the render path — a design that already carries an entry for a hidden slot still renders that slot, exactly as it does for a retired asset (Section 9.7). The registry is 19 rows whatever visible says; hiding a slot changes what a visitor is offered, never what a permalink means. owner toggles it on the admin slots screen (Section 17.9), which is also the only column on this table staff may write.

The unique constraint on z_order already creates the index the renderer and every catalog listing use to iterate slots in composite order, so no separate index is declared.

Fixed row count: 19.

6.10 assets #

The catalog entry for one wearable or cosmetic item.

Primary key: id. Unique: asset_key. Foreign keys: slot_key → slots(slot_key) ON DELETE RESTRICT; source_file_id → source_files(id) ON DELETE SET NULL; first_build_id → game_builds(id) ON DELETE RESTRICT; last_build_id → game_builds(id) ON DELETE RESTRICT. Check: assets_asset_key_shape, asset_key ~ '^[a-z0-9]+([-_.][a-z0-9]+)*$' AND length(asset_key) <= 128 — the same shape AssetKeySchema enforces at the API boundary (Section 13.2.5), applied at the database so the ingest path, which never passes through an API schema, cannot write a key that would later be interpolated into an object-storage path.

ON DELETE RESTRICT on slot_key and the build FKs prevents removing a slot or build that catalog rows still reference; the correct way to remove an asset from circulation is retired_at, never a row delete, and there is no deleted_at column on this table.

first_build_id/last_build_id are provenance only. Build scoping that the renderer depends on lives on asset_variants.build_id (Section 6.11); no lookup on the render path consults either of these two columns.

partial_hue is the asset-level default, not the value the renderer reads. D6 (Section 7.8) copies it into asset_variants.hue_mode when it creates a variant row, and the renderer resolves the mode from the variant, so a single asset can carry a corrected mode in a newer build without rewriting the older build's variant.

CREATE INDEX assets_slot_key_idx ON assets (slot_key, display_order) WHERE retired_at IS NULL;
-- The hot path: list active assets for one slot in the designer and catalog, already ordered.
CREATE INDEX assets_gump_id_idx ON assets (gump_id) WHERE gump_id IS NOT NULL;
-- Used by classification (Section 7.6) to detect duplicate gump ids across builds.
CREATE INDEX assets_search_tsv_idx ON assets USING GIN (search_tsv);
-- Serves the full-text half of Section 15.6's search.
CREATE INDEX assets_display_name_trgm_idx ON assets USING GIN (display_name gin_trgm_ops);
-- Serves the fuzzy/similarity half of Section 15.6's search.

Typical row count: 2,000–6,000 (cosmetic items across the 18 choosable slots, both genders where distinct).

6.11 asset_variants #

Per-body, per-build sprite geometry for one asset. An asset with identical art for both genders still gets two rows per build (Section 8.5 requires per-body offsets even when the pixels match).

Primary key: id. Unique: (asset_id, body, build_id). Foreign keys: asset_id → assets(id) ON DELETE CASCADE; build_id → game_builds(id) ON DELETE RESTRICT. Check: body IN ('m','f'); hue_mode IN ('full','partial'); width > 0 AND height > 0.

Why build_id is on this table. Permalink immutability is the product's core promise, and it is structural, not aspirational. A design pins designs.build_id at creation; the renderer resolves every asset and every hue inside that build. hues has always been build-scoped by (hue_index, build_id) (Section 6.13), and asset_variants is scoped the same way for the same reason: when a patch changes an asset's art, D6 inserts a new variant row for the new build instead of overwriting the old one, so every permalink created before the patch keeps resolving the bytes it was created against. Without this column the "Changed" bucket in Section 9.3 would have to overwrite a single row per (asset_id, body) and would silently re-render every historical design with the new art.

CREATE INDEX asset_variants_sha256_idx ON asset_variants (sha256);
-- Used by diffing (Section 9.3) to detect unchanged art across builds without pixel comparison.
CREATE INDEX asset_variants_build_idx ON asset_variants (build_id);
-- Serves the render path's build-scoped lookup and the per-build enumeration the publish
-- transaction uses to compute game_builds.build_hash (Section 9.5).

Typical row count: roughly double assets per build (most slots have both-gender variants; facial_hair has male only), and the table therefore grows once per build rather than being rewritten in place.

6.12 asset_images #

A rendered scale × format output file for one asset variant, produced by normalization (Section 7.8).

Primary key: id. Unique: (asset_variant_id, scale, format). Foreign key: asset_variant_id → asset_variants(id) ON DELETE CASCADE. Check: scale IN (1,2,3); format IN ('webp','png').

storage_key is deliberately not unique, and that is a direct consequence of build-scoped variants (Section 6.11). The key embeds the variant's content hash, so two builds whose art for an asset is byte-identical produce the same key and share one stored object — the common case, since most assets are unchanged by any given patch. Each build still gets its own asset_variants row and its own six asset_images rows, and those rows point at the shared object. A unique constraint here would reject the second build outright.

The same property is what stops a patch from overwriting history: because the key changes whenever the bytes change, D6 never writes new art over an object an older build's design still resolves. A (asset_variant_id, scale, format) pair is still unique, so a variant can never have two rows for the same scale and format.

Two formats and no others, and the CHECK is the enforcement: the pipeline produces encoded images only. There is no rgba format value, no raw-buffer object under catalog/, and no per-sprite mask or index sidecar anywhere in this system — the hue table index is derived from the sprite pixel itself (Section 8.3), so nothing needs to be stored beside the image. The renderer's input is the scale-1 PNG, catalog/:assetKey/:body/:hash12/1.png, decoded to RGBA in process (Section 8.2.1); PNG is lossless, so that decode reproduces exactly the buffer extraction wrote (Section 7.8). Decoded RGBA buffers do exist during import, but only as scratch files inside the operator's work directory (Section 7.5); they are never uploaded, never get an asset_images row, and never leave the machine running the pipeline.

CREATE INDEX asset_images_storage_key_idx ON asset_images (storage_key);
-- Resolves "which rows reference this object", used by the restore validation in Section 6.34.

Typical row count: 6× asset_variants (3 scales × 2 formats each); distinct stored objects are far fewer, since unchanged art is shared across every build that carries it.

6.13 hues #

One decoded 32-colour hue table, per the hue model in Section 3.6.

Primary key: id. Unique: (hue_index, build_id) — the same numeric hue index can be re-imported per build with a possibly different colour table. Foreign key: build_id → game_builds(id) ON DELETE RESTRICT. Check: source IN ('base','outlands','derived'); array_length(colors,1) = 32.

Because the unique key is (hue_index, build_id) and not hue_index alone, no query anywhere resolves a hue without a build_id. A lookup that filters on hue_index only returns one row per build and, if the caller takes the first, renders an old design with a newer colour table — precisely the failure Sections 9.8 and 13.7 exist to prevent. Section 19.7 states the same rule for the hot render-path query.

Hue index 0 (unhued) is never stored as a row; it is a reserved sentinel handled entirely in application code and Section 8.3.3's rendering algorithm, since it has no colour table to decode. Because there is no row, there is also no name: a design carrying skinHue: 0 is described as "default skin", never by a hue name (Section 12.10).

Who writes this table. hues rows are inserted by stage D6 (Sections 7.2.6 and 7.7), in the same run that writes assets, asset_variants and asset_images, and by nothing else. D4 decodes the hue tables into the work directory and D5 records a proposed group set on the candidate row, but neither touches this table — a hues row cannot exist before D6 runs, which is why hue_group_members (Section 6.15) is written by D6 as well. Staff edits on the hues admin screen (Section 17.8) update name, source and swatch_override_argb on existing rows; they never insert one.

CREATE INDEX hues_source_idx ON hues (source) WHERE retired_at IS NULL;
-- Powers the hue browser (Section 15.5) filtered by base/outlands/derived.

Typical row count: 3,000–4,000 active per build (UO's base hue space) plus Outlands custom hues.

6.14 hue_groups #

Curated groupings of hues for browsing and filtering.

Primary key: id. Unique: group_key. Fixed row count: 5 (seeded, Section 6.31).

A hue_group here is a curated UI taxonomy, not a file-format structure. The 8-hue records inside hues.mul described in Section 7.3.3 are hue blocks, a decoding detail that never becomes a row in this table. Anything joining on group membership joins hue_groups.group_key; there is no hue_group column on asset_variants or on any other table.

6.15 hue_group_members #

Many-to-many join between hues and hue_groups. A hue may belong to more than one group (e.g. an Outlands cosmetic hue tagged both skin and event).

Primary key: (hue_id, hue_group_id). Foreign keys: hue_id → hues(id) ON DELETE CASCADE; hue_group_id → hue_groups(id) ON DELETE CASCADE. Check: method IN ('manual','heuristic').

method is what lets the curation policy change without a schema change: the import path writes heuristic rows, staff edits write manual rows, and a staff edit is never overwritten by a later import because the import only touches rows it owns.

Membership rows are inserted by D6, never by D5. hue_id is a foreign key to hues.id, and Section 6.13 establishes that no hues row exists until D6 creates it, so a D5 write here could only ever violate the constraint. D5's contribution is the proposal: it records the group keys its rule tables selected on extraction_candidates.metadata.hueGroups, and D6 turns that proposal into rows at the moment the hue itself becomes real (Section 7.7).

CREATE INDEX hue_group_members_group_idx ON hue_group_members (hue_group_id);
-- Supports "list all hues in group X" for the hue picker (Section 11.6).

Typical row count: roughly equal to active hues (most hues belong to exactly one group).

6.16 tags #

Catalog facets for browse/search filtering (Sections 15.3 and 15.6).

Primary key: id. Unique: tag_key. Check: category IN ('style','era','event','source'). The API names this field tagKey and matches on the tag_key value; there is no separate short tag column. Typical row count: 20–60, curated by staff.

6.17 asset_tags #

Many-to-many join between assets and tags.

Primary key: (asset_id, tag_id). Foreign keys: asset_id → assets(id) ON DELETE CASCADE; tag_id → tags(id) ON DELETE CASCADE.

CREATE INDEX asset_tags_tag_idx ON asset_tags (tag_id);
-- Supports "list all assets with tag X" for catalog facet filtering (Section 15.3).

An insert or delete here also refreshes the owning asset's search_tsv through the trigger defined in Section 6.30, so tag names stay searchable without a rebuild step.

Typical row count: 2–6× assets (an asset commonly carries several tags).

6.18 designs #

An immutable saved combination, per the canonical design JSON in Section 13.2.

Primary key: id. Unique: short_code. Foreign key: build_id → game_builds(id) ON DELETE RESTRICT (a build must never be hard-deleted while designs reference it; see Section 6.33).

Checks: short_code ~ '^[0-9A-HJKMNP-TV-Z]{10}$' (exactly ten Crockford Base32 characters — the alphabet excludes I, L, O and U); salt IS NULL OR salt BETWEEN 1 AND 255.

salt is nullable and never 0, because "unsalted" and "salted with the byte 0x00" are two different hash inputs and would otherwise be indistinguishable on a stored row. Any code that re-derives a short code from canonical_json must append a salt byte if and only if salt IS NOT NULL.

canonical_json is TEXT, not JSONB, because Section 13.4.2's collision procedure compares the stored document byte-for-byte against a freshly canonicalized one. jsonb normalizes whitespace, reorders object keys and drops duplicates, so the value read back would never be the bytes that were hashed, and every re-share of an identical design would be misclassified as a true hash collision. The generated canonical_jsonb column exists so the queryable form is still available without sacrificing that guarantee.

taken_down_at is the only removal mechanism. Designs are never hard-deleted and there is no age-based or view-based pruning of this table; the reasoning and the storage-growth answer are in Section 6.33.

No free-text user-supplied field exists on this table by deliberate moderation decision: designs are combinations of catalog references only, never arbitrary strings, so there is no surface for abusive user text to appear on a permalink.

CREATE INDEX designs_build_id_idx ON designs (build_id);
-- Used by re-sync (Section 9.8) to find all designs pinned to a given build for hue/asset lookups.
CREATE INDEX designs_canonical_gin_idx ON designs USING GIN (canonical_jsonb jsonb_path_ops);
-- Serves Section 13.8's containment query on the permalink page without a sequential scan.
CREATE INDEX designs_created_at_idx ON designs (created_at DESC) WHERE taken_down_at IS NULL;
-- Serves the sitemap and the random-design endpoint (Sections 10.6 and 16.8.13).

Typical row count: unbounded growth, the largest table over time (every share action inserts one row); expect hundreds of thousands within the first year at moderate traffic.

6.19 design_renders #

Cache rows for server-composited images, per the cache-key scheme in Section 8.9. The table backs all three render URL shapes in Section 8.8, not only whole-design composites.

Primary key: id. Unique: cache_key; storage_key. Foreign key: design_id → designs(id) ON DELETE CASCADE. Checks: render_kind IN ('design','asset','swatch'); (render_kind = 'design') = (design_id IS NOT NULL); scale IN (1,2,3); format IN ('webp','png').

design_id is nullable because asset and swatch renders have no owning design. The paired CHECK makes the discriminator honest in both directions, so a design row can never be orphaned and an asset row can never smuggle in a design reference.

The column is named storage_key, matching asset_images and og_images; no section uses object_key.

CREATE INDEX design_renders_design_id_idx ON design_renders (design_id) WHERE design_id IS NOT NULL;
-- Enumerates all cached render variants for a design, used by the takedown path (Section 20.8).
CREATE INDEX design_renders_last_served_idx ON design_renders (last_served_at NULLS FIRST);
-- Serves the regenerable-artifact purge runbook (Section 23.8).

Typical row count: up to 6× designs (3 scales × 2 formats), in practice lower since most permalinks are only ever viewed at one scale/format combination, plus one row per cached asset/swatch render.

6.20 og_images #

Generated Open Graph preview images for a design (Section 14.5).

Primary key: id. Unique: cache_key; storage_key. Foreign key: design_id → designs(id) ON DELETE CASCADE.

There is deliberately no unique constraint on design_id: bumping the OG template version produces a second, differently-keyed row for the same design, and the old row stays until the pruning job removes it. Lookups are by cache_key.

CREATE INDEX og_images_design_idx ON og_images (design_id);
-- Enumerates every generated card for a design, used by the takedown path and the pruning job.

Typical row count: equal to the subset of designs that have been shared at least once and crawled, generated lazily per Section 14.5.

6.21 jobs #

The Postgres-backed background job queue (Section 4.7).

Primary key: id. Checks: job_type and status constrained to the enumerations above; attempts >= 0; max_attempts > 0.

There is no done status and no pending status. A finished job is succeeded; a job waiting to run is queued. Any write that sets another value violates the CHECK.

dedupe_key is what makes the render mutex in Section 8.10 work: a request that wants a render inserts a render job keyed on the render's cache_key with ON CONFLICT DO NOTHING, and the partial unique index below is the arbiter, so a thundering herd on a viral permalink produces exactly one render.

CREATE UNIQUE INDEX jobs_dedupe_key_idx ON jobs (dedupe_key)
  WHERE dedupe_key IS NOT NULL AND status IN ('queued','running');
-- The ON CONFLICT arbiter: one live job per dedupe key, freed as soon as the job reaches a terminal state.
CREATE INDEX jobs_claim_idx ON jobs (priority DESC, run_after)
  WHERE status = 'queued';
-- The claim query: SELECT ... WHERE status = 'queued' AND run_after <= now()
--   ORDER BY priority DESC, run_after LIMIT n FOR UPDATE SKIP LOCKED, using this partial index.

Typical row count: low thousands transient (queued/running), with completed rows pruned per Section 6.33's retention policy.

6.22 admin_users #

Staff accounts.

Primary key: id. Unique: email. Check: role IN ('viewer','curator','owner').

There are exactly three roles, ordered viewer < curator < owner; the per-action permission matrix that gives them meaning is Section 17.3.1, and this table stores nothing beyond the role name. An account is active when disabled_at IS NULL; there is no separate is_active flag, so the two can never disagree.

Rows are created only by the skinforge-cli admin user create command (Section 17.15); there is no public self-signup and no admin-console endpoint that creates a user without going through the CLI. Typical row count: under 20.

6.23 admin_sessions #

Server-side sessions backing the sf_admin cookie.

Primary key: id. Unique: session_token_hash. Foreign key: admin_user_id → admin_users(id) ON DELETE CASCADE.

CREATE INDEX admin_sessions_user_active_idx
  ON admin_sessions (admin_user_id) WHERE revoked_at IS NULL;
-- Supports "list my active sessions" and bulk revoke-all-sessions-for-user.

Typical row count: low, one to a few per active staff member; expired/revoked rows pruned per Section 6.33.

6.24 admin_recovery_codes #

One-time TOTP recovery codes.

Primary key: id. Foreign key: admin_user_id → admin_users(id) ON DELETE CASCADE. Ten rows are generated per admin user at account creation and at explicit staff-initiated regeneration.

The hash is SHA-256, matching Section 17.2.2, not a slow KDF: a recovery code carries 50 bits of entropy, verification costs ten hashes per attempt, and the login-failure lockout in Section 17.2.3 bounds attempts anyway.

6.25 audit_log #

Append-only record of every state-changing admin or pipeline action.

Primary key: id. Foreign key: actor_id → admin_users(id) ON DELETE SET NULL (the row survives account deletion via the denormalized actor_label).

CREATE INDEX audit_log_entity_idx ON audit_log (entity_type, entity_id, created_at DESC);
-- Powers "show history for this entity" on every admin detail screen (Section 17.13).
CREATE INDEX audit_log_actor_idx ON audit_log (actor_id, created_at DESC);
-- Powers "show this staff member's recent actions" (Section 17.13).

Typical row count: unbounded, append-only; the second-largest table over time after designs. Rows are retained indefinitely and never pruned (Section 6.33).

6.26 rate_limit_buckets #

Token-bucket state for the rate limits in Section 16.7.

Primary key: bucket_key. No foreign keys. Rows are upserted on every rate-limited request; stale rows (untouched for 24 hours) are pruned by a scheduled job (Section 6.33). Typical row count: proportional to distinct recent client IPs, low thousands under normal traffic.

6.27 stat_counters #

Aggregate counters computed server-side, no PII.

Primary key: (counter_key, day). This composite key is the conflict target every increment uses: INSERT INTO stat_counters (counter_key, day, value) VALUES ($1, current_date, 1) ON CONFLICT (counter_key, day) DO UPDATE SET value = stat_counters.value + 1, updated_at = now();.

Two shapes share the table. Per-day counters use the real UTC date and are pruned on the same 400-day window as page_view_daily (Section 6.33). All-time totals use the sentinel day 0001-01-01, are never pruned, and are the handful of counters the public stats endpoint reads. Row count is therefore bounded by (named counters × retained days) plus one sentinel row per counter.

6.28 page_view_daily #

Daily page-view aggregate per path, written server-side at request time, no cookies, no PII (Section 21.6).

Primary key: (day, path). No foreign keys.

CREATE INDEX page_view_daily_day_idx ON page_view_daily (day);
-- Supports the admin dashboard's traffic-by-day rollup (Section 17.4).

Typical row count: distinct paths × days; bounded by pruning rows older than the retention window in Section 6.33.

6.29 takedown_requests #

Legal/rights takedown requests (Section 20.8).

Primary key: id. Foreign keys: design_id → designs(id) ON DELETE SET NULL; asset_id → assets(id) ON DELETE SET NULL; handled_by → admin_users(id) ON DELETE SET NULL. Check: status IN ('new','triaging','upheld','rejected','withdrawn').

upheld is the only status that changes anything on the public site: it sets designs.taken_down_at (or retires the asset), after which every surface serves 410 per Section 20.8. Typical row count: low, expected to stay under a few hundred for the life of the product.

Three further tables — admin_pending_mfa, admin_login_failures and app_settings — are specified in Sections 6.35, 6.36 and 6.37; they appear in the DDL below in dependency order alongside the rest of the schema.

6.30 Full DDL — db/migrations/0001_init.sql #

The file contains no BEGIN;/COMMIT;. The migration runner (Section 6.32) opens and commits one transaction per file; a file that opened its own would leave the runner's bookkeeping insert outside it.

-- 0001_init.sql
-- SkinForge initial schema. Applied by the Deno migration runner (Section 6.32).
-- The runner owns the transaction; this file must not contain BEGIN or COMMIT.

-- pg_trgm backs the similarity half of catalog search (Section 15.6). Creating an extension
-- requires a superuser or a role with rds_superuser-equivalent rights; the first-time
-- provisioning runbook (Section 23.4) grants it before the first migration runs.
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE schema_migrations (
  filename    TEXT PRIMARY KEY,
  checksum    CHAR(64) NOT NULL,
  applied_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

CREATE TABLE admin_users (
  id              CHAR(26) PRIMARY KEY,
  email           TEXT NOT NULL UNIQUE,
  password_hash   TEXT NOT NULL,
  totp_secret     TEXT NOT NULL,
  role            TEXT NOT NULL DEFAULT 'viewer' CHECK (role IN ('viewer','curator','owner')),
  disabled_at     TIMESTAMPTZ,
  last_login_at   TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TRIGGER admin_users_set_updated_at
  BEFORE UPDATE ON admin_users FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE admin_sessions (
  id                  CHAR(26) PRIMARY KEY,
  admin_user_id       CHAR(26) NOT NULL REFERENCES admin_users(id) ON DELETE CASCADE,
  session_token_hash  CHAR(64) NOT NULL UNIQUE,
  expires_at          TIMESTAMPTZ NOT NULL,
  last_seen_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  revoked_at          TIMESTAMPTZ,
  ip_address          INET,
  user_agent          TEXT,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX admin_sessions_user_active_idx
  ON admin_sessions (admin_user_id) WHERE revoked_at IS NULL;

CREATE TABLE admin_recovery_codes (
  id             CHAR(26) PRIMARY KEY,
  admin_user_id  CHAR(26) NOT NULL REFERENCES admin_users(id) ON DELETE CASCADE,
  code_hash      CHAR(64) NOT NULL,
  used_at        TIMESTAMPTZ,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE admin_pending_mfa (
  id             CHAR(26) PRIMARY KEY,
  admin_user_id  CHAR(26) NOT NULL REFERENCES admin_users(id) ON DELETE CASCADE,
  token_hash     CHAR(64) NOT NULL UNIQUE,
  attempts       SMALLINT NOT NULL DEFAULT 0 CHECK (attempts >= 0),
  expires_at     TIMESTAMPTZ NOT NULL,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE admin_login_failures (
  id               CHAR(26) PRIMARY KEY,
  admin_user_id    CHAR(26) REFERENCES admin_users(id) ON DELETE CASCADE,
  email_attempted  TEXT NOT NULL,
  ip_address       INET,
  phase            TEXT NOT NULL CHECK (phase IN ('password','totp','recovery_code')),
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX admin_login_failures_email_time_idx
  ON admin_login_failures (email_attempted, created_at DESC);
CREATE INDEX admin_login_failures_ip_time_idx
  ON admin_login_failures (ip_address, created_at DESC);

CREATE TABLE app_settings (
  setting_key  TEXT PRIMARY KEY
                 CHECK (setting_key IN ('admin_ip_allowlist','maintenance_mode',
                                        'takedown_response_template','featured_designs',
                                        'designs_like_this_threshold','reimport_reminder_cadence_days')),
  value        JSONB NOT NULL,
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by   CHAR(26) REFERENCES admin_users(id) ON DELETE SET NULL
);

CREATE TABLE audit_log (
  id           CHAR(26) PRIMARY KEY,
  actor_id     CHAR(26) REFERENCES admin_users(id) ON DELETE SET NULL,
  actor_label  TEXT NOT NULL,
  action       TEXT NOT NULL,
  entity_type  TEXT NOT NULL,
  entity_id    TEXT,
  before       JSONB,
  after        JSONB,
  reason       TEXT,
  ip_address   INET,
  user_agent   TEXT,
  metadata     JSONB NOT NULL DEFAULT '{}',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX audit_log_entity_idx ON audit_log (entity_type, entity_id, created_at DESC);
CREATE INDEX audit_log_actor_idx ON audit_log (actor_id, created_at DESC);

CREATE TABLE game_builds (
  id                      CHAR(26) PRIMARY KEY,
  label                   TEXT NOT NULL UNIQUE,
  status                  TEXT NOT NULL DEFAULT 'draft'
                            CHECK (status IN ('draft','in_review','published','archived','rolled_back')),
  build_hash              CHAR(64),
  source_client_version   TEXT,
  notes                   TEXT,
  created_by              CHAR(26) REFERENCES admin_users(id) ON DELETE SET NULL,
  published_at            TIMESTAMPTZ,
  published_by            CHAR(26) REFERENCES admin_users(id) ON DELETE SET NULL,
  archived_at             TIMESTAMPTZ,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX game_builds_published_singleton
  ON game_builds ((1)) WHERE status = 'published';
CREATE TRIGGER game_builds_set_updated_at
  BEFORE UPDATE ON game_builds FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE source_files (
  id              CHAR(26) PRIMARY KEY,
  build_id        CHAR(26) NOT NULL REFERENCES game_builds(id) ON DELETE CASCADE,
  relative_path   TEXT NOT NULL,
  file_type       TEXT NOT NULL
                    CHECK (file_type IN ('mul','idx','uop','def','txt','image','archive','unknown')),
  size_bytes      BIGINT NOT NULL,
  sha256          CHAR(64) NOT NULL,
  magic_bytes     TEXT,
  entropy         REAL,
  discovered_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (build_id, relative_path)
);
CREATE INDEX source_files_build_type_idx ON source_files (build_id, file_type);

CREATE TABLE import_runs (
  id            CHAR(26) PRIMARY KEY,
  build_id      CHAR(26) NOT NULL REFERENCES game_builds(id) ON DELETE CASCADE,
  stage         TEXT NOT NULL
                  CHECK (stage IN ('inventory','identify','probe','extract','classify','normalize',
                                    'import','verify')),
  status        TEXT NOT NULL DEFAULT 'queued'
                  CHECK (status IN ('queued','running','succeeded','failed','cancelled')),
  started_at    TIMESTAMPTZ,
  finished_at   TIMESTAMPTZ,
  triggered_by  CHAR(26) REFERENCES admin_users(id) ON DELETE SET NULL,
  summary       JSONB NOT NULL DEFAULT '{}',
  error         TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX import_runs_build_stage_idx ON import_runs (build_id, stage, created_at DESC);

CREATE TABLE import_run_items (
  id               CHAR(26) PRIMARY KEY,
  import_run_id    CHAR(26) NOT NULL REFERENCES import_runs(id) ON DELETE CASCADE,
  source_file_id   CHAR(26) REFERENCES source_files(id) ON DELETE SET NULL,
  item_type        TEXT NOT NULL,
  status           TEXT NOT NULL CHECK (status IN ('ok','skipped','failed','needs_operator_input')),
  detail           JSONB NOT NULL DEFAULT '{}',
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX import_run_items_run_status_idx ON import_run_items (import_run_id, status);

CREATE TABLE slots (
  slot_key            TEXT PRIMARY KEY,
  display_name        TEXT NOT NULL,
  z_order             INT NOT NULL UNIQUE,
  display_order       INT NOT NULL UNIQUE,
  uo_layer            INT,
  hueable             BOOLEAN NOT NULL DEFAULT true,
  gender_scope        TEXT NOT NULL DEFAULT 'both' CHECK (gender_scope IN ('both','male','female')),
  is_required         BOOLEAN NOT NULL DEFAULT false,
  is_always_visible   BOOLEAN NOT NULL DEFAULT false,
  visible             BOOLEAN NOT NULL DEFAULT true,
  hue_swatch_crop     TEXT,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE assets (
  id               CHAR(26) PRIMARY KEY,
  asset_key        TEXT NOT NULL UNIQUE,
  slot_key         TEXT NOT NULL REFERENCES slots(slot_key) ON DELETE RESTRICT,
  display_name     TEXT NOT NULL,
  display_order    INT NOT NULL DEFAULT 0,
  source_file_id   CHAR(26) REFERENCES source_files(id) ON DELETE SET NULL,
  source_offset    BIGINT,
  gump_id          INT,
  partial_hue      BOOLEAN NOT NULL DEFAULT false,
  search_tsv       tsvector,
  first_build_id   CHAR(26) NOT NULL REFERENCES game_builds(id) ON DELETE RESTRICT,
  last_build_id    CHAR(26) NOT NULL REFERENCES game_builds(id) ON DELETE RESTRICT,
  retired_at       TIMESTAMPTZ,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT assets_asset_key_shape
    CHECK (asset_key ~ '^[a-z0-9]+([-_.][a-z0-9]+)*$' AND length(asset_key) <= 128)
);
CREATE INDEX assets_slot_key_idx ON assets (slot_key, display_order) WHERE retired_at IS NULL;
CREATE INDEX assets_gump_id_idx ON assets (gump_id) WHERE gump_id IS NOT NULL;
CREATE INDEX assets_search_tsv_idx ON assets USING GIN (search_tsv);
CREATE INDEX assets_display_name_trgm_idx ON assets USING GIN (display_name gin_trgm_ops);
CREATE TRIGGER assets_set_updated_at
  BEFORE UPDATE ON assets FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE extraction_candidates (
  id                   CHAR(26) PRIMARY KEY,
  import_run_id        CHAR(26) NOT NULL REFERENCES import_runs(id) ON DELETE CASCADE,
  source_file_id       CHAR(26) REFERENCES source_files(id) ON DELETE SET NULL,
  candidate_key        TEXT NOT NULL,
  suggested_slot_key   TEXT REFERENCES slots(slot_key) ON DELETE SET NULL,
  suggested_body       TEXT CHECK (suggested_body IN ('m','f')),
  confidence           REAL NOT NULL DEFAULT 0 CHECK (confidence BETWEEN 0 AND 1),
  status               TEXT NOT NULL DEFAULT 'pending'
                         CHECK (status IN ('pending','needs_operator_input','approved','rejected')),
  image_ref            TEXT,
  metadata             JSONB NOT NULL DEFAULT '{}',
  reviewed_by          CHAR(26) REFERENCES admin_users(id) ON DELETE SET NULL,
  reviewed_at          TIMESTAMPTZ,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (import_run_id, candidate_key)
);
CREATE INDEX extraction_candidates_status_confidence_idx
  ON extraction_candidates (status, confidence DESC);

CREATE TABLE asset_variants (
  id           CHAR(26) PRIMARY KEY,
  asset_id     CHAR(26) NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
  build_id     CHAR(26) NOT NULL REFERENCES game_builds(id) ON DELETE RESTRICT,
  body         TEXT NOT NULL CHECK (body IN ('m','f')),
  width        INT NOT NULL CHECK (width > 0),
  height       INT NOT NULL CHECK (height > 0),
  offset_x     INT NOT NULL,
  offset_y     INT NOT NULL,
  hue_mode     TEXT NOT NULL DEFAULT 'full' CHECK (hue_mode IN ('full','partial')),
  sha256       CHAR(64) NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (asset_id, body, build_id)
);
CREATE INDEX asset_variants_sha256_idx ON asset_variants (sha256);
CREATE INDEX asset_variants_build_idx ON asset_variants (build_id);

CREATE TABLE asset_images (
  id                  CHAR(26) PRIMARY KEY,
  asset_variant_id    CHAR(26) NOT NULL REFERENCES asset_variants(id) ON DELETE CASCADE,
  scale               SMALLINT NOT NULL CHECK (scale IN (1,2,3)),
  format              TEXT NOT NULL CHECK (format IN ('webp','png')),
  storage_key         TEXT NOT NULL,
  byte_size           INT NOT NULL,
  content_hash        CHAR(64) NOT NULL,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (asset_variant_id, scale, format)
);
CREATE INDEX asset_images_storage_key_idx ON asset_images (storage_key);

CREATE TABLE hues (
  id                    CHAR(26) PRIMARY KEY,
  hue_index             INT NOT NULL,
  source                TEXT NOT NULL CHECK (source IN ('base','outlands','derived')),
  name                  TEXT NOT NULL,
  colors                INTEGER[32] NOT NULL CHECK (array_length(colors,1) = 32),
  table_start           INT NOT NULL,
  table_end             INT NOT NULL,
  swatch_color          INTEGER NOT NULL,
  swatch_override_argb  INTEGER,
  partial               BOOLEAN NOT NULL DEFAULT false,
  build_id              CHAR(26) NOT NULL REFERENCES game_builds(id) ON DELETE RESTRICT,
  retired_at            TIMESTAMPTZ,
  created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (hue_index, build_id)
);
CREATE INDEX hues_source_idx ON hues (source) WHERE retired_at IS NULL;

CREATE TABLE hue_groups (
  id            CHAR(26) PRIMARY KEY,
  group_key     TEXT NOT NULL UNIQUE,
  display_name  TEXT NOT NULL,
  sort_order    INT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE hue_group_members (
  hue_id        CHAR(26) NOT NULL REFERENCES hues(id) ON DELETE CASCADE,
  hue_group_id  CHAR(26) NOT NULL REFERENCES hue_groups(id) ON DELETE CASCADE,
  method        TEXT NOT NULL DEFAULT 'manual' CHECK (method IN ('manual','heuristic')),
  assigned_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (hue_id, hue_group_id)
);
CREATE INDEX hue_group_members_group_idx ON hue_group_members (hue_group_id);

CREATE TABLE tags (
  id             CHAR(26) PRIMARY KEY,
  tag_key        TEXT NOT NULL UNIQUE,
  display_name   TEXT NOT NULL,
  category       TEXT NOT NULL CHECK (category IN ('style','era','event','source')),
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE asset_tags (
  asset_id  CHAR(26) NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
  tag_id    CHAR(26) NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (asset_id, tag_id)
);
CREATE INDEX asset_tags_tag_idx ON asset_tags (tag_id);

-- Full-text search vector, maintained by trigger. A GENERATED column cannot be used here:
-- PostgreSQL rejects a generation expression containing a subquery or a cross-table reference,
-- and the vector must include tag names from asset_tags.
--
-- These two functions and three triggers are defined here and nowhere else. Section 15.6 owns the
-- *query* that reads assets.search_tsv and the weighting rationale; it does not redeclare the
-- trigger. Both triggers fire AFTER, not BEFORE: the refresh function re-reads the row through a
-- sub-select, so on BEFORE INSERT the row would not yet be visible and every newly imported asset
-- would land with a NULL vector. The refresh writes only search_tsv, while the assets trigger is
-- scoped to UPDATE OF display_name, asset_key -- so the write cannot re-fire the trigger and the
-- recursion terminates after one pass.
-- tags columns are (id, tag_key, display_name, category, created_at): the vector reads
-- tags.display_name. There is no tags.name and no tags.slug.
CREATE FUNCTION assets_search_tsv_refresh(target_asset_id CHAR(26)) RETURNS void
LANGUAGE sql AS $$
  UPDATE assets a
     SET search_tsv =
           setweight(to_tsvector('english', coalesce(a.display_name, '')), 'A')
        || setweight(to_tsvector('english', replace(coalesce(a.asset_key, ''), '.', ' ')), 'B')
        || setweight(to_tsvector('english', coalesce((
             SELECT string_agg(t.display_name, ' ')
               FROM asset_tags xt
               JOIN tags t ON t.id = xt.tag_id
              WHERE xt.asset_id = a.id), '')), 'C')
   WHERE a.id = target_asset_id;
$$;

CREATE FUNCTION assets_search_tsv_trigger() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  PERFORM assets_search_tsv_refresh(NEW.id);
  RETURN NULL;
END;
$$;
CREATE TRIGGER assets_search_tsv_aiu
  AFTER INSERT OR UPDATE OF display_name, asset_key ON assets
  FOR EACH ROW EXECUTE FUNCTION assets_search_tsv_trigger();

CREATE FUNCTION asset_tags_search_tsv_trigger() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  -- NEW is unassigned in a DELETE trigger, so it must not be referenced there at all.
  IF TG_OP = 'DELETE' THEN
    PERFORM assets_search_tsv_refresh(OLD.asset_id);
  ELSE
    PERFORM assets_search_tsv_refresh(NEW.asset_id);
  END IF;
  RETURN NULL;
END;
$$;
CREATE TRIGGER asset_tags_search_tsv_aid
  AFTER INSERT OR DELETE ON asset_tags
  FOR EACH ROW EXECUTE FUNCTION asset_tags_search_tsv_trigger();

CREATE TABLE designs (
  id               CHAR(26) PRIMARY KEY,
  short_code       TEXT NOT NULL UNIQUE
                     CHECK (short_code ~ '^[0-9A-HJKMNP-TV-Z]{10}$'),
  salt             SMALLINT CHECK (salt IS NULL OR salt BETWEEN 1 AND 255),
  canonical_json   TEXT NOT NULL,
  canonical_jsonb  JSONB NOT NULL GENERATED ALWAYS AS (canonical_json::jsonb) STORED,
  build_id         CHAR(26) NOT NULL REFERENCES game_builds(id) ON DELETE RESTRICT,
  view_count       BIGINT NOT NULL DEFAULT 0,
  render_count     BIGINT NOT NULL DEFAULT 0,
  taken_down_at    TIMESTAMPTZ,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX designs_build_id_idx ON designs (build_id);
CREATE INDEX designs_canonical_gin_idx ON designs USING GIN (canonical_jsonb jsonb_path_ops);
CREATE INDEX designs_created_at_idx ON designs (created_at DESC) WHERE taken_down_at IS NULL;

CREATE TABLE design_renders (
  id               CHAR(26) PRIMARY KEY,
  render_kind      TEXT NOT NULL CHECK (render_kind IN ('design','asset','swatch')),
  design_id        CHAR(26) REFERENCES designs(id) ON DELETE CASCADE,
  subject_ref      TEXT NOT NULL,
  cache_key        CHAR(64) NOT NULL UNIQUE,
  scale            SMALLINT NOT NULL CHECK (scale IN (1,2,3)),
  format           TEXT NOT NULL CHECK (format IN ('webp','png')),
  storage_key      TEXT NOT NULL UNIQUE,
  byte_size        INT NOT NULL,
  content_hash     CHAR(64) NOT NULL,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_served_at   TIMESTAMPTZ,
  CONSTRAINT design_renders_kind_ref
    CHECK ((render_kind = 'design') = (design_id IS NOT NULL))
);
CREATE INDEX design_renders_design_id_idx ON design_renders (design_id) WHERE design_id IS NOT NULL;
CREATE INDEX design_renders_last_served_idx ON design_renders (last_served_at NULLS FIRST);

CREATE TABLE og_images (
  id               CHAR(26) PRIMARY KEY,
  design_id        CHAR(26) NOT NULL REFERENCES designs(id) ON DELETE CASCADE,
  cache_key        CHAR(64) NOT NULL UNIQUE,
  storage_key      TEXT NOT NULL UNIQUE,
  byte_size        INT NOT NULL,
  content_hash     CHAR(64) NOT NULL,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_served_at   TIMESTAMPTZ
);
CREATE INDEX og_images_design_idx ON og_images (design_id);

CREATE TABLE jobs (
  id             CHAR(26) PRIMARY KEY,
  job_type       TEXT NOT NULL
                   CHECK (job_type IN ('render','og_render','import_stage','stat_rollup',
                                       'notify','reimport_reminder','prune')),
  payload        JSONB NOT NULL DEFAULT '{}',
  dedupe_key     TEXT,
  priority       SMALLINT NOT NULL DEFAULT 0,
  status         TEXT NOT NULL DEFAULT 'queued'
                   CHECK (status IN ('queued','running','succeeded','failed','cancelled')),
  run_after      TIMESTAMPTZ NOT NULL DEFAULT now(),
  attempts       INT NOT NULL DEFAULT 0 CHECK (attempts >= 0),
  max_attempts   INT NOT NULL DEFAULT 5 CHECK (max_attempts > 0),
  claimed_at     TIMESTAMPTZ,
  claimed_by     TEXT,
  last_error     TEXT,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX jobs_dedupe_key_idx ON jobs (dedupe_key)
  WHERE dedupe_key IS NOT NULL AND status IN ('queued','running');
CREATE INDEX jobs_claim_idx ON jobs (priority DESC, run_after) WHERE status = 'queued';
CREATE TRIGGER jobs_set_updated_at
  BEFORE UPDATE ON jobs FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE rate_limit_buckets (
  bucket_key   TEXT PRIMARY KEY,
  tokens       REAL NOT NULL,
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE stat_counters (
  counter_key  TEXT NOT NULL,
  day          DATE NOT NULL,
  value        BIGINT NOT NULL DEFAULT 0,
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (counter_key, day)
);

CREATE TABLE page_view_daily (
  day     DATE NOT NULL,
  path    TEXT NOT NULL,
  views   BIGINT NOT NULL DEFAULT 0,
  PRIMARY KEY (day, path)
);
CREATE INDEX page_view_daily_day_idx ON page_view_daily (day);

CREATE TABLE takedown_requests (
  id                 CHAR(26) PRIMARY KEY,
  design_id          CHAR(26) REFERENCES designs(id) ON DELETE SET NULL,
  asset_id           CHAR(26) REFERENCES assets(id) ON DELETE SET NULL,
  requester_email    TEXT NOT NULL,
  reason             TEXT NOT NULL,
  status             TEXT NOT NULL DEFAULT 'new'
                       CHECK (status IN ('new','triaging','upheld','rejected','withdrawn')),
  resolution_note    TEXT,
  handled_by         CHAR(26) REFERENCES admin_users(id) ON DELETE SET NULL,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  resolved_at        TIMESTAMPTZ
);

Cascade discipline. Two rules govern every foreign key above, and they are deliberately opposite:

• Rows a permalink depends on use ON DELETE RESTRICT — designs.build_id, asset_variants.build_id, hues.build_id, assets.first_build_id/last_build_id, assets.slot_key. A build or slot that designs still reference cannot be deleted at all.
• Rows that are regenerable derived artifacts use ON DELETE CASCADE — design_renders.design_id, og_images.design_id, asset_images.asset_variant_id. This is what lets the artifact-pruning runbook in Section 23.8 delete cached renders and OG cards without a manual cleanup step, and it is safe precisely because those rows can always be regenerated from the design and its pinned build.

6.31 Seed Data #

Seed data is applied by db/migrations/0002_seed.sql, which — like every migration file — contains no BEGIN;/COMMIT;. It runs in every environment (production, staging, dev) except the dev-only fixture build, which is gated behind SKINFORGE_ENV = 'development' at the application layer, not inside the SQL file — the migration runner applies every file unconditionally, and Section 6.32 explains why fixture data is instead seeded by a separate skinforge-cli db fixtures command, never a numbered migration.

Every literal id below is a valid 26-character Crockford Base32 ULID: the alphabet is 0123456789ABCDEFGHJKMNPQRSTVWXYZ, with I, L, O and U excluded so a hand-transcribed id cannot be confused with 1 or 0.

-- 0002_seed.sql
-- The runner owns the transaction; this file must not contain BEGIN or COMMIT.

INSERT INTO slots (slot_key, display_name, z_order, display_order, uo_layer, hueable, gender_scope, is_required, is_always_visible, visible) VALUES
  ('body',          'Body',              10,  10,  NULL, true,  'both',   true,  false, true),
  ('tattoo_body',   'Body Tattoo',       20,  20,  NULL, true,  'both',   false, false, true),
  ('footwear',      'Footwear',          30,  30,  3,    true,  'both',   false, false, true),
  ('legs_inner',    'Pants',             40,  40,  4,    true,  'both',   false, false, true),
  ('torso_inner',   'Shirt',             50,  50,  5,    true,  'both',   false, false, true),
  ('torso_middle',  'Chest Piece',       60,  60,  17,   true,  'both',   false, false, true),
  ('arms',          'Arms',              70,  70,  19,   true,  'both',   false, false, true),
  ('gloves',        'Gloves',            80,  80,  7,    true,  'both',   false, false, true),
  ('waist',         'Belt / Sash',       90,  90,  12,   true,  'both',   false, false, true),
  ('legs_outer',    'Skirt / Kilt',      100, 100, 23,   true,  'both',   false, false, true),
  ('torso_outer',   'Robe / Outer Torso',110, 110, 22,   true,  'both',   false, false, true),
  ('neck',          'Neck',              120, 120, 10,   true,  'both',   false, false, true),
  ('hair',          'Hair',              130, 130, 11,   true,  'both',   false, false, true),
  ('facial_hair',   'Beard',             140, 140, 16,   true,  'male',   false, false, true),
  ('face',          'Face Art',          150, 150, 15,   true,  'both',   false, false, true),
  ('earrings',      'Earrings',          160, 160, 18,   true,  'both',   false, false, true),
  ('head',          'Hat / Helm',        170, 170, 6,    true,  'both',   false, false, true),
  ('cloak',         'Cloak',             180, 180, 20,   true,  'both',   false, false, true),
  ('backpack',      'Backpack',          190, 190, 21,   true,  'both',   false, true,  true);

INSERT INTO hue_groups (id, group_key, display_name, sort_order) VALUES
  ('01J8Z3K9H0HGRPSKN000000001', 'skin',     'Skin Hues',      1),
  ('01J8Z3K9H0HGRPHA1R00000002', 'hair',     'Hair Hues',      2),
  ('01J8Z3K9H0HGRPTATT00000003', 'tattoo',   'Tattoo Hues',    3),
  ('01J8Z3K9H0HGRPCTH000000004', 'clothing', 'Clothing Hues',  4),
  ('01J8Z3K9H0HGRPEVNT00000005', 'event',    'Event Hues',     5);

INSERT INTO tags (id, tag_key, display_name, category) VALUES
  ('01J8Z3K9H1TAGSTYFANTASY001', 'style-fantasy', 'Fantasy',       'style'),
  ('01J8Z3K9H1TAGSTYR0GVE00002', 'style-rogue',   'Rogue',         'style'),
  ('01J8Z3K9H1TAGSTYN0B1E00003', 'style-noble',   'Noble',         'style'),
  ('01J8Z3K9H1TAGSTYRSTC000004', 'style-rustic',  'Rustic',        'style'),
  ('01J8Z3K9H1TAGERA0R1G1N0005', 'era-original',  'Original UO',   'era'),
  ('01J8Z3K9H1TAGERA0VT1ANDS06', 'era-outlands',  'Outlands',      'era'),
  ('01J8Z3K9H1TAGSRCSH0P000007', 'source-shop',   'Item Shop',     'source'),
  ('01J8Z3K9H1TAGSRCDR0P000008', 'source-drop',   'In-World Drop', 'source');

INSERT INTO app_settings (setting_key, value) VALUES
  ('admin_ip_allowlist',             '[]'),
  ('maintenance_mode',               'false'),
  ('takedown_response_template',     '""'),
  ('featured_designs',               '[]'),
  ('designs_like_this_threshold',    '2'),
  ('reimport_reminder_cadence_days', '30');

The 19 seeded slots are the whole registry: one required slot (body) and 18 choosable cosmetic slots, backpack among them. backpack is is_always_visible = true because a paperdoll always draws one, and it is nonetheless choosable — the visitor may change its asset and its hue — which is why the design document in Section 13.2 may carry a backpack entry like any other choosable slot, and why omitting it falls back to backpack.default at hue 0 rather than rendering nothing. That default asset key is produced by the pipeline (Section 7.8) and guaranteed present in every published build.

is_always_visible is about the rendered paperdoll, not about the stored document: the backpack is always drawn, but the entry that names which backpack is optional, exactly like every other choosable slot.

A dev-only fixture build is provided by skinforge-cli db fixtures, which runs a Deno script (not raw SQL) at db/seed/dev-fixtures.ts — the path Section 5.1 declares, and the only name for it — that inserts one game_builds row with label = 'dev-fixture-001' and status = 'published', a handful of assets/asset_variants/asset_images rows pointing at placeholder sprites checked into tests/fixtures/sprites/, and 16 representative hues rows spanning all five hue groups, each with its hue_group_members rows. The script generates every id with the application's own ULID generator, so fixture rows satisfy the same 26-character shape as production rows. It refuses to run when SKINFORGE_ENV is not development, to guarantee fixture data never reaches a production database.

6.32 Migration Policy and Runner #

Migration files live in db/migrations/, named NNNN_snake_case_description.sql with a zero-padded, strictly increasing four-digit sequence number. NNNN is never reused, even if a migration is later found to be wrong; a correction ships as a new migration with the next number.

A migration file never contains BEGIN; or COMMIT;. The runner owns transaction boundaries, and a file that opened its own transaction would commit the schema change before the runner recorded it, leaving schema_migrations and the actual schema able to disagree after a crash.

The Deno migration runner (skinforge-cli db migrate, implemented at cli/commands/db-migrate.ts, sharing its DB connection module with core/db/) does the following, in order, inside a single transaction per file:

1. Connect using SKINFORGE_DATABASE_URL (Section 24.2).
2. Ensure schema_migrations exists (bootstrap CREATE TABLE IF NOT EXISTS before the transactional loop, since the table cannot track its own creation).
3. List db/migrations/*.sql sorted lexically (equivalent to numeric order given the zero-padded prefix).
4. For each file not present in schema_migrations, in ascending order: open a transaction, run the file's SQL verbatim, insert a schema_migrations row with the file's SHA-256 checksum, commit.
5. If any file fails, roll back that file's transaction, leave all prior files applied, print the failing filename and the database error, and exit non-zero. The runner never applies a later file after an earlier one fails.
6. On every run, for files already recorded in schema_migrations, verify the stored checksum still matches the on-disk file; a mismatch aborts immediately with a checksum-drift error, since it means a migration was edited after being applied — the fix is always a new migration, never an edit.

Reversible-change rules: a migration must never be written as a destructive DROP COLUMN or DROP TABLE in the same release that also removes the application code reading that column, because a zero-downtime rolling deploy briefly runs old and new code against the same database. The pattern is: release N adds the new column/table (never dropping anything old); release N+1 switches application code to the new shape; release N+2 (a later migration file) drops the now-unused old column/table. Column additions must either be nullable or carry a DEFAULT, since adding a NOT NULL column without a default locks and rewrites the table. Renaming a column is always two migrations: add the new column and backfill in a batched UPDATE, then drop the old column in a later migration once application code no longer references it.

Zero-downtime rules for indexes: every CREATE INDEX on a table expected to hold more than a few thousand rows at migration time (designs, audit_log, import_run_items) uses CREATE INDEX CONCURRENTLY, which cannot run inside the runner's per-file transaction; such migrations are marked with a -- concurrent comment on the first line, and the runner detects that marker and runs the file outside a transaction, in autocommit mode, for that one file only. 0001_init.sql is exempt: it creates every table empty, so its indexes build instantly.

6.33 Data Lifecycle and Retention #

Permalink immutability is the reason catalog rows are never hard-deleted: a designs row references assets and hues only by the build_id it was created against, and Sections 9.7 and 9.8 guarantee that a retired asset or a superseded hue table still renders exactly as it did at design-creation time. Deleting the underlying assets, asset_variants, asset_images or hues rows would break every design ever created against that build, which is the one outcome the schema is designed to prevent.

The corollary matters just as much: storage growth is bounded by pruning regenerable artifacts, never by pruning designs. A design row is a few hundred bytes; its cached renders are hundreds of kilobytes. Deleting the renders reclaims essentially all of the space and costs only a re-render on the next view, whereas deleting the design would break a link somebody has already shared.

6.34 Backup and Restore Expectations #

The database is the single source of truth for every table above. Object storage (Section 4.7) holds image bytes under four prefixes — catalog/, renders/, og/ and imports/ — of which catalog/ and og/ are backed up and renders//imports/ are excluded as regenerable or scratch. The content_hash columns on asset_images, design_renders and og_images let a restore verify that restored object storage still matches the database's expectation.

Backup requirements at the data level (operational mechanics — schedule, tooling, retention location — are Section 23.6's concern):

• Full logical backups (pg_dump) must be taken at an interval no coarser than daily, and must succeed before being counted; a failed backup triggers an alert (Section 21.5).
• Point-in-time recovery must be possible to within 5 minutes of any point in the last 7 days, which requires continuous WAL archiving in addition to daily logical dumps, and therefore requires WAL segments to be retained for at least those 7 days (Section 23.6 states the operational retention).
• A restore is validated periodically by restoring into a scratch database and running: SELECT count(*) FROM schema_migrations matches the expected migration count; SELECT count(*) FROM designs is non-zero and non-decreasing versus the prior validation; and one asset_images row's storage_key is fetched from object storage and its bytes hash to the row's content_hash, proving database and object storage stayed consistent. asset_images is the right table to sample because catalog/ is backed up; sampling design_renders would fail legitimately on any object the purge job has reclaimed.
• Object storage itself (the fs or s3 driver, Section 4.7) must be backed up or replicated independently of the database; a database-only restore without matching object storage produces a site that returns correct JSON but broken images, which the restore validation step above is designed to catch before it reaches production.
• Because designs and audit_log are permanent, append-only records, a restore must never be treated as an opportunity to prune them; retention rules (Section 6.33) are enforced only by the scheduled cleanup jobs, never by excluding tables from a backup.

The catalog/ objects a restore validates are the content-addressed keys described in Section 6.12; because those keys never change once written, a restored bucket and a restored database agree or the content_hash check fails loudly, with no third possibility.

6.35 admin_pending_mfa #

A login that has passed the password step and is waiting for a TOTP or recovery code (Section 17.2.1).

Primary key: id. Unique: token_hash. Foreign key: admin_user_id → admin_users(id) ON DELETE CASCADE. Rows are deleted on successful login and pruned on expiry (Section 6.33). Typical row count: zero to a handful at any moment.

6.36 admin_login_failures #

The rolling record the lockout policy in Section 17.2.3 counts.

Primary key: id. Foreign key: admin_user_id → admin_users(id) ON DELETE CASCADE. Check: phase IN ('password','totp','recovery_code').

CREATE INDEX admin_login_failures_email_time_idx
  ON admin_login_failures (email_attempted, created_at DESC);
CREATE INDEX admin_login_failures_ip_time_idx
  ON admin_login_failures (ip_address, created_at DESC);
-- The two lockout counters in Section 17.2.3: per-account and per-IP, each over a rolling window.

Only failures are recorded; a successful login updates admin_users.last_login_at and writes an audit_log row instead. Rows older than 30 days are pruned (Section 6.33).

6.37 app_settings #

The small, explicitly enumerated set of operator-editable runtime settings the admin console writes (Section 17.14). Everything else about this system is configured by environment variable (Section 24.1); this table is the carved-out exception for the handful of values staff must be able to change without a redeploy.

Primary key: setting_key. Foreign key: updated_by → admin_users(id) ON DELETE SET NULL. Check: setting_key IN ('admin_ip_allowlist','maintenance_mode','takedown_response_template', 'featured_designs','designs_like_this_threshold','reimport_reminder_cadence_days') — a fixed vocabulary, so an unknown key cannot be smuggled in and silently ignored.

Every write also writes an audit_log row. Fixed row count: six — the CHECK above is the complete list, and adding a seventh setting is a migration, not a configuration change.

The division of labour is stated once, in Section 24.1, and this table is its other half: environment variables configure the process at boot and are immutable for the life of that process, while these six values are read at request time through a cached accessor so an operator can change them from the admin console without a redeploy. No value appears in both places.

7. Asset Discovery & Extraction Pipeline #

7.1 Operating Model #

Extraction runs entirely on the operator's own machine or the same server that hosts the application, against the operator's own legally installed UO Outlands client. Nothing in this pipeline downloads game files from any third party, and the public web application never touches raw client files. End users of the public site never supply files, never see a file picker, and never interact with this pipeline in any form; everything in Section 7 is staff/operator tooling exposed through skinforge-cli (Section 4.3) and surfaced read-only in the admin console (Section 17.5).

The operator points the CLI at a local directory:

skinforge-cli import discover --client-dir /path/to/uo-outlands/client --build-label 2026-09-01-patch-47

This creates a game_builds row (Section 6.4) in status = 'draft' and begins Stage D1. Every later stage operates against that build_id and its findings end up in source_files, import_runs, import_run_items, and extraction_candidates (Sections 6.5–6.8). No stage mutates the operator's client directory; it is opened read-only throughout.

The work directory for a build (SKINFORGE_IMPORT_WORKDIR/<build_id>/, Section 24.2) holds all intermediate artifacts — decoded sprite buffers before normalization, decoded hue tables, the stage journal, manifest JSON, format notes — and is never web-accessible (Section 7.11).

Process model, and why it is split in two. Every byte this pipeline parses is untrusted input from a binary file format with no integrity guarantees. So the code that parses it is never the code that holds a network socket:

• The extractor child. Each stage runs its decoding work in a child process launched with deno run --allow-read=<client-dir>,<workdir> --allow-write=<workdir> and no --allow-net flag at all. It cannot open a socket to anything — not the database, not object storage, not the internet. It reads client bytes, decodes them, and appends its findings as newline-delimited JSON to the stage journal under <workdir>/<build_id>/journal/, plus binary artifacts under extracted/ and normalized/.
• The driver parent. skinforge-cli itself holds the database connection and the object-storage client, launched with --allow-net=<database-host>,<storage-endpoint> and nothing else. It tails the child's journal as it is written and mirrors each entry into source_files, import_runs, import_run_items and extraction_candidates, and uploads the encoded images the child wrote. This is why import_runs.summary reflects live progress (Section 7.10) even though the decoder itself cannot reach the database.

This split is what makes the isolation guarantee in Section 20.6 literally true rather than merely intended: a malicious or corrupt game file that achieves code execution inside the decoder lands in a process with no network permission and write access to one scratch directory.

7.2 The Six Stages #

The pipeline is six ordered stages, D1 through D6, each a separate CLI subcommand and a separate import_runs row (stage column, Section 6.6). A stage can be re-run independently once its predecessor has succeeded; each stage reads only the database rows and work-directory artifacts its predecessor produced, never the operator's client directory directly except D1, D2 and D4, which read raw client bytes.

D1…D6 is prose shorthand used inside this section only. The value actually written to import_runs.stage, shown on every screen and used as every metric label, is the stage name from Section 6.6's eight-value vocabulary — inventory, identify, probe, extract, classify, normalize, plus the two ingestion stages import and verify that this section does not produce. Section 6.6 carries the full mapping; no surface outside this section ever writes or renders a D abbreviation.

Throughout this section, "records a source_files row" and "writes an import_run_items row" describe the outcome, not the mechanism: the decoding child appends the entry to the stage journal and the driver parent performs the actual INSERT (Section 7.1). The row shapes, conflict targets and status vocabularies are exactly those in Sections 6.5–6.8, and every write uses the natural key as its ON CONFLICT target so re-running a stage is idempotent (Section 9.9).

7.2.1 Stage D1 — Inventory #

Input: the operator's client directory path. Output: one source_files row per file found, and an import_runs row summarizing counts by file_type. Command: skinforge-cli import discover --client-dir <path> --build-label <label> (creates the build) or skinforge-cli import discover --build <build_id> --client-dir <path> (re-run D1 against an existing build).

D1 walks the directory tree recursively, and for every regular file:

1. Records relative_path relative to the client directory root.
2. Records size_bytes from the filesystem.
3. Reads the first 4,096 bytes (or the whole file if smaller) and computes sha256 over the full file streamed in 64 KB chunks (never loading a multi-hundred-MB file fully into memory), magic_bytes as the hex of the first 16 bytes, and entropy (Shannon entropy in bits/byte) over the sampled prefix.
4. Classifies file_type by extension first (.mul → mul, .idx → idx, .uop → uop, .def → def, .txt/.cfg → txt, .png/.bmp/.tga → image, .zip/.rar/.7z → archive), then corrects the classification using magic bytes when the extension is missing or ambiguous (e.g. a MYP\0 magic reclassifies any extension as uop).
5. Inserts the source_files row.

Failure modes: unreadable file (permission denied) is recorded as an import_run_items row with status = 'failed' and the OS error in detail, and D1 continues past it — a single unreadable file never aborts inventory. A client directory that does not exist or is empty aborts the whole run immediately with a non-zero CLI exit code, since that indicates operator error, not pipeline risk.

7.2.2 Stage D2 — Identify #

Input: source_files rows from D1. Output: an import_run_items row per file recording its identified structure (or "unidentified"), feeding D3 for anything unidentified. Command: skinforge-cli import identify --build <build_id>.

D2 probes every mul, idx, uop, and unknown-typed file against the known UO structures from Section 7.3, in this order, stopping at the first match:

1. MUL/IDX pair: file has a same-stem .idx companion (or is itself a .idx with a same-stem .mul) and the .idx file's size is an exact multiple of 12 bytes → identified as mul_idx_pair.
2. UOP MythicPackage: first 4 bytes equal 4D 59 50 00 (MYP\0) → identified as uop.
3. hues.mul: filename is exactly hues.mul (case-insensitive) or file size is an exact multiple of 708 bytes (4-byte header + 8 × 88-byte hue entries per group, Section 7.3.3) → identified as hues_table.
4. tiledata.mul: filename is exactly tiledata.mul → identified as tiledata.
5. .def/.txt control files: matched by filename against a fixed dictionary (body.def, bodyconv.def, mobtypes.txt) → identified as control_file with a kind sub-field.
6. Loose image: file_type = 'image' from D1 → identified as loose_image.
7. Anything not matched by 1–6 is left status = 'needs_operator_input' and queued for D3.

Each identification is written as an import_run_items row with item_type = 'identify' and detail.structure set to the matched structure name.

7.2.3 Stage D3 — Probe Unknown Containers #

Input: files D2 left needs_operator_input. Output: extraction_candidates rows of a special candidate_key prefix probe: describing what D3 learned, still status = 'needs_operator_input' unless a later heuristic upgrades it. Command: skinforge-cli import probe --build <build_id>.

For every unidentified file, D3 runs, in order:

1. Header fingerprinting: compares the first 32 bytes against a signature table (zlib 78 9C/78 01/78 DA, gzip 1F 8B, LZ4 frame 04 22 4D 18, generic PNG/BMP/TGA signatures already handled by D1's classification). A match is recorded even though D2 did not resolve it to a known UO structure, since it may be a compressed variant of one.
2. Entropy scoring: entropy from D1, already computed, is bucketed as low (< 5.0, likely structured/uncompressed data or padding), medium (5.0–7.2, likely mixed binary), high (> 7.2, likely compressed or encrypted). High-entropy unidentified files are flagged for compression sniffing before anything else.
3. Compression sniffing: for files flagged high-entropy without a header match, D3 attempts a speculative decompress of the first 256 KB using DecompressionStream('deflate'), DecompressionStream('deflate-raw'), and DecompressionStream('gzip') (Section 7.3.5); the first variant that decompresses without a stream error and yields entropy < 6.5 on the decompressed sample is recorded as the working transform.
4. Repeated-record-stride detection: computes byte-difference histograms at candidate strides (4, 8, 12, 16, 20, 24, 32, 64 bytes) over the first 64 KB, looking for a stride at which a fixed-position sub-field repeats a recognizable pattern (e.g. a monotonically increasing pseudo-offset, consistent with an index file at a stride D1/D2 did not anticipate). A stride with a fit score above 0.8 is recorded as detail.likelyStride.
5. Manual format note: D3 checks for a file named format-note.json in the work directory (Section 7.4) authored by the operator, matched by relative_path glob; if present, its declared structure and parameters are trusted and the file is re-run through D4 as that structure instead of being left unidentified.

Every file leaves D3 as an extraction_candidates row: either upgraded (a later stage can now attempt extraction) or still status = 'needs_operator_input' with all of the above evidence recorded in metadata, ready for a human to read on the import detail screen (Section 17.5) and, if needed, write a format-note.json. D3 never crashes on a file it cannot resolve and never silently drops it — every input file has a corresponding output row by the end of D3.

7.2.4 Stage D4 — Extract #

Input: source_files identified by D2 (directly) or upgraded by D3. Output: decoded raw RGBA sprite buffers and decoded hue tables written to the work directory, each with an extraction_candidates row recording source offset and provenance. Command: skinforge-cli import extract --build <build_id> [--only mul_idx_pair|uop|hues_table|...].

For mul_idx_pair and uop structures identified as containing gump art, D4 walks every index entry (Section 7.3.1/7.3.4), decodes the gump run-length encoding (Section 7.3.2) into a raw RGBA buffer, and writes it to <workdir>/<build_id>/extracted/gumps/<gump_id>.rgba alongside a JSON sidecar with width, height, and source provenance (source_file_id, byte offset, and for UOP entries the 64-bit path hash that resolved it). For hues_table structures, D4 decodes every one of the 375 groups (Section 7.3.3) into 3,000 individual hue records held in memory, then written to <workdir>/<build_id>/extracted/hues.json.

Failure modes: a corrupt index entry (Section 7.12) is recorded as import_run_items with status = 'failed' and extraction continues with the next entry — one bad record never aborts a 50,000-entry file. A gump whose decoded byte length does not match its declared extra dimensions (Section 7.3.1) is recorded as status = 'failed' with detail.reason = 'dimension_mismatch' and is not written to the work directory.

7.2.5 Stage D5 — Classify #

Input: extracted sprites and hue records from D4. Output: extraction_candidates rows with suggested_slot_key, suggested_body, and confidence populated (Section 7.6). Command: skinforge-cli import classify --build <build_id>.

D5 runs the heuristics in Section 7.6 against every D4 output, writing or updating the corresponding extraction_candidates row. Rows scoring at or above the acceptance threshold (0.85, or 0.65 under the cold-start rule in Section 7.6) are marked status = 'approved' automatically; rows below it stay status = 'pending' for the staff review queue (Section 17.6).

7.2.6 Stage D6 — Normalize #

Input: approved candidates from D5 (automatically or via staff approval), plus the decoded hue records D4 wrote to the work directory. Output: assets, asset_variants, asset_images, hues and hue_group_members rows (Sections 6.10–6.13 and 6.15), with encoded WebP/PNG files written to object storage. Command: skinforge-cli import normalize --build <build_id>.

D6 performs the trimming, offset preservation, multi-scale generation, and encoding described in Section 7.8, and is the only stage that writes to the permanent catalog tables rather than pipeline scratch tables. D6 never runs against a candidate that has not reached status = 'approved'. Nothing else in the system writes assets, asset_variants, asset_images or hues: approving a candidate in the admin console (Section 17.6) only sets its status, and the catalog rows appear when the operator next runs D6.

Hues are D6's output too, and this is the only place they are written. D4 decodes every hue table into <workdir>/<build_id>/extracted/hues.json and D5 proposes a group assignment for each one, but neither writes a row: hues is a permanent catalog table, so it follows the same rule as every other one. In the same run, and inside the same per-build transaction boundary, D6 inserts one hues row per decoded hue for this build_id, substitutes the Hue <index> placeholder for any blank decoded name (Section 7.7), and inserts the hue_group_members rows that realize D5's proposal. The ordering is forced rather than stylistic — hue_group_members.hue_id is a foreign key to hues.id (Section 6.15), so a membership row written before D6 has no row to point at.

7.3 File Format Reference #

This reference is sufficient, on its own, to implement every reader without consulting another source. All multi-byte integers are little-endian unless stated otherwise, matching the original client's x86 origin.

7.3.1 MUL/IDX Pairs #

A MUL/IDX pair is two files: a .mul file holding concatenated binary records, and a .idx file holding one fixed 12-byte index entry per logical record, pointing into the .mul file.

Each index entry:

Walking a pair: recordCount = Math.floor(idxFileSize / 12). For i in 0..recordCount, read the 12-byte entry at offset i * 12. If lookup === 0xFFFFFFFF, the record at index i does not exist — skip it, never treat it as an error. Otherwise read length bytes from the .mul file starting at lookup.

interface MulIdxEntry {
  lookup: number;
  length: number;
  extra: number;
}

function readIdxEntries(idxBytes: Uint8Array): MulIdxEntry[] {
  const view = new DataView(idxBytes.buffer, idxBytes.byteOffset, idxBytes.byteLength);
  // Floored: a .idx whose size is not an exact multiple of 12 has a partial trailing entry,
  // and reading it would run past the end of the DataView and throw a RangeError.
  const count = Math.floor(idxBytes.byteLength / 12);
  const entries: MulIdxEntry[] = [];
  for (let i = 0; i < count; i++) {
    const base = i * 12;
    entries.push({
      lookup: view.getUint32(base, true),
      length: view.getUint32(base + 4, true),
      extra: view.getUint32(base + 8, true),
    });
  }
  return entries;
}

7.3.2 Gump Art Encoding #

A gump art record (the .mul bytes located by one MUL/IDX entry, or one decompressed UOP entry) is a per-row lookup table followed by run-length-encoded pixel data.

Layout:

width and height come from the owning MUL/IDX entry's extra field (Section 7.3.1) or, for UOP gump entries, from a 8-byte header prefix (uint32 width, uint32 height) placed before the row lookup table by the UOP-specific packaging (Section 7.3.4).

Each row's run data is a sequence of runs, each 4 bytes: uint16 color (ARGB1555 packed pixel value) followed by uint16 runLength (count of consecutive pixels with that color). A row ends when the sum of runLength values for that row reaches width; there is no explicit end-of-row marker — the reader must track the running x-position and stop consuming runs for the current row once it reaches width.

The packed value 0x0000 is the transparency sentinel: a run whose color is zero writes runLength fully transparent pixels. Every other packed value decodes as opaque. Bit 15 is never consulted — UO gump art carries no per-pixel alpha channel beyond that zero/opaque split, so 0x8000 (alpha bit set, all colour bits zero) is still transparent.

The unpacking function has exactly one definition in this system; Section 8.3.1 owns it, and it is reproduced here because D4 runs long before any rendering code is loaded. Its signature and its semantics are identical in both places:

interface Rgba { r: number; g: number; b: number; a: number }

function unpackArgb1555(c: number): Rgba {
  // ARGB1555: bits 14-10 = R, 9-5 = G, 4-0 = B. Bit 15 is not consulted.
  const r5 = (c >> 10) & 0x1f;
  const g5 = (c >> 5) & 0x1f;
  const b5 = c & 0x1f;
  // 5-bit to 8-bit expansion: replicate the top 3 bits into the low 3 bits for full dynamic range.
  return {
    r: (r5 << 3) | (r5 >> 2),
    g: (g5 << 3) | (g5 >> 2),
    b: (b5 << 3) | (b5 >> 2),
    a: c === 0x0000 ? 0 : 255,
  };
}

The decoder validates every offset it reads out of the file before using it. A gump record is untrusted input, and three separate malformed shapes — a truncated lookup table, a row offset pointing outside the record, and a run whose declared length is zero — will otherwise throw a RangeError or spin forever, because a zero-length run advances the cursor but never advances x:

class GumpDecodeError extends Error {
  constructor(public readonly reason: string) {
    super(`gump decode failed: ${reason}`);
    this.name = 'GumpDecodeError';
  }
}

function decodeGump(data: Uint8Array, width: number, height: number): Uint8ClampedArray {
  const view = new DataView(data.buffer, data.byteOffset, data.byteLength);
  const rgba = new Uint8ClampedArray(width * height * 4);
  const lookupBase = 0;
  const runDataBase = 4 * height;

  if (data.byteLength < runDataBase) throw new GumpDecodeError('lookup_table_truncated');

  for (let row = 0; row < height; row++) {
    const rowOffset = runDataBase + view.getUint32(lookupBase + row * 4, true);
    if (rowOffset < runDataBase || rowOffset > data.byteLength - 4) {
      throw new GumpDecodeError('row_offset_out_of_bounds');
    }
    let x = 0;
    let cursor = rowOffset;
    while (x < width) {
      if (cursor + 4 > data.byteLength) throw new GumpDecodeError('run_data_truncated');
      const color = view.getUint16(cursor, true);
      const runLength = view.getUint16(cursor + 2, true);
      cursor += 4;
      if (runLength === 0) throw new GumpDecodeError('zero_length_run');
      if (color === 0) {
        // Transparent run: leave RGBA buffer at its zero-initialized (0,0,0,0) default.
        x += runLength;
        continue;
      }
      const { r, g, b, a } = unpackArgb1555(color);
      for (let i = 0; i < runLength && x < width; i++, x++) {
        const pixelIndex = (row * width + x) * 4;
        rgba[pixelIndex] = r;
        rgba[pixelIndex + 1] = g;
        rgba[pixelIndex + 2] = b;
        rgba[pixelIndex + 3] = a;
      }
    }
  }
  return rgba;
}

Transparency rule: any pixel never written by a run (which does not occur in well-formed data, since runs sum to exactly width per row) is fully transparent because the buffer is zero-initialized.

Failure rule: the decoder throws a typed GumpDecodeError on any malformed input and never returns a partially-decoded buffer. D4 catches it per Section 7.2.4 and records an import_run_items row with status = 'failed' and detail.reason set to the error's reason code — one of lookup_table_truncated, row_offset_out_of_bounds, run_data_truncated or zero_length_run (Section 7.12). A throw inside the decoder is therefore a handled outcome for one record, not an unhandled exception, which is what Section 7.4's "nothing in D1–D3 ever throws an unhandled exception" means: the throw is caught at the stage boundary, one row down.

No mask, index map or palette sidecar is produced alongside the decoded buffer. Gump art is direct-colour ARGB1555, not palette-indexed, so there is no palette position to capture; the hue table index for a pixel is derived from the pixel itself at render time (Section 8.3), never stored.

7.3.3 hues.mul #

hues.mul holds 375 groups of 8 hues each (3,000 hues total), each hue occupying a fixed 88 bytes.

Group layout (708 bytes: 4-byte header + 8 × 88-byte entries):

Each 88-byte hue entry:

Total per entry: 64 + 2 + 2 + 20 = 88 bytes, confirming the layout. Global hue index for group g (0-based) and slot s (0-based, 0..7) is g * 8 + s + 1 (hue indices are 1-based; index 0 is the reserved "unhued" sentinel per Section 3.6, never present in this file).

interface DecodedHue {
  hueIndex: number;
  colors: number[]; // 32 entries, packed 0xRRGGBB after ARGB1555 expansion
  tableStart: number;
  tableEnd: number;
  name: string;
}

function decodeHuesMul(data: Uint8Array): DecodedHue[] {
  const view = new DataView(data.buffer, data.byteOffset, data.byteLength);
  const hues: DecodedHue[] = [];
  const GROUP_SIZE = 708;
  const groupCount = Math.floor(data.byteLength / GROUP_SIZE);
  for (let g = 0; g < groupCount; g++) {
    const groupBase = g * GROUP_SIZE + 4; // skip 4-byte header
    for (let s = 0; s < 8; s++) {
      const entryBase = groupBase + s * 88;
      const colors: number[] = [];
      for (let c = 0; c < 32; c++) {
        const argb1555 = view.getUint16(entryBase + c * 2, true);
        const { r, g: gc, b } = unpackArgb1555(argb1555);
        colors.push((r << 16) | (gc << 8) | b);
      }
      const tableStart = view.getUint16(entryBase + 64, true);
      const tableEnd = view.getUint16(entryBase + 66, true);
      const nameBytes = data.subarray(entryBase + 68, entryBase + 88);
      const nul = nameBytes.indexOf(0);
      const name = new TextDecoder('ascii').decode(nul >= 0 ? nameBytes.subarray(0, nul) : nameBytes).trim();
      hues.push({ hueIndex: g * 8 + s + 1, colors, tableStart, tableEnd, name });
    }
  }
  return hues;
}