schema.sql

-- D1 schema for the FlowApp Mini App backend.
-- Create with:  wrangler d1 execute flowapp --local --file=./schema.sql
-- (and again without --local to apply to the remote database)

CREATE TABLE IF NOT EXISTS sessions (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id       TEXT UNIQUE NOT NULL,
  state         TEXT NOT NULL DEFAULT 'disconnected', -- disconnected|awaitingCode|awaitingPassword|connected
  session_str   TEXT,                                        -- gramjs StringSession (authorised)
  account_json  TEXT,                                        -- {id,firstName,lastName,username}
  updated_at    TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS pending_logins (
  user_id         TEXT UNIQUE NOT NULL,
  step            TEXT NOT NULL,                            -- phone|code|password
  phone           TEXT,
  phone_code_hash TEXT,
  session_str     TEXT,                                      -- partial StringSession between handshake steps
  updated_at      TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS events (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id     TEXT NOT NULL,
  type        TEXT NOT NULL,
  data_json   TEXT,
  created_at  TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_events_user ON events(user_id, created_at DESC);