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:	991
Committed:	Mon Mar 9 21:52:02 2026 UTC (2 weeks, 4 days ago) by nino.borges
Content type:	text/markdown
File size:	9460 byte(s)
Log Message:	I meant to have this as the commit message for 1.5 but I had an issue wiht the message. so I added some spaces to the files to force another commit. 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: - favorite_game - release_date, release_date_scraped - game_genre, game_genre_scraped - cooperative, cooperative_scraped - max_players, max_players_scraped - Wired UI to user-first metadata precedence with _scraped fallback for release date, genre, cooperative, max players, and description. - Added release date display/storage conversion: - GUI display MM-DD-YYYY - DB storage YYYY-MM-DD - Refactored main game info panel: - moved hash/file/No-Intro/TOSEC detail fields into Reports -> Game Properties 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: - models.py - Database Dictonary.md - canonical DB utility schema/migration logic
#	Content
1	# 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	- `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	- `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	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	### `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	\| 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	\| 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)`
236
237