Active_prgs/Gromulus/Database%20Dictonary.md

# Database Dictonary

## Overview
This document describes the current Gromulus database in two ways:
1. High-level intent: why each table and field exists.
2. Technical schema: the concrete table/column definitions used by the app.

Gromulus tracks ROM containers, system ownership, metadata imported from DAT files (No-Intro and TOSEC), and artwork associations used by the UI.

## High-Level Model

### Database Purpose
The database is the source of truth for:
- What files are in the collection.
- Which platform/system a file belongs to.
- What that file matches to in external metadata sources.
- What artwork has already been attached.
- What DAT imports are current for each system.

### Table Purpose (Business View)

#### `main_app_system`
Represents each supported platform (for example SNES, NES, Genesis). It exists so ROM rows and imported metadata can be scoped to a specific system.

#### `main_app`
Represents a managed ROM container in the local collection. It is the core app table used by list views, game detail screens, hash matching, and artwork paths.

#### `main_app_file_hash`
Stores hash/file-name records tied to a `main_app` row. This supports stable matching when filenames differ and allows current logic to pick a preferred hash record for joins.

#### `no_intro_system`
Represents No-Intro DAT header/system metadata. It provides a reference target for No-Intro entries.

#### `no_intro_main`
Stores No-Intro game/ROM entries imported from DAT files. The app uses this table mainly for hash-to-title matching and naming enrichment.

#### `tosec_main`
Stores TOSEC game/ROM entries imported from DAT files. Similar to `no_intro_main`, this table enriches collection entries using hash matches.

#### `dat_import_history`
Tracks the most recent DAT import per app system and source (`no_intro` or `tosec`), including version/timestamp/count for traceability.

## Field Purpose (Why the Fields Exist)

### `main_app_system`
- `id`: Stable internal key for one system.
- `name`: Human-readable system name for UI.
- `short_name`: Compact system identifier used in UI toggles and path conventions.
- `relative_file_path`: System-specific ROM location under the configured backup root.

### `main_app`
- `id`: Stable key for one managed ROM container.
- `game_name`: Curated/local display name.
- `game_name_scraped`: Scraped/alternate name from external sources.
- `container_file_name`: Stored ROM container filename.
- `favorite_game`: User-controlled favorite flag (`1` favorite, `0`/blank not favorite).
- `release_date`: User override release date (`YYYY-MM-DD` storage format).
- `release_date_scraped`: Scraped release date (`YYYY-MM-DD` expected).
- `game_genre`: User override genre value.
- `game_genre_scraped`: Scraped genre value.
- `cooperative`: User override cooperative value (`Yes`/`No`).
- `cooperative_scraped`: Scraped cooperative value (`Yes`/`No`).
- `max_players`: User override max players value.
- `max_players_scraped`: Scraped max players value.
- `description`: Curated/local description text.
- `description_scraped`: Imported/scraped description text.
- `system_console`: Foreign key-like system pointer (`main_app_system.id`).
- `system_console_scraped`: Scraped/alternate system text.
- `path_to_screenshot_box`: Relative/absolute path to box art.
- `path_to_screenshot_title`: Relative/absolute path to title-screen art.
- `path_to_screenshot_ingame`: Relative/absolute path to in-game art.
- `path_to_video`: Optional media/video path.
- `user_notes`: User-authored notes.
- `container_md5_hash`: Primary hash used for de-duplication and metadata matching.
- `version`: Version/revision marker for the entry.

Metadata precedence in UI:
- For dual fields, app uses user field first, then `_scraped` fallback:
`release_date`, `game_genre`, `cooperative`, `max_players`, `description`.

### `main_app_file_hash`
- `id`: Stable key for a hash record.
- `file_name`: Filename associated with the hashed file.
- `file_md5_hash`: Hash value used to link into metadata tables.
- `container_file_id`: Parent `main_app.id` for this hash entry.

### `no_intro_system`
- `id`: No-Intro system identifier from DAT header.
- `name`: DAT system name.
- `description`: DAT system description.
- `dat_version`: DAT version string.

### `no_intro_main`
- `game_name`: DAT game title.
- `no_intro_id`: No-Intro game identifier.
- `clone_of_id`: Parent/clone relationship indicator.
- `description`: DAT game description.
- `rom_name`: ROM filename from DAT.
- `crc`: DAT CRC.
- `md5`: DAT MD5 (primary matching key used by app).
- `sha1`: DAT SHA1.
- `sha256`: DAT SHA256.
- `status`: DAT ROM status.
- `no_intro_system_id`: Links entry to No-Intro system metadata.
- `app_system_id`: Scopes entry to a Gromulus app system for safer matching.

### `tosec_main`
- `game_name`: DAT game title.
- `description`: DAT game description.
- `rom_name`: ROM filename from DAT.
- `crc`: DAT CRC.
- `md5`: DAT MD5 (primary matching key used by app).
- `sha1`: DAT SHA1.
- `system_id`: DAT system identifier.
- `app_system_id`: Scopes entry to a Gromulus app system for safer matching.

### `dat_import_history`
- `id`: Stable key for history record.
- `app_system_id`: Which app system this import belongs to.
- `source`: Import source (`no_intro` or `tosec`).
- `dat_name`: DAT name from header.
- `dat_description`: DAT description from header.
- `dat_version`: DAT version from header.
- `imported_at`: Import timestamp.
- `entry_count`: Number of rows imported during that run.

## Technical Schema (Current)

### `main_app_system`
| Column | Type | Null | Key | Notes |
|---|---|---|---|---|
| id | INTEGER | No | PK | AUTOINCREMENT |
| name | TEXT | Yes |  |  |
| short_name | TEXT | Yes |  |  |
| relative_file_path | TEXT | Yes |  |  |

### `main_app`
| Column | Type | Null | Key | Notes |
|---|---|---|---|---|
| id | INTEGER | No | PK | AUTOINCREMENT |
| game_name | TEXT | Yes |  |  |
| game_name_scraped | TEXT | Yes |  |  |
| container_file_name | TEXT | Yes |  |  |
| favorite_game | INTEGER | Yes |  | User favorite flag |
| release_date | TEXT | Yes |  | User value, `YYYY-MM-DD` |
| release_date_scraped | TEXT | Yes |  | Scraped value, `YYYY-MM-DD` |
| game_genre | TEXT | Yes |  | User value |
| game_genre_scraped | TEXT | Yes |  | Scraped value |
| cooperative | TEXT | Yes |  | User value, `Yes`/`No` |
| cooperative_scraped | TEXT | Yes |  | Scraped value, `Yes`/`No` |
| max_players | TEXT | Yes |  | User value |
| max_players_scraped | TEXT | Yes |  | Scraped value |
| description | TEXT | Yes |  |  |
| description_scraped | TEXT | Yes |  |  |
| system_console | INTEGER | Yes |  | App system pointer |
| system_console_scraped | TEXT | Yes |  |  |
| path_to_screenshot_box | TEXT | Yes |  |  |
| path_to_screenshot_title | TEXT | Yes |  |  |
| path_to_screenshot_ingame | TEXT | Yes |  |  |
| path_to_video | TEXT | Yes |  |  |
| user_notes | TEXT | Yes |  |  |
| container_md5_hash | TEXT | Yes |  | Indexed |
| version | TEXT | Yes |  |  |

### `main_app_file_hash`
| Column | Type | Null | Key | Notes |
|---|---|---|---|---|
| id | INTEGER | No | PK | AUTOINCREMENT |
| file_name | TEXT | Yes |  |  |
| file_md5_hash | TEXT | Yes |  |  |
| container_file_id | INTEGER | No | FK | References `main_app.id` |

### `no_intro_system`
| Column | Type | Null | Key | Notes |
|---|---|---|---|---|
| id | INTEGER | No | PK | Not autoincremented in canonical DDL |
| name | TEXT | Yes |  |  |
| description | TEXT | Yes |  |  |
| dat_version | TEXT | Yes |  |  |

### `no_intro_main`
| Column | Type | Null | Key | Notes |
|---|---|---|---|---|
| game_name | TEXT | Yes |  |  |
| no_intro_id | TEXT | Yes |  |  |
| clone_of_id | TEXT | Yes |  |  |
| description | TEXT | Yes |  |  |
| rom_name | TEXT | Yes |  |  |
| crc | TEXT | Yes |  |  |
| md5 | TEXT | Yes |  | Indexed |
| sha1 | TEXT | Yes |  |  |
| sha256 | TEXT | Yes |  |  |
| status | TEXT | Yes |  |  |
| no_intro_system_id | INTEGER | No | FK | References `no_intro_system.id` |
| app_system_id | INTEGER | Yes |  | Indexed with `md5` |

Note: this table currently has no declared primary key.

### `tosec_main`
| Column | Type | Null | Key | Notes |
|---|---|---|---|---|
| game_name | TEXT | Yes |  |  |
| description | TEXT | Yes |  |  |
| rom_name | TEXT | Yes |  |  |
| crc | TEXT | Yes |  |  |
| md5 | TEXT | Yes |  | Indexed |
| sha1 | TEXT | Yes |  |  |
| system_id | INTEGER | No | FK | References `no_intro_system.id` |
| app_system_id | INTEGER | Yes |  | Indexed with `md5` |

Note: this table currently has no declared primary key.

### `dat_import_history`
| Column | Type | Null | Key | Notes |
|---|---|---|---|---|
| id | INTEGER | No | PK | AUTOINCREMENT |
| app_system_id | INTEGER | No | FK | References `main_app_system.id` |
| source | TEXT | No |  |  |
| dat_name | TEXT | Yes |  |  |
| dat_description | TEXT | Yes |  |  |
| dat_version | TEXT | Yes |  |  |
| imported_at | TEXT | No |  |  |
| entry_count | INTEGER | No |  | Default `0` |

Unique constraint:
- (`app_system_id`, `source`)

## Indexes (Current)
- `idx_main_app_md5` on `main_app(container_md5_hash)`
- `idx_main_app_system` on `main_app(system_console)`
- `idx_no_intro_md5` on `no_intro_main(md5)`
- `idx_tosec_md5` on `tosec_main(md5)`
- `idx_no_intro_system_md5` on `no_intro_main(app_system_id, md5)`
- `idx_tosec_system_md5` on `tosec_main(app_system_id, md5)`
- `idx_dat_import_system_source` on `dat_import_history(app_system_id, source)`
Revision:	990
Committed:	Mon Mar 9 21:20:37 2026 UTC (2 weeks, 4 days ago) by nino.borges
Content type:	text/markdown
File size:	9458 byte(s)
Log Message:	Gromulus v1.5: user metadata save workflow, schema expansion, and UI refactor - Added user-editable game metadata workflow with explicit Save button (no autosave). - Added dirty-state tracking and Save/Discard/Cancel prompts when navigating away or closing with unsaved changes. - Added DB schema support for new metadata fields: - - , - , - , - , - Wired UI to user-first metadata precedence with fallback for release date, genre, cooperative, max players, and description. - Added release date display/storage conversion: - GUI display - DB storage - Refactored main game info panel: - moved hash/file/No-Intro/TOSEC detail fields into modal - added compact TOSEC/NoIntro match indicators - added Favorite, Release Date, Genre, Cooperative, Max Players, Description, and User Notes controls - Enhanced artwork previews from prior update: - larger preview boxes - aspect-ratio-preserving scaling - click-to-open full-size modal viewer - Updated schema/documentation files to stay aligned: - - - canonical DB utility schema/migration logic
#	User	Rev	Content
1	nino.borges	988	# Database Dictonary
2
3			## Overview
4			This document describes the current Gromulus database in two ways:
5			1. High-level intent: why each table and field exists.
6			2. Technical schema: the concrete table/column definitions used by the app.
7
8			Gromulus tracks ROM containers, system ownership, metadata imported from DAT files (No-Intro and TOSEC), and artwork associations used by the UI.
9
10			## High-Level Model
11
12			### Database Purpose
13			The database is the source of truth for:
14			- What files are in the collection.
15			- Which platform/system a file belongs to.
16			- What that file matches to in external metadata sources.
17			- What artwork has already been attached.
18			- What DAT imports are current for each system.
19
20			### Table Purpose (Business View)
21
22			#### `main_app_system`
23			Represents each supported platform (for example SNES, NES, Genesis). It exists so ROM rows and imported metadata can be scoped to a specific system.
24
25			#### `main_app`
26			Represents a managed ROM container in the local collection. It is the core app table used by list views, game detail screens, hash matching, and artwork paths.
27
28			#### `main_app_file_hash`
29			Stores hash/file-name records tied to a `main_app` row. This supports stable matching when filenames differ and allows current logic to pick a preferred hash record for joins.
30
31			#### `no_intro_system`
32			Represents No-Intro DAT header/system metadata. It provides a reference target for No-Intro entries.
33
34			#### `no_intro_main`
35			Stores No-Intro game/ROM entries imported from DAT files. The app uses this table mainly for hash-to-title matching and naming enrichment.
36
37			#### `tosec_main`
38			Stores TOSEC game/ROM entries imported from DAT files. Similar to `no_intro_main`, this table enriches collection entries using hash matches.
39
40			#### `dat_import_history`
41			Tracks the most recent DAT import per app system and source (`no_intro` or `tosec`), including version/timestamp/count for traceability.
42
43			## Field Purpose (Why the Fields Exist)
44
45			### `main_app_system`
46			- `id`: Stable internal key for one system.
47			- `name`: Human-readable system name for UI.
48			- `short_name`: Compact system identifier used in UI toggles and path conventions.
49			- `relative_file_path`: System-specific ROM location under the configured backup root.
50
51			### `main_app`
52			- `id`: Stable key for one managed ROM container.
53			- `game_name`: Curated/local display name.
54			- `game_name_scraped`: Scraped/alternate name from external sources.
55			- `container_file_name`: Stored ROM container filename.
56	nino.borges	990	- `favorite_game`: User-controlled favorite flag (`1` favorite, `0`/blank not favorite).
57			- `release_date`: User override release date (`YYYY-MM-DD` storage format).
58			- `release_date_scraped`: Scraped release date (`YYYY-MM-DD` expected).
59			- `game_genre`: User override genre value.
60			- `game_genre_scraped`: Scraped genre value.
61			- `cooperative`: User override cooperative value (`Yes`/`No`).
62			- `cooperative_scraped`: Scraped cooperative value (`Yes`/`No`).
63			- `max_players`: User override max players value.
64			- `max_players_scraped`: Scraped max players value.
65	nino.borges	988	- `description`: Curated/local description text.
66			- `description_scraped`: Imported/scraped description text.
67			- `system_console`: Foreign key-like system pointer (`main_app_system.id`).
68			- `system_console_scraped`: Scraped/alternate system text.
69			- `path_to_screenshot_box`: Relative/absolute path to box art.
70			- `path_to_screenshot_title`: Relative/absolute path to title-screen art.
71			- `path_to_screenshot_ingame`: Relative/absolute path to in-game art.
72			- `path_to_video`: Optional media/video path.
73			- `user_notes`: User-authored notes.
74			- `container_md5_hash`: Primary hash used for de-duplication and metadata matching.
75			- `version`: Version/revision marker for the entry.
76
77	nino.borges	990	Metadata precedence in UI:
78			- For dual fields, app uses user field first, then `_scraped` fallback:
79			`release_date`, `game_genre`, `cooperative`, `max_players`, `description`.
80
81	nino.borges	988	### `main_app_file_hash`
82			- `id`: Stable key for a hash record.
83			- `file_name`: Filename associated with the hashed file.
84			- `file_md5_hash`: Hash value used to link into metadata tables.
85			- `container_file_id`: Parent `main_app.id` for this hash entry.
86
87			### `no_intro_system`
88			- `id`: No-Intro system identifier from DAT header.
89			- `name`: DAT system name.
90			- `description`: DAT system description.
91			- `dat_version`: DAT version string.
92
93			### `no_intro_main`
94			- `game_name`: DAT game title.
95			- `no_intro_id`: No-Intro game identifier.
96			- `clone_of_id`: Parent/clone relationship indicator.
97			- `description`: DAT game description.
98			- `rom_name`: ROM filename from DAT.
99			- `crc`: DAT CRC.
100			- `md5`: DAT MD5 (primary matching key used by app).
101			- `sha1`: DAT SHA1.
102			- `sha256`: DAT SHA256.
103			- `status`: DAT ROM status.
104			- `no_intro_system_id`: Links entry to No-Intro system metadata.
105			- `app_system_id`: Scopes entry to a Gromulus app system for safer matching.
106
107			### `tosec_main`
108			- `game_name`: DAT game title.
109			- `description`: DAT game description.
110			- `rom_name`: ROM filename from DAT.
111			- `crc`: DAT CRC.
112			- `md5`: DAT MD5 (primary matching key used by app).
113			- `sha1`: DAT SHA1.
114			- `system_id`: DAT system identifier.
115			- `app_system_id`: Scopes entry to a Gromulus app system for safer matching.
116
117			### `dat_import_history`
118			- `id`: Stable key for history record.
119			- `app_system_id`: Which app system this import belongs to.
120			- `source`: Import source (`no_intro` or `tosec`).
121			- `dat_name`: DAT name from header.
122			- `dat_description`: DAT description from header.
123			- `dat_version`: DAT version from header.
124			- `imported_at`: Import timestamp.
125			- `entry_count`: Number of rows imported during that run.
126
127			## Technical Schema (Current)
128
129			### `main_app_system`
130			\| Column \| Type \| Null \| Key \| Notes \|
131			\|---\|---\|---\|---\|---\|
132			\| id \| INTEGER \| No \| PK \| AUTOINCREMENT \|
133			\| name \| TEXT \| Yes \| \| \|
134			\| short_name \| TEXT \| Yes \| \| \|
135			\| relative_file_path \| TEXT \| Yes \| \| \|
136
137			### `main_app`
138			\| Column \| Type \| Null \| Key \| Notes \|
139			\|---\|---\|---\|---\|---\|
140			\| id \| INTEGER \| No \| PK \| AUTOINCREMENT \|
141			\| game_name \| TEXT \| Yes \| \| \|
142			\| game_name_scraped \| TEXT \| Yes \| \| \|
143			\| container_file_name \| TEXT \| Yes \| \| \|
144	nino.borges	990	\| favorite_game \| INTEGER \| Yes \| \| User favorite flag \|
145			\| release_date \| TEXT \| Yes \| \| User value, `YYYY-MM-DD` \|
146			\| release_date_scraped \| TEXT \| Yes \| \| Scraped value, `YYYY-MM-DD` \|
147			\| game_genre \| TEXT \| Yes \| \| User value \|
148			\| game_genre_scraped \| TEXT \| Yes \| \| Scraped value \|
149			\| cooperative \| TEXT \| Yes \| \| User value, `Yes`/`No` \|
150			\| cooperative_scraped \| TEXT \| Yes \| \| Scraped value, `Yes`/`No` \|
151			\| max_players \| TEXT \| Yes \| \| User value \|
152			\| max_players_scraped \| TEXT \| Yes \| \| Scraped value \|
153	nino.borges	988	\| description \| TEXT \| Yes \| \| \|
154			\| description_scraped \| TEXT \| Yes \| \| \|
155			\| system_console \| INTEGER \| Yes \| \| App system pointer \|
156			\| system_console_scraped \| TEXT \| Yes \| \| \|
157			\| path_to_screenshot_box \| TEXT \| Yes \| \| \|
158			\| path_to_screenshot_title \| TEXT \| Yes \| \| \|
159			\| path_to_screenshot_ingame \| TEXT \| Yes \| \| \|
160			\| path_to_video \| TEXT \| Yes \| \| \|
161			\| user_notes \| TEXT \| Yes \| \| \|
162			\| container_md5_hash \| TEXT \| Yes \| \| Indexed \|
163			\| version \| TEXT \| Yes \| \| \|
164
165			### `main_app_file_hash`
166			\| Column \| Type \| Null \| Key \| Notes \|
167			\|---\|---\|---\|---\|---\|
168			\| id \| INTEGER \| No \| PK \| AUTOINCREMENT \|
169			\| file_name \| TEXT \| Yes \| \| \|
170			\| file_md5_hash \| TEXT \| Yes \| \| \|
171			\| container_file_id \| INTEGER \| No \| FK \| References `main_app.id` \|
172
173			### `no_intro_system`
174			\| Column \| Type \| Null \| Key \| Notes \|
175			\|---\|---\|---\|---\|---\|
176			\| id \| INTEGER \| No \| PK \| Not autoincremented in canonical DDL \|
177			\| name \| TEXT \| Yes \| \| \|
178			\| description \| TEXT \| Yes \| \| \|
179			\| dat_version \| TEXT \| Yes \| \| \|
180
181			### `no_intro_main`
182			\| Column \| Type \| Null \| Key \| Notes \|
183			\|---\|---\|---\|---\|---\|
184			\| game_name \| TEXT \| Yes \| \| \|
185			\| no_intro_id \| TEXT \| Yes \| \| \|
186			\| clone_of_id \| TEXT \| Yes \| \| \|
187			\| description \| TEXT \| Yes \| \| \|
188			\| rom_name \| TEXT \| Yes \| \| \|
189			\| crc \| TEXT \| Yes \| \| \|
190			\| md5 \| TEXT \| Yes \| \| Indexed \|
191			\| sha1 \| TEXT \| Yes \| \| \|
192			\| sha256 \| TEXT \| Yes \| \| \|
193			\| status \| TEXT \| Yes \| \| \|
194			\| no_intro_system_id \| INTEGER \| No \| FK \| References `no_intro_system.id` \|
195			\| app_system_id \| INTEGER \| Yes \| \| Indexed with `md5` \|
196
197			Note: this table currently has no declared primary key.
198
199			### `tosec_main`
200			\| Column \| Type \| Null \| Key \| Notes \|
201			\|---\|---\|---\|---\|---\|
202			\| game_name \| TEXT \| Yes \| \| \|
203			\| description \| TEXT \| Yes \| \| \|
204			\| rom_name \| TEXT \| Yes \| \| \|
205			\| crc \| TEXT \| Yes \| \| \|
206			\| md5 \| TEXT \| Yes \| \| Indexed \|
207			\| sha1 \| TEXT \| Yes \| \| \|
208			\| system_id \| INTEGER \| No \| FK \| References `no_intro_system.id` \|
209			\| app_system_id \| INTEGER \| Yes \| \| Indexed with `md5` \|
210
211			Note: this table currently has no declared primary key.
212
213			### `dat_import_history`
214			\| Column \| Type \| Null \| Key \| Notes \|
215			\|---\|---\|---\|---\|---\|
216			\| id \| INTEGER \| No \| PK \| AUTOINCREMENT \|
217			\| app_system_id \| INTEGER \| No \| FK \| References `main_app_system.id` \|
218			\| source \| TEXT \| No \| \| \|
219			\| dat_name \| TEXT \| Yes \| \| \|
220			\| dat_description \| TEXT \| Yes \| \| \|
221			\| dat_version \| TEXT \| Yes \| \| \|
222			\| imported_at \| TEXT \| No \| \| \|
223			\| entry_count \| INTEGER \| No \| \| Default `0` \|
224
225			Unique constraint:
226			- (`app_system_id`, `source`)
227
228			## Indexes (Current)
229			- `idx_main_app_md5` on `main_app(container_md5_hash)`
230			- `idx_main_app_system` on `main_app(system_console)`
231			- `idx_no_intro_md5` on `no_intro_main(md5)`
232			- `idx_tosec_md5` on `tosec_main(md5)`
233			- `idx_no_intro_system_md5` on `no_intro_main(app_system_id, md5)`
234			- `idx_tosec_system_md5` on `tosec_main(app_system_id, md5)`
235			- `idx_dat_import_system_source` on `dat_import_history(app_system_id, source)`