# Unmatched Clone — Database Everything needed to stand up the database described in `unmatched-clone-design-plan.md`. Built for **MySQL / MariaDB**, verified against both before delivery. --- ## What this covers — and what it deliberately doesn't Design doc §2 splits the world into three tiers, and only one of them is a database concern: | Tier | Lives where | In this schema? | |---|---|---| | Deck library — champions, sidekicks, cards, effects | MySQL | **Yes — all of it** | | Lobbies, `GameState`, generated maps, active status effects | In-memory on the Node process | No, by design (§2) | | Draft decks in progress | Browser `localStorage` | No, by design (§2) | So there are no `lobby`, `game`, `map` or `match_history` tables. That is the doc's architecture, not an oversight. If you later want match history or reconnect-safe games (§11 flags reconnect handling as undesigned), say so — it's an additive change, not a rewrite. --- ## Files, in import order | File | What it does | Required? | |---|---|---| | `sql/00_create_database.sql` | Creates the `unmatched` DB and an app user | Skip if your host made these | | `sql/01_schema.sql` | All tables, constraints, indexes | **Yes** | | `sql/02_reference_data.sql` | The §6 effect vocabulary + balance bounds | **Yes** | | `sql/03_views_and_validation.sql` | Views, triggers, stored procedures | **Yes** | | `sql/04_example_deck.sql` | One complete working deck | Optional | | `sql/05_new_deck_template.sql` | Fill-in-the-blanks template for a new deck | Not imported — a working template | | `sql/install_all.sql` | Runs 00–04 in one shot | Convenience | | `prisma/schema.prisma` | Prisma models matching the SQL exactly | For the Node layer | **Change the password in `00_create_database.sql` before running it.** ### Installing ```bash # From the project root, with the mysql CLI: mysql -u root -p < sql/install_all.sql ``` Or one file at a time: ```bash mysql -u root -p < sql/00_create_database.sql mysql -u root -p unmatched < sql/01_schema.sql mysql -u root -p unmatched < sql/02_reference_data.sql mysql -u root -p unmatched < sql/03_views_and_validation.sql mysql -u root -p unmatched < sql/04_example_deck.sql # optional ``` > **Using phpMyAdmin / Workbench / DBeaver instead?** `install_all.sql` > won't work — `SOURCE` is a CLI command. Run 00–04 individually, and > before running `03` set your tool's statement delimiter to `$$`, because > that file defines triggers and procedures. `01_schema.sql` starts with `DROP TABLE IF EXISTS` for every table, so re-running it **wipes the deck library**. `02` and `03` are idempotent and safe to re-run at any time. --- ## The data model ```mermaid erDiagram champion ||--o| deck : "is the hero of" champion ||--o{ effect : "has passives" deck ||--o{ sidekick : "0..2" deck ||--o{ card : "copies sum to 30" card ||--o{ effect : "has" effect ||--o{ effect_condition : "if" effect ||--o{ effect_action : "then" ref_card_type ||--o{ card : types ref_trigger ||--o{ effect : "fires on" ref_condition_type ||--o{ effect_condition : types ref_action_type ||--o{ effect_action : types ref_target_scope ||--o{ effect_action : targets ref_expiry_trigger ||--o{ effect_action : "expires on" champion { int id PK varchar name smallint base_health tinyint base_movement varchar avatar_image_path } deck { int id PK varchar name UK varchar creator_nickname int champion_id FK "UNIQUE" varchar status "draft|final" } sidekick { int id PK int deck_id FK tinyint slot_index "0 or 1" smallint base_health } card { int id PK int deck_id FK varchar slug "stable handle" varchar card_type FK tinyint attack_value tinyint defense_value tinyint boost_value bool is_unique tinyint copies } effect { int id PK int card_id FK "XOR champion_id" int champion_id FK varchar trigger_code FK json trigger_filter } effect_condition { int id PK int effect_id FK varchar condition_type FK json params text code "raw JS" } effect_action { int id PK int effect_id FK varchar action_type FK varchar target_scope FK json params varchar expires_on FK text code "raw JS" } ``` ### Three decisions worth knowing about **1. Effects are normalised, not stored as JSON blobs.** §6.1 presents an effect as a JSON object. Storing it that way would have been less code — but then `"onCardPlayd"` is a typo that silently never fires, discovered mid-game. Here `trigger`, condition type, action type, scope and `expiresOn` are all foreign keys into the vocabulary tables, so a typo is rejected at `INSERT`. `v_effect_json` reassembles the exact §6.1 shape for the effect runner, so nothing downstream has to care: ```json { "id": 1, "label": "Howl cows nearby enemies", "trigger": "onCardPlayed", "triggerFilter": { "cardId": "howl" }, "conditions": [ { "type": "adjacency", "scope": "adjacent", "team": "opponent" } ], "actions": [ { "type": "applyStatus", "scope": "conditionMatches", "modifier": { "defense": -1 }, "expiresOn": "nextDefense" } ] } ``` That is real output from the shipped example deck, and it is §6.1's worked example character-for-character (plus `id`/`sortOrder` metadata). **2. The vocabulary is data, not enums.** §11 expects the trigger/condition/action vocabulary to grow as real decks hit its limits. Adding a verb is one `INSERT` into a `ref_` table plus a handler in the effect runner — no `ALTER TABLE`, no migration, and it appears in the deck builder's dropdowns immediately if you populate them from `v_vocabulary` as intended. **3. `copies`, not 30 rows.** Real Unmatched decks reach 30 cards through duplicates. One `card` row is one distinct design; `copies` is how many go in the deck. §7's "exactly 30" is validated as `SUM(copies) = 30`. See question 1 below. --- ## Where each §7 rule is enforced | Rule | Enforced by | Fails at | |---|---|---| | Exactly 30 cards | `trg_deck_before_update` | finalize | | At least one of each card type | `trg_deck_before_update` (reads `ref_card_type.required_in_deck`) | finalize | | At least 2 unique cards | `trg_deck_before_update` | finalize | | 0–2 sidekicks | `slot_index IN (0,1)` + `UNIQUE(deck_id, slot_index)` | insert | | Attack cards have attack values, schemes have neither, etc. | `chk_card_type_values` | insert | | Sidekicks hold no passives (§7) | `effect` has no `sidekick_id` column | impossible | | An effect belongs to a card **or** a champion, never both | `trg_effect_before_insert/update` | insert | | Only `applyStatus` sets `expiresOn` (§6.6) | `chk_action_expiry_owner` | insert | | `expression` rows carry code; structured rows don't | `chk_condition_code`, `chk_action_code` | insert | | Unknown trigger / action / scope / expiry codes | foreign keys | insert | A deck **cannot be inserted as `final`** — it has no cards yet at that point. Insert as `draft`, add content, then `CALL sp_finalize_deck(id)`. That call is the §7 gate, and it names every broken rule when it refuses: ``` ERROR 1644 (45000): Deck not legal: card count is 1 (needs 30); missing card type(s): defense, ranged_attack, scheme, versatile; has 0 ``` (`SIGNAL` truncates at 128 bytes — `v_deck_validation` has the untruncated list, and is what the deck builder should read to drive the "Save final" button's tooltip.) Once final, a deck is **locked** against edits, because §7 says it becomes visible to every lobby the instant it's published and editing it out from under a lobby mid-selection is a nasty bug. Override deliberately: ```sql SET @allow_final_deck_edit = 1; -- ... your changes ... SET @allow_final_deck_edit = NULL; CALL sp_finalize_deck(@deck_id); -- re-validate ``` --- ## Views and procedures the app should use | Object | Use it for | |---|---| | `v_deck_library` | §4's lobby deck picker. Final decks only, with champion thumbnails | | `v_deck_export` | The whole deck as one JSON doc — load once at `lobby:start`, hand to the engine. No N+1 | | `v_deck_validation` | Deck builder's "Save final" button state + why it's disabled | | `v_deck_stats` | Deck inspector; also `raw_js_count`, i.e. how much §6.4 JS a vocabulary change would break | | `v_vocabulary` | Fills every dropdown in the §6.8 effect editor in one query | | `v_effect_json` | A single effect in §6.1 shape | | `sp_finalize_deck(id)` | "Save final" (§7) | | `sp_clone_deck(src, name, creator, @out)` | "Start from this deck" — copies champion, sidekicks, cards, effects, conditions and actions | | `sp_delete_deck(id)` | Deletes a deck *and* its champion (cascade can't reach the champion — the FK points the other way) | --- ## The Prisma layer `prisma/schema.prisma` targets `provider = "mysql"` and matches the SQL exactly — all 151 column mappings were checked against `information_schema`. **Do not run `prisma migrate dev` against a fresh database.** Prisma doesn't model CHECK constraints, triggers, views or stored procedures, so letting it own the schema would silently drop every §7 rule and the whole JSON reassembly layer. Import the SQL first, then: ```bash npx prisma db pull # confirms this schema matches reality npx prisma generate ``` Baselining instructions for Prisma Migrate are in the file's header comment. Two things to know: - IDs are `INT UNSIGNED`, not `BIGINT`. Prisma maps MySQL `BIGINT` to JS `BigInt`, which `JSON.stringify()` throws on — you'd have hit that the first time a deck id crossed the Socket.io boundary. - The `view` blocks need `previewFeatures = ["views"]`. If your Prisma version predates it, delete that block and use `$queryRaw` — the views work identically either way. --- ## Deploying on Oracle Cloud Nothing in the schema is architecture-dependent, and both MySQL 8 and MariaDB ship ARM64 builds, so an Ampere A1 instance is fine. Points that actually bite on OCI: - **Two firewalls, not one.** OCI images ship with host `iptables`/ `firewalld` rules *on top of* the VCN Security List. Opening a port in the console alone does nothing. Since §1 puts the API and the built React app in a single Node process on one host, the cleanest answer is to not open 3306 at all — bind MySQL to `127.0.0.1` and expose only 80/443. - **Managed MySQL (HeatWave / MDS) rejects trigger creation by default.** With binary logging on and no `SUPER` privilege, `CREATE TRIGGER` fails with `ERROR 1419`. Set `log_bin_trust_function_creators = ON` in the DB System's configuration if you go managed. Self-hosting on the VM avoids this entirely. - **Back up with routines and triggers**, or you'll restore a database where any deck can be published: ```bash mysqldump --routines --triggers --single-transaction unmatched > backup.sql ``` - §1 stores card art on local disk with the path in the DB. That's the boot volume — default 50 GB, and the free tier allows 200 GB total block storage. Include `/uploads` in whatever backs up the VM; the database only holds paths, so a DB backup alone loses every image. --- ## Deviations from the design doc | Doc says | Delivered | Why | |---|---|---| | §1: PostgreSQL | MySQL / MariaDB | Your deployment choice. Costs: no partial indexes, no native `jsonb` operators, and CHECK constraints can't use subqueries — which is why the §7 deck rules are triggers rather than constraints | | §1: Prisma ORM | Prisma, `provider = "mysql"` | Unchanged otherwise | | §2: `Card` has no id/handle | Added `card.slug` | §6.1's own example filters with `{"cardId": "howl"}` — a slug. Slugs survive cloning and JSON export; auto-increment ids don't | | §6.1: effects as JSON | Normalised, with `v_effect_json` rebuilding the JSON | Typos in the vocabulary now fail at write time instead of silently never firing | --- ## Verified before delivery Full import run end-to-end on **MySQL 8.0.46** and **MariaDB 10.11.14**, identical results on both: - All five files import with zero errors; `install_all.sql` works from the project root - `05_new_deck_template.sql` runs as-shipped and produces a valid deck - The §6.1 effect round-trips through `v_effect_json` byte-identically - `v_deck_export` nests correctly (cards → effects → conditions/actions all as objects, not escaped strings) - `sp_clone_deck` reproduces all 30 cards, 6 card effects and 1 champion passive - Ten negative cases all rejected with the intended error: attack card with no attack value, finalizing an incomplete deck, a third sidekick, editing a final deck, an ownerless effect, a double-owned effect, an unknown trigger code, `expiresOn` on a non-`applyStatus` action, an `expression` row with no code, and a deck inserted directly as `final` Three real bugs were found and fixed during that pass: MySQL rejects a CHECK on a column whose FK carries a referential action (so the card-or-champion rule became a trigger); `SIGNAL MESSAGE_TEXT` truncates at 128 bytes and was raising a misleading secondary error; and MariaDB has no `CAST(... AS JSON)`, which broke JSON nesting until the views were rewritten to a portable form. Prisma's own CLI couldn't run here (its engine binaries are network- blocked in this sandbox), so `prisma db pull` is worth running once on your side as a final confirmation. --- ## Questions 1. **Does `copies` match your intent?** A deck reaches 30 cards through duplicates, as real Unmatched does — 9 distinct designs in the example deck, 30 physical cards. If you actually want 30 *distinct* card designs, one line in `trg_deck_before_update` changes. 2. **§6.5 and §6.1 disagree on target scopes.** §6.5 lists `self`, `target`, `adjacentEnemies`, `adjacentFriendly`, `zone`, `prompt` — but §6.1's worked example uses `conditionMatches`, which isn't in that list. I seeded the union of both. Drop the row if it wasn't intended. 3. **One champion per deck, or a shared hero library?** Right now `deck.champion_id` is `UNIQUE`, so each hero belongs to exactly one deck. If you'd rather have several decks share a hero, that's dropping one index — but hero passives would then be shared too, so two decks couldn't tune the same hero differently. 4. **Should a published deck stay locked?** Currently yes (with an explicit override), because a lobby may already have it selected. The alternative is letting the creator revise in place and accepting that lobbies mid-selection can shift under them. 5. **Card art** — is a filesystem path enough (§1 says local disk), or do you want dimensions / content hashes stored too, for dedup and for catching broken image links before a game starts? 6. **§11's open items** — the win-condition and sidekick-elimination edge cases still need resolving against the real rulebook. Want me to work through those next? They affect the engine, not this schema. 7. **What comes next?** The build order in §10 puts lobby/networking before the deck builder. I can take the Express + Prisma data-access layer over this schema, or the §5 map generator, or the §6.7 effect runner — whichever unblocks you fastest.