CREATE TABLE home_identity_links (
  issuer TEXT NOT NULL,
  subject TEXT NOT NULL,
  person_id INTEGER NOT NULL,
  PRIMARY KEY (issuer, subject),
  UNIQUE (issuer, person_id)
);
CREATE TABLE home_link_flows (
  id_hash TEXT PRIMARY KEY NOT NULL,
  stage TEXT NOT NULL CHECK (stage IN ('init', 'pending')),
  expires_at INTEGER NOT NULL,
  payload BLOB NOT NULL
);
CREATE INDEX home_link_flows_expiry ON home_link_flows(expires_at);
CREATE TABLE home_link_clock (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  high_water INTEGER NOT NULL
);
INSERT INTO home_link_clock (id, high_water) VALUES (1, 0);