The Architecture in One Picture

incoming request
   |
   v
[Middleware]
   |
   |--- public path (login, health, static)? ---> pass through
   |
   |--- source IP in LOCAL_BYPASS list? ---------> pass through
   |
   |--- source IP in blacklist? -----------------> 403 Forbidden
   |
   |--- session cookie present and valid? -------> attach user, pass through
   |
   |--- otherwise --------------------------------> redirect to /login

-- Config: one row, holds the PIN hash and policy
CREATE TABLE security_config (
  id              INTEGER PRIMARY KEY CHECK (id = 1),
  pin_hash        TEXT    NOT NULL,
  pin_enabled     INTEGER NOT NULL DEFAULT 1,
  max_retry       INTEGER NOT NULL DEFAULT 5,
  session_seconds INTEGER NOT NULL DEFAULT 2592000   -- 30 days
);

-- Active sessions
CREATE TABLE security_sessions (
  token       TEXT PRIMARY KEY,
  ip          TEXT    NOT NULL,
  created_at  INTEGER NOT NULL,
  expires_at  INTEGER NOT NULL
);

-- Per-IP retry counter
CREATE TABLE security_attempts (
  ip          TEXT PRIMARY KEY,
  fail_count  INTEGER NOT NULL DEFAULT 0,
  updated_at  INTEGER NOT NULL
);

-- IP blacklist
CREATE TABLE security_blacklist (
  ip          TEXT PRIMARY KEY,
  reason      TEXT,
  blocked_at  INTEGER NOT NULL
);