Přeskočit na hlavní obsah

Databázové schéma

D1 databáze používá SQLite-kompatibilní schéma s následujícími hlavními tabulkami.

Přehled​

Databáze je spravována pomocí migrací v packages/db/migrations/. Každý migrační soubor obsahuje SQL příkazy pro vytvoření nebo úpravu tabulek, indexů a omezení.

Základní tabulky​

users​

Systémoví uživatelé s autentizací a rolemi.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • email (TEXT UNIQUE)
  • password_hash (TEXT)
  • name (TEXT)
  • role (TEXT) - 'Loader', 'Producer', nebo 'Admin'
  • two_factor_enabled (INTEGER)
  • two_factor_method (TEXT) - 'TOTP' nebo 'SMS'
  • totp_secret (TEXT)
  • phone_number (TEXT)
  • sms_code (TEXT) - Přidáno v migraci 0012
  • sms_code_expires_at (INTEGER) - Přidáno v migraci 0012
  • google_id (TEXT)
  • created_at (INTEGER)
  • updated_at (INTEGER)

Indexy:

  • idx_users_email na email

items​

Typy položek (sériové a nesériové).

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • name (TEXT)
  • category_id (INTEGER)
  • is_serialized (INTEGER)
  • notes (TEXT)
  • created_at (INTEGER)
  • updated_at (INTEGER)

Indexy:

  • idx_items_category_id na category_id

assets​

Konkrétní fyzické kusy s QR scan_id.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • item_id (INTEGER)
  • scan_id (INTEGER UNIQUE)
  • functional_status (TEXT) - 'OK_100', 'OK_50', 'NOT_OK'
  • availability_status (TEXT) - 'AVAILABLE', 'ALLOCATED', 'LOST', 'RETIRED'
  • location (TEXT)
  • notes (TEXT)
  • created_at (INTEGER)
  • updated_at (INTEGER)

Indexy:

  • idx_assets_item_id na item_id
  • idx_assets_scan_id na scan_id
  • idx_assets_availability_status na availability_status

stock_qty​

Množství pro nesériové položky.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • item_id (INTEGER UNIQUE)
  • qty (INTEGER)
  • updated_at (INTEGER)

Indexy:

  • idx_stock_qty_item_id na item_id

Kity​

kits_global​

Globální kity (celofiremní).

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • name (TEXT)
  • scan_id (INTEGER UNIQUE)
  • notes (TEXT)
  • created_at (INTEGER)
  • updated_at (INTEGER)

Indexy:

  • idx_kits_global_scan_id na scan_id

kit_global_member_assets​

Konkrétní asset členové globálních kitů.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • kit_id (INTEGER)
  • asset_id (INTEGER)
  • created_at (INTEGER)

Indexy:

  • idx_kit_global_member_assets_kit_id na kit_id
  • idx_kit_global_member_assets_asset_id na asset_id
  • Unikátní omezení na (kit_id, asset_id)

kit_global_member_items​

Nekonkrétní item členové globálních kitů.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • kit_id (INTEGER)
  • item_id (INTEGER)
  • qty (INTEGER)
  • created_at (INTEGER)

Indexy:

  • idx_kit_global_member_items_kit_id na kit_id
  • idx_kit_global_member_items_item_id na item_id
  • Unikátní omezení na (kit_id, item_id)

kits_project​

Projektové kity.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • project_id (INTEGER)
  • name (TEXT)
  • scan_id (INTEGER) - Automaticky nastaven z proxy_asset_id
  • proxy_asset_id (INTEGER) - Reference na asset v kitu, jehož scan_id se použije (migrace 0015)
  • notes (TEXT)
  • created_at (INTEGER)
  • updated_at (INTEGER)

Indexy:

  • idx_kits_project_project_id na project_id
  • idx_kits_project_scan_id na scan_id
  • idx_kits_project_proxy_asset_id na proxy_asset_id (nový)

Poznámka: Scan ID se automaticky nastaví z vybraného proxy assetu v kitu. Ruční zadání scan_id již není podporováno.

kit_project_lines​

Řádky (assety/položky) v projektových kitech.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • kit_id (INTEGER)
  • asset_id (INTEGER)
  • item_id (INTEGER)
  • qty (INTEGER)
  • source_type (TEXT)
  • source_id (INTEGER)
  • created_at (INTEGER)

Indexy:

  • idx_kit_project_lines_kit_id na kit_id
  • idx_kit_project_lines_asset_id na asset_id
  • idx_kit_project_lines_item_id na item_id

Projekty a nakládka​

projects​

Projekty/akce.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • name (TEXT)
  • date_from (INTEGER)
  • date_to (INTEGER)
  • notes (TEXT)
  • created_by_user_id (INTEGER)
  • created_at (INTEGER)
  • updated_at (INTEGER)

Indexy:

  • idx_projects_created_by_user_id na created_by_user_id
  • idx_projects_date_from na date_from
  • idx_projects_date_to na date_to

load_sessions​

Nakládací sessiony (deprecated).

Poznámka: Od migrace 0013 se nakládka provádí přímo na projektech pomocí scan_events.project_id. Tato tabulka je zachována pro zpětnou kompatibilitu, ale nové operace nepoužívají load sessions.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • project_id (INTEGER)
  • status (TEXT) - 'OPEN' nebo 'CLOSED'
  • started_at (INTEGER)
  • started_by_user_id (INTEGER)
  • closed_at (INTEGER)
  • closed_by_user_id (INTEGER)

Indexy:

  • idx_load_sessions_project_id na project_id
  • idx_load_sessions_status na status
  • idx_load_sessions_started_at na started_at

load_plan_lines​

Řádky plánu nakládky (ASSET, ITEM_QTY, GLOBAL_KIT, PROJECT_KIT).

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • session_id (INTEGER)
  • line_type (TEXT) - 'ASSET', 'ITEM_QTY', 'GLOBAL_KIT', 'PROJECT_KIT'
  • ref_id (INTEGER)
  • qty (INTEGER)
  • section (TEXT)
  • created_at (INTEGER)

Indexy:

  • idx_load_plan_lines_session_id na session_id
  • idx_load_plan_lines_line_type na line_type
  • idx_load_plan_lines_ref_id na ref_id

project_plan_lines​

Plán gearu pro projekt (plánování před vytvořením load session).

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • project_id (INTEGER) - ID projektu (cizí klíč na projects.id)
  • line_type (TEXT) - Typ položky ('ASSET', 'ITEM_QTY', 'GLOBAL_KIT', 'PROJECT_KIT')
  • ref_id (INTEGER) - ID reference (podle line_type odkazuje na assets.id, items.id, kits_global.id nebo kits_project.id)
  • qty (INTEGER) - Množství (pouze pro ITEM_QTY)
  • section (TEXT) - Sekce (volitelné)
  • created_at (INTEGER) - Čas vytvoření (Unix timestamp)
  • updated_at (INTEGER) - Čas aktualizace (Unix timestamp) - přidáno v migraci 0029

Indexy:

  • idx_project_plan_lines_project_id na project_id
  • idx_project_plan_lines_line_type na line_type
  • idx_project_plan_lines_ref_id na ref_id

Poznámky:

  • ref_id odkazuje na různé tabulky podle line_type:
    • 'ASSET' → assets.id
    • 'ITEM_QTY' → items.id
    • 'GLOBAL_KIT' → kits_global.id
    • 'PROJECT_KIT' → kits_project.id
  • Sloupec updated_at byl přidán v migraci 0029

scan_events​

Skenovací události (audit log pro nakládací operace).

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • session_id (INTEGER) - Deprecated, používá se project_id
  • project_id (INTEGER) - Přímý vztah s projektem (migrace 0013)
  • event_type (TEXT) - 'SCAN_LOAD', 'SCAN_UNLOAD', 'QTY_ADD', 'QTY_REMOVE', 'QTY_SET', 'PLAN_ADD', 'PLAN_REMOVE'
  • scan_id (INTEGER)
  • asset_id (INTEGER)
  • item_id (INTEGER)
  • qty_delta (INTEGER)
  • user_id (INTEGER)
  • timestamp (INTEGER)

Indexy:

  • idx_scan_events_session_id na session_id (deprecated)
  • idx_scan_events_project_id na project_id (nový)
  • idx_scan_events_asset_id na asset_id
  • idx_scan_events_timestamp na timestamp

Poznámka: Od migrace 0013 se nakládka provádí přímo na projektech pomocí project_id, ne přes load sessions.

Inventura​

inventory_sessions​

Inventurní sessiony.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • date (INTEGER)
  • user_id (INTEGER)
  • notes (TEXT)
  • created_at (INTEGER)

Indexy:

  • idx_inventory_sessions_user_id na user_id
  • idx_inventory_sessions_date na date

inventory_records​

Jednotlivé skeny assetů v inventurních sessionách.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • session_id (INTEGER)
  • asset_id (INTEGER)
  • scanned_at (INTEGER)
  • scan_id (INTEGER)

Indexy:

  • idx_inventory_records_session_id na session_id
  • idx_inventory_records_asset_id na asset_id
  • idx_inventory_records_scanned_at na scanned_at

Autentizace​

user_passkeys​

WebAuthn/Passkeys přihlašovací údaje.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • user_id (INTEGER)
  • credential_id (TEXT UNIQUE)
  • public_key (TEXT)
  • name (TEXT)
  • counter (INTEGER)
  • created_at (INTEGER)
  • last_used_at (INTEGER)

Indexy:

  • idx_user_passkeys_user_id na user_id
  • idx_user_passkeys_credential_id na credential_id

user_sessions​

Sledování 2FA session.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • session_token (TEXT UNIQUE)
  • user_id (INTEGER)
  • two_factor_verified (INTEGER)
  • expires_at (INTEGER)
  • created_at (INTEGER)

Indexy:

  • idx_user_sessions_session_token na session_token
  • idx_user_sessions_user_id na user_id
  • idx_user_sessions_expires_at na expires_at

passkey_challenges​

Passkey registrační/přihlašovací výzvy.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • challenge (TEXT UNIQUE)
  • user_id (INTEGER)
  • email (TEXT)
  • type (TEXT) - 'register' nebo 'login'
  • expires_at (INTEGER)
  • created_at (INTEGER)

Indexy:

  • idx_passkey_challenges_challenge na challenge
  • idx_passkey_challenges_expires_at na expires_at
  • idx_passkey_challenges_user_id na user_id

Poznámka: Vytvořeno v migraci 0012.

oauth_states​

OAuth state tokeny pro CSRF ochranu.

Sloupce:

  • state (TEXT PRIMARY KEY)
  • user_id (INTEGER)
  • expires_at (INTEGER)
  • created_at (INTEGER)

Indexy:

  • idx_oauth_states_expires_at na expires_at

Soubory​

files​

Metadata pro soubory v R2 úložišti.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • r2_key (TEXT UNIQUE)
  • filename (TEXT)
  • mime_type (TEXT)
  • size (INTEGER)
  • file_type (TEXT)
  • entity_type (TEXT)
  • entity_id (INTEGER)
  • uploaded_by_user_id (INTEGER)
  • public_token (TEXT UNIQUE)
  • is_public (INTEGER)
  • created_at (INTEGER)

Indexy:

  • idx_files_r2_key na r2_key
  • idx_files_public_token na public_token
  • idx_files_uploaded_by_user_id na uploaded_by_user_id

Audit​

audit_log​

Systémový audit log pro všechny důležité akce.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • user_id (INTEGER)
  • action (TEXT)
  • entity_type (TEXT)
  • entity_id (INTEGER)
  • changes (TEXT) - JSON řetězec
  • timestamp (INTEGER)
  • ip_address (TEXT)

Indexy:

  • idx_audit_log_user_id na user_id
  • idx_audit_log_entity_type na entity_type
  • idx_audit_log_entity_id na entity_id
  • idx_audit_log_timestamp na timestamp
  • idx_audit_log_action na action

rate_limits​

Počítadla pro rate limiting.

Sloupce:

  • id (INTEGER PRIMARY KEY)
  • identifier (TEXT) - IP adresa nebo user ID
  • timestamp (INTEGER)

Indexy:

  • idx_rate_limits_identifier na identifier
  • idx_rate_limits_timestamp na timestamp
  • idx_rate_limits_identifier_timestamp na (identifier, timestamp)

Vztahy​

  • Users → Projects (created_by_user_id)
  • Projects → Load Sessions (one-to-many)
  • Load Sessions → Load Plan Lines (one-to-many)
  • Load Sessions → Scan Events (one-to-many)
  • Global Kits → Kit Members (assets and items)
  • Project Kits → Kit Lines (assets and items)
  • Inventory Sessions → Inventory Records (one-to-many)
  • Users → Passkeys (one-to-many)
  • Users → Files (uploaded_by_user_id)

Migrace​

Migrace jsou umístěny v packages/db/migrations/:

  1. 0001_initial_schema.sql - Základní tabulky (users, items, assets, projects, atd.)
  2. 0002_import_inventory.sql - Inventurní tabulky
  3. 0003_oauth_and_2fa.sql - OAuth a 2FA tabulky
  4. 0004_rate_limits.sql - Tabulka pro rate limiting

Spuštění migrací​

Lokálně (vývoj):

cd workers/api
wrangler d1 migrations apply dna-cruises-dev --local

Vzdáleně (produkce):

cd workers/api
wrangler d1 migrations apply dna-cruises-prod --remote

Indexy a optimalizace​

Všechny tabulky mají vhodné indexy pro:

  • Vyhledávání podle cizích klíčů
  • Běžné dotazovací vzory
  • Operace řazení
  • Časově založené dotazy

Indexy jsou vytvářeny v migračních souborech a automaticky aplikovány při spuštění migrací.

Osvědčené postupy​

  1. Vždy používejte prepared statements - Zabraňuje SQL injection
  2. Používejte indexy - Zajistěte, aby dotazy používaly indexované sloupce
  3. Omezení cizích klíčů - Udržujte integritu dat
  4. Časové značky - Používejte Unix timestampy (sekundy od epochy)
  5. Zpracování NULL - Používejte NULL pro volitelná pole, ne prázdné řetězce
  6. Textová pole - Sanitizujte před uložením, abyste zabránili XSS

Související témata​