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.
- `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.

### `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 |  |  |
| 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:	988
Committed:	Sun Mar 8 04:15:07 2026 UTC (2 weeks, 6 days ago) by nino.borges
Content type:	text/markdown
File size:	8214 byte(s)
Log Message:	Added a database dictionary markdown file which attempts to describe at a highlevel the database, tables, fields and the reason for these. also updaed the models.py file to reflect the updated database, since it was so out of date. I'm currently not using it beyond documenting the schema. also added a Projet Guidelines file and lessons learned file. These are my attempt at tryiing out spec-driven development. Projectguidelines is the core guide that will act as the source of truth of the application.
#	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			- `description`: Curated/local description text.
57			- `description_scraped`: Imported/scraped description text.
58			- `system_console`: Foreign key-like system pointer (`main_app_system.id`).
59			- `system_console_scraped`: Scraped/alternate system text.
60			- `path_to_screenshot_box`: Relative/absolute path to box art.
61			- `path_to_screenshot_title`: Relative/absolute path to title-screen art.
62			- `path_to_screenshot_ingame`: Relative/absolute path to in-game art.
63			- `path_to_video`: Optional media/video path.
64			- `user_notes`: User-authored notes.
65			- `container_md5_hash`: Primary hash used for de-duplication and metadata matching.
66			- `version`: Version/revision marker for the entry.
67
68			### `main_app_file_hash`
69			- `id`: Stable key for a hash record.
70			- `file_name`: Filename associated with the hashed file.
71			- `file_md5_hash`: Hash value used to link into metadata tables.
72			- `container_file_id`: Parent `main_app.id` for this hash entry.
73
74			### `no_intro_system`
75			- `id`: No-Intro system identifier from DAT header.
76			- `name`: DAT system name.
77			- `description`: DAT system description.
78			- `dat_version`: DAT version string.
79
80			### `no_intro_main`
81			- `game_name`: DAT game title.
82			- `no_intro_id`: No-Intro game identifier.
83			- `clone_of_id`: Parent/clone relationship indicator.
84			- `description`: DAT game description.
85			- `rom_name`: ROM filename from DAT.
86			- `crc`: DAT CRC.
87			- `md5`: DAT MD5 (primary matching key used by app).
88			- `sha1`: DAT SHA1.
89			- `sha256`: DAT SHA256.
90			- `status`: DAT ROM status.
91			- `no_intro_system_id`: Links entry to No-Intro system metadata.
92			- `app_system_id`: Scopes entry to a Gromulus app system for safer matching.
93
94			### `tosec_main`
95			- `game_name`: DAT game title.
96			- `description`: DAT game description.
97			- `rom_name`: ROM filename from DAT.
98			- `crc`: DAT CRC.
99			- `md5`: DAT MD5 (primary matching key used by app).
100			- `sha1`: DAT SHA1.
101			- `system_id`: DAT system identifier.
102			- `app_system_id`: Scopes entry to a Gromulus app system for safer matching.
103
104			### `dat_import_history`
105			- `id`: Stable key for history record.
106			- `app_system_id`: Which app system this import belongs to.
107			- `source`: Import source (`no_intro` or `tosec`).
108			- `dat_name`: DAT name from header.
109			- `dat_description`: DAT description from header.
110			- `dat_version`: DAT version from header.
111			- `imported_at`: Import timestamp.
112			- `entry_count`: Number of rows imported during that run.
113
114			## Technical Schema (Current)
115
116			### `main_app_system`
117			\| Column \| Type \| Null \| Key \| Notes \|
118			\|---\|---\|---\|---\|---\|
119			\| id \| INTEGER \| No \| PK \| AUTOINCREMENT \|
120			\| name \| TEXT \| Yes \| \| \|
121			\| short_name \| TEXT \| Yes \| \| \|
122			\| relative_file_path \| TEXT \| Yes \| \| \|
123
124			### `main_app`
125			\| Column \| Type \| Null \| Key \| Notes \|
126			\|---\|---\|---\|---\|---\|
127			\| id \| INTEGER \| No \| PK \| AUTOINCREMENT \|
128			\| game_name \| TEXT \| Yes \| \| \|
129			\| game_name_scraped \| TEXT \| Yes \| \| \|
130			\| container_file_name \| TEXT \| Yes \| \| \|
131			\| description \| TEXT \| Yes \| \| \|
132			\| description_scraped \| TEXT \| Yes \| \| \|
133			\| system_console \| INTEGER \| Yes \| \| App system pointer \|
134			\| system_console_scraped \| TEXT \| Yes \| \| \|
135			\| path_to_screenshot_box \| TEXT \| Yes \| \| \|
136			\| path_to_screenshot_title \| TEXT \| Yes \| \| \|
137			\| path_to_screenshot_ingame \| TEXT \| Yes \| \| \|
138			\| path_to_video \| TEXT \| Yes \| \| \|
139			\| user_notes \| TEXT \| Yes \| \| \|
140			\| container_md5_hash \| TEXT \| Yes \| \| Indexed \|
141			\| version \| TEXT \| Yes \| \| \|
142
143			### `main_app_file_hash`
144			\| Column \| Type \| Null \| Key \| Notes \|
145			\|---\|---\|---\|---\|---\|
146			\| id \| INTEGER \| No \| PK \| AUTOINCREMENT \|
147			\| file_name \| TEXT \| Yes \| \| \|
148			\| file_md5_hash \| TEXT \| Yes \| \| \|
149			\| container_file_id \| INTEGER \| No \| FK \| References `main_app.id` \|
150
151			### `no_intro_system`
152			\| Column \| Type \| Null \| Key \| Notes \|
153			\|---\|---\|---\|---\|---\|
154			\| id \| INTEGER \| No \| PK \| Not autoincremented in canonical DDL \|
155			\| name \| TEXT \| Yes \| \| \|
156			\| description \| TEXT \| Yes \| \| \|
157			\| dat_version \| TEXT \| Yes \| \| \|
158
159			### `no_intro_main`
160			\| Column \| Type \| Null \| Key \| Notes \|
161			\|---\|---\|---\|---\|---\|
162			\| game_name \| TEXT \| Yes \| \| \|
163			\| no_intro_id \| TEXT \| Yes \| \| \|
164			\| clone_of_id \| TEXT \| Yes \| \| \|
165			\| description \| TEXT \| Yes \| \| \|
166			\| rom_name \| TEXT \| Yes \| \| \|
167			\| crc \| TEXT \| Yes \| \| \|
168			\| md5 \| TEXT \| Yes \| \| Indexed \|
169			\| sha1 \| TEXT \| Yes \| \| \|
170			\| sha256 \| TEXT \| Yes \| \| \|
171			\| status \| TEXT \| Yes \| \| \|
172			\| no_intro_system_id \| INTEGER \| No \| FK \| References `no_intro_system.id` \|
173			\| app_system_id \| INTEGER \| Yes \| \| Indexed with `md5` \|
174
175			Note: this table currently has no declared primary key.
176
177			### `tosec_main`
178			\| Column \| Type \| Null \| Key \| Notes \|
179			\|---\|---\|---\|---\|---\|
180			\| game_name \| TEXT \| Yes \| \| \|
181			\| description \| TEXT \| Yes \| \| \|
182			\| rom_name \| TEXT \| Yes \| \| \|
183			\| crc \| TEXT \| Yes \| \| \|
184			\| md5 \| TEXT \| Yes \| \| Indexed \|
185			\| sha1 \| TEXT \| Yes \| \| \|
186			\| system_id \| INTEGER \| No \| FK \| References `no_intro_system.id` \|
187			\| app_system_id \| INTEGER \| Yes \| \| Indexed with `md5` \|
188
189			Note: this table currently has no declared primary key.
190
191			### `dat_import_history`
192			\| Column \| Type \| Null \| Key \| Notes \|
193			\|---\|---\|---\|---\|---\|
194			\| id \| INTEGER \| No \| PK \| AUTOINCREMENT \|
195			\| app_system_id \| INTEGER \| No \| FK \| References `main_app_system.id` \|
196			\| source \| TEXT \| No \| \| \|
197			\| dat_name \| TEXT \| Yes \| \| \|
198			\| dat_description \| TEXT \| Yes \| \| \|
199			\| dat_version \| TEXT \| Yes \| \| \|
200			\| imported_at \| TEXT \| No \| \| \|
201			\| entry_count \| INTEGER \| No \| \| Default `0` \|
202
203			Unique constraint:
204			- (`app_system_id`, `source`)
205
206			## Indexes (Current)
207			- `idx_main_app_md5` on `main_app(container_md5_hash)`
208			- `idx_main_app_system` on `main_app(system_console)`
209			- `idx_no_intro_md5` on `no_intro_main(md5)`
210			- `idx_tosec_md5` on `tosec_main(md5)`
211			- `idx_no_intro_system_md5` on `no_intro_main(app_system_id, md5)`
212			- `idx_tosec_system_md5` on `tosec_main(app_system_id, md5)`
213			- `idx_dat_import_system_source` on `dat_import_history(app_system_id, source)`