CREATE TABLE inviter_invitee(
  id TEXT PRIMARY KEY NOT NULL DEFAULT (lower(hex(randomblob(16)))),
  ccn_pubkey TEXT NOT NULL,
  inviter_pubkey TEXT NOT NULL,
  invitee_pubkey TEXT NOT NULL,
  invite_hash TEXT NOT NULL,
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  FOREIGN KEY (ccn_pubkey) REFERENCES ccns(pubkey) ON DELETE CASCADE
);

CREATE INDEX idx_inviter_invitee_ccn_pubkey ON inviter_invitee(ccn_pubkey);
CREATE INDEX idx_inviter_invitee_inviter_pubkey ON inviter_invitee(inviter_pubkey);
CREATE INDEX idx_inviter_invitee_invitee_pubkey ON inviter_invitee(invitee_pubkey);

CREATE TABLE allowed_writes (
  id TEXT PRIMARY KEY NOT NULL DEFAULT (lower(hex(randomblob(16)))),
  ccn_pubkey TEXT NOT NULL,
  pubkey TEXT NOT NULL,
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  FOREIGN KEY (ccn_pubkey) REFERENCES ccns(pubkey) ON DELETE CASCADE
);

CREATE INDEX idx_allowed_writes_ccn_pubkey ON allowed_writes(ccn_pubkey);
CREATE INDEX idx_allowed_writes_pubkey ON allowed_writes(pubkey);