# Title Screen schema (archive 2026.10.09, schema 1)

## Rules a consumer can rely on

- Ids never change meaning. A work id is `w` + 7 base32 letters, a release `r`, a company `c`. An id that stops being used appears in `redirect`, with the id that absorbed it when two works were merged.
- Columns are only added, never renamed or removed, within a major schema version (this is schema 1).
- Every fact in work and release is backed by rows of `assertion` (evidence.jsonl): its source, tier, how it was joined to the dump and where to look again. Tiers: 0 an override, 1 the media itself, 2 the curated dump lists and MAME, 3 the press, 4 the web databases.
- Empty means unknown, never zero or "none". In JSON Lines the key is absent.
- No column holds prose: titles, names, identifiers, dates, numbers and short codes only.
- Dates are split into year, month, day; `date_basis` says whether the date is evidence about that release (`release`), the title's first date on the platform (`title`) or only the game's first date anywhere (`work`). MAME's uncertain years ("19??") are kept as written in `year`.
- A dump sold in several markets ("(Japan, USA)") belongs to a release per market: `dump_release` (in dumps.jsonl, `release_ids`) lists them all. When a publisher shipped identical bytes in several markets, no hash can tell them apart: a lookup returns every release the dump was sold as.
- A dump's `identity` is the SHA-1 + `size` of its canonical form, or set1 over its files for a dump of several (https://titlescreen.org/spec/canonical-form): container and file name never matter. `fp1` is a lookup key that returns candidates, not an identity (https://titlescreen.org/spec/fp1). `file_hash.status` says who has seen a hash: a list (`dat`), a scan that agreed with the list (`matched`), a scan of a copy no list has (`scan-only`).
- Lookups (the site's API, Phase 3): by `sha1` + `size`, `crc32` + `size` (a pre-check, not confirmed), `fp1`, or a list's whole-file hash; each answer carries its basis (`sha1`, `crc32`, `fp1`, `file`).

## Files

- `gamedb-<ver>.sqlite.gz`: every table below but `assertion`.
- `gamedb-<ver>-evidence.sqlite.gz` and `gamedb-<ver>-evidence.jsonl.gz`: the `assertion` table (the evidence), a separate download.
- `gamedb-<ver>-jsonl.tar.gz`: one file per table (works, releases, dumps with their files, hashes and release ids, companies, platforms, genres, titles, links, joins, listings, redirects).
- `gamedb-<ver>-csv.zip`: works, releases, dumps, dump files, file hashes, companies, platforms, with names joined in.
- `freeplay-gamedb-<ver>.sqlite.gz`: the database in Freeplay's own schema (one row per dump), for Freeplay and anything built on it.

## Tables

### meta

the build's version, inputs and counts

| column | type | meaning |
|---|---|---|
| `key` | TEXT | version, schema, built, libretro_commit, mame, mame_hash_commit, licence, ids_minted |
| `value` | TEXT |  |

### platform

the consoles, handhelds and arcade the database covers

| column | type | meaning |
|---|---|---|
| `id` | TEXT | Freeplay's system ids, extended (intv, o2, ps4, switch ...) |
| `name` | TEXT | the platform's usual English name |
| `manufacturer` | TEXT |  |
| `generation` | TEXT | '2'..'8' or 'arcade' |
| `kind` | TEXT | home, handheld, arcade, hybrid |
| `addon_of` | TEXT | the platform an add-on plugs into (32x, segacd, fds, jaguarcd) |
| `launch_year` | INTEGER | first launch anywhere |
| `end_year` | INTEGER | last commercial year; NULL while homebrew or new releases continue |
| `media` | TEXT | cartridge, cd, gd-rom, dvd, bd, umd, card, disk, arcade-set |
| `title_only` | INTEGER | 1: no dump list exists, releases come from the web lists |

### company

publishers and developers, every spelling the sources use folded into one row

| column | type | meaning |
|---|---|---|
| `id` | TEXT | c + 7 base32 |
| `name` | TEXT | display name: the merge file's, else the spelling the sources use most |
| `key` | TEXT | the fold every spelling reduces to |
| `aliases` | TEXT | JSON list of every spelling the sources used |
| `codes` | TEXT | JSON: header code tables (licensee, maker codes) |
| `wikidata` | TEXT | Wikidata item, when the merge file names one |
| `published` | INTEGER | releases naming it as publisher |
| `developed` | INTEGER | releases naming it as developer |

### genre

the controlled genre list (data/genre-map.yaml)

| column | type | meaning |
|---|---|---|
| `id` | TEXT | a path: shooter, shooter/shmup |
| `name` | TEXT |  |
| `parent` | TEXT | the top-level genre of a sub-genre |

### work

a game as a creative work, across every platform and market

| column | type | meaning |
|---|---|---|
| `id` | TEXT | w + 7 base32 |
| `title` | TEXT | the first English-market commercial release's title, else the first release's |
| `sort_title` | TEXT | the title without a leading article, for browsing |
| `first_year` | INTEGER | the earliest dated commercial release, on any platform |
| `first_month` | INTEGER |  |
| `first_day` | INTEGER |  |
| `first_platform_id` | TEXT | NULL when several platforms share the earliest date, or none is dated |
| `first_platform_status` | TEXT | single, simultaneous, unknown |
| `first_platforms` | TEXT | the platforms of the earliest date, comma-separated |
| `developer_id` | TEXT | the original developer, when the first platform's releases agree |
| `genre_id` | TEXT | the genre most of its commercial releases have |
| `max_players` | INTEGER | the maximum over its releases |
| `franchise` | TEXT | libretro's franchise name |
| `kind` | TEXT | game, compilation, add-on, non-game |
| `releases` | INTEGER | how many releases it has |
| `platforms` | TEXT | comma-separated platform ids |

### release

a title on one platform in one market and class (commercial, prototype, demo ...)

| column | type | meaning |
|---|---|---|
| `id` | TEXT | r + 7 base32 |
| `work_id` | TEXT |  |
| `platform_id` | TEXT |  |
| `region` | TEXT | market: JP, US, EU, WORLD, DE, FR, KR ...; '' unknown |
| `title` | TEXT | as released in that market (native script for JP/KR/CN when known) |
| `title_latin` | TEXT | the dump lists' Latin spelling |
| `year` | TEXT | may be MAME's '19??' |
| `month` | INTEGER |  |
| `day` | INTEGER |  |
| `date_basis` | TEXT | release: evidence about this release; title: the title's first date on the platform; work: only the game's first date anywhere (Wikidata) was known |
| `copyright_year` | TEXT | from the media or the title screen (Phase 4, 5) |
| `publisher_id` | TEXT |  |
| `publisher_raw` | TEXT | the winning source's spelling |
| `developer_id` | TEXT |  |
| `developer_raw` | TEXT | the winning source's spelling |
| `players_min` | INTEGER |  |
| `players_max` | INTEGER |  |
| `players_mode` | TEXT | alternating, simultaneous, both (later) |
| `multiplayer_hw` | TEXT | multitap, link-cable, online (media stage) |
| `genre_id` | TEXT |  |
| `genre_raw` | TEXT | the winning source's genre words |
| `serial` | TEXT | the publisher's catalogue numbers, ';'-joined |
| `product_code` | TEXT | the maker's code where it differs from the serial (media stage) |
| `rating` | TEXT | the age rating the media declares (media stage) |
| `price` | TEXT | launch price in that market (press stage) |
| `price_currency` | TEXT | ISO 4217 |
| `media` | TEXT | the platform's media |
| `licensed` | TEXT | commercial, unlicensed, homebrew, prototype, demo, bios, add-on |
| `titles_by_lang` | TEXT | JSON {lang: [titles]} the release's own evidence carries |
| `natural_key` | TEXT | platform|title key|market|class, what the id was minted for |
| `dumps` | INTEGER | how many dumps it has |

### dump

an identified copy: a No-Intro, Redump, MAME, FBNeo or MAME software-list entry, or a copy a scan read that no list has (source scan or community)

| column | type | meaning |
|---|---|---|
| `id` | INTEGER | stable only within one archive; use key across archives |
| `key` | TEXT | platform/source/name, or arcade/mame/<set> |
| `release_id` | TEXT | the first of its releases (dump_release has all) |
| `platform_id` | TEXT |  |
| `name` | TEXT | the DAT's own name, verbatim; for a scan, the name of the list dump its header's serial placed it beside |
| `source` | TEXT | no-intro, redump, mame, fbneo, mame-sl, scan, community |
| `dat` | TEXT | which DAT or list |
| `region` | TEXT | the DAT's own region field, verbatim ("USA", "Japan, Europe") |
| `revision` | TEXT | from the name's tags: Rev 1, v1.1 |
| `languages` | TEXT | from the name's tags: En,Ja |
| `serial` | TEXT | ';'-joined |
| `set_name` | TEXT | arcade and software lists: the set, which is its file's name |
| `parent` | TEXT | the parent set of a clone |
| `header_title` | TEXT | what the media says about itself (media stage) |
| `header_version` | TEXT |  |
| `mastered` | TEXT | the mastering or build date the media carries (media stage) |
| `identity` | TEXT | sha1 of the canonical file, or set1 over its files (https://titlescreen.org/spec/canonical-form), lowercase; NULL when the list gives no sha1 for some file |
| `identity_kind` | TEXT | file, set |
| `size` | INTEGER | bytes of the identity: the file, or the sum of the set's files |
| `fp1` | TEXT | the disc fingerprint (https://titlescreen.org/spec/fp1) of the canonical image, once a scan has read one |
| `round_trip` | TEXT | discs: verified (a scan of a CHD reproduced the list's files), mismatch (a CHD's rebuilt tracks differ: pregap); not published yet (always NULL) |
| `disc` | INTEGER | which disc of a multi-disc game this dump is (1, 2, ...), from the list's name |
| `discs` | INTEGER | how many discs that game's set has; NULL for a single-disc dump |

### listing

a row of a web list, for the platforms with no dump list (PS4, Xbox One, Switch): what a release of those platforms was built from

| column | type | meaning |
|---|---|---|
| `id` | INTEGER |  |
| `key` | TEXT |  |
| `release_id` | TEXT | the first of its releases (listing_release has all) |
| `platform_id` | TEXT |  |
| `name` | TEXT | the list's title |
| `source` | TEXT | wikipedia |

### listing_release

every release a listing belongs to (one per market it was dated in)

| column | type | meaning |
|---|---|---|
| `listing_id` | INTEGER |  |
| `release_id` | TEXT |  |

### dump_release

every release a dump belongs to: a "(Japan, USA)" dump is in the JP and the US release

| column | type | meaning |
|---|---|---|
| `dump_id` | INTEGER |  |
| `release_id` | TEXT |  |

### dump_file

a dump's files, as its DAT lists them (headerless where No-Intro takes checksums headerless)

| column | type | meaning |
|---|---|---|
| `dump_id` | INTEGER |  |
| `name` | TEXT |  |
| `size` | INTEGER |  |
| `crc32` | TEXT |  |
| `sha1` | TEXT |  |
| `md5` | TEXT |  |

### file_hash

every hash known for a dump's files: from the lists, from scans of real copies, from submissions. One table answers every lookup: by sha1 + size, crc32 + size, fp1, or a list's whole-file hash

| column | type | meaning |
|---|---|---|
| `dump_id` | INTEGER |  |
| `seq` | INTEGER | the file's place in the dump in canonical order (track number); 0 for a set or fp row |
| `role` | TEXT | file, set, fp |
| `form` | TEXT | canon1, set1, fp1, file (a list's whole-file hash where it differs from canon1), xiso, decrypted, scrubbed (states of a disc image the lists do not hash) |
| `size` | INTEGER | bytes; the sum for a set; sectors (N) for an fp row |
| `crc32` | TEXT | lowercase hex |
| `sha1` | TEXT |  |
| `md5` | TEXT | of the canonical file; equals the RetroAchievements hash for cartridges |
| `sha256` | TEXT | reserved, empty in version 1 |
| `fp1` | TEXT | the fingerprint, when role = fp |
| `source` | TEXT | dat (a dump list), scan (a copy read by the project's tool), community |
| `status` | TEXT | dat (a list's value), scan-only (a value no list has, read from a copy); later: unverified (one submission), verified (two or more independent ones) |
| `confirmations` | INTEGER | independent observations that agree (0 until submissions are published) |
| `first_seen` | TEXT | ISO date: the build that first carried a list's value |
| `last_seen` | TEXT |  |

### title_alt

a work's titles in other scripts (kana, kanji, hangul), with the sources that give them

| column | type | meaning |
|---|---|---|
| `work_id` | TEXT |  |
| `release_id` | TEXT | set when the title is the release's own (later) |
| `title` | TEXT |  |
| `lang` | TEXT | ja, zh, ko; cjk when a source gave kanji with no language |
| `sources` | TEXT | comma-separated |

### work_join

why releases are one work: every join the roll-up made (a spanning tree of each work), so a wrong merge can be traced to the one join that made it

| column | type | meaning |
|---|---|---|
| `release_a` | TEXT |  |
| `release_b` | TEXT |  |
| `reason` | TEXT | title key, code <c>, translated subtitle, wikidata <Q>, title, developer and year |

### work_link

the join keys third parties want: other databases' ids for a work

| column | type | meaning |
|---|---|---|
| `work_id` | TEXT |  |
| `kind` | TEXT | wikidata, launchbox, gametdb |
| `value` | TEXT |  |

### assertion

the evidence: one row per fact one source states about one dump or listing

| column | type | meaning |
|---|---|---|
| `subject_kind` | TEXT | dump, listing |
| `subject_id` | INTEGER | dump.id or listing.id |
| `field` | TEXT | date, publisher, developer, genre, players_max, franchise, title_ja, wikidata ... |
| `value` | TEXT | normalised: company ids (';'-joined, in the string's order), a genre id, an ISO date |
| `value_raw` | TEXT | as the source wrote it |
| `source` | TEXT | libretro, mame, mame-sl, fbneo, gametdb, wikipedia, wikidata, launchbox, catver |
| `tier` | INTEGER | 0 override, 1 media, 2 dump lists and MAME, 3 press, 4 web databases |
| `how` | TEXT | the join: crc, serial, code, set, self, row, name, main |
| `scope` | TEXT | release (this copy), market:<M> (a region's date), title (the title on the platform) |
| `locator` | TEXT | where to look again: a list entry, a Wikipedia page revision and row, an item id |
| `observed` | TEXT | when the build read it |
| `confidence` | REAL | 1.0 for a checksum or serial join, lower for a name join |

### redirect

ids no longer in use, with the id that absorbed them when two works merged

| column | type | meaning |
|---|---|---|
| `old_id` | TEXT |  |
| `new_id` | TEXT |  |
| `reason` | TEXT | merged, retired |