Skip to content

Reference: Database Schema

SafeMode uses SQLite with WAL mode for better concurrent access.

Implementation: src/url_monitor/core/database.py

Connection Behavior

  • check_same_thread=False for multi-thread/process scenarios.
  • PRAGMA journal_mode=WAL enabled.
  • timeout configured to avoid immediate lock failures.

Tables

alerts

Stores security events that reached alert persistence.

Columns:

  • id INTEGER PK
  • url TEXT
  • host TEXT
  • secrets TEXT (JSON payload)
  • timestamp TEXT
  • seen INTEGER default 0
  • alert_type TEXT default secret

whitelist

Stores user-approved domains/URLs.

Columns:

  • id INTEGER PK
  • type TEXT (domain or url style values)
  • value TEXT UNIQUE
  • added_at TEXT

history

Stores monitored URL history.

Columns:

  • id INTEGER PK
  • url TEXT
  • host TEXT
  • timestamp TEXT
  • status INTEGER
  • content_type TEXT
  • blocked BOOLEAN default 0
  • block_reason TEXT

phishtank_urls

Stores phishing feed data (incremental model).

Columns:

  • id INTEGER PK
  • url TEXT UNIQUE
  • domain TEXT
  • first_seen TEXT
  • last_updated TEXT
  • active BOOLEAN default 1
  • source TEXT default unknown

Indexes:

  • idx_phishtank_domain
  • idx_phishtank_active

Migration Behavior

Current migration path includes adding missing source column to existing phishtank_urls tables.

Data Lifecycle

  • Alert rows can be marked as seen.
  • History can be auto-cleared by retention settings.
  • Phishing URLs are refreshed incrementally; stale entries can be marked inactive.