Database Model
==============

The webapp uses SQLite with two metadata tables plus dynamically created text
value tables.

Metadata tables:

- ``imported_packages``
  Tracks each imported metadata/zip pair.
- ``package_tables``
  Tracks source txt filenames and associated physical SQLite table names.

Metadata indexes:

- ``idx_package_tables_package_id``
  Optimizes package-scoped table lookups.
- ``idx_package_tables_package_id_source_txt``
  Optimizes package + source filename lookups.
- ``idx_imported_packages_imported_at``
  Optimizes recency-oriented package browsing.

Dynamic tables:

- One physical table per imported ``*.txt`` file.
- Schema: ``id``, ``line_number``, ``value``.

Connection defaults:

- ``PRAGMA foreign_keys = ON``
- ``PRAGMA journal_mode = WAL``
- ``PRAGMA synchronous = NORMAL``
- ``PRAGMA busy_timeout = 5000``

Import behavior summary:

- Metadata ``files_included`` (when present) limits which ``*.txt`` entries are
  imported from the ZIP.
- JSON files in the ZIP are currently ignored by importer persistence.
- Duplicate metadata+zip imports are rejected via uniqueness constraints.