gitfor/platform

-- notify: squashed from 1700_notify.sql.

CREATE TABLE notification_category_prefs (
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  category TEXT NOT NULL,
  web INTEGER NOT NULL,
  email INTEGER NOT NULL,
  digest TEXT NOT NULL DEFAULT 'off',
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, category)
);

CREATE TABLE notification_digest_items (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  repo_id TEXT REFERENCES repos(id) ON DELETE CASCADE,
  category TEXT NOT NULL,
  title TEXT NOT NULL,
  url TEXT NOT NULL,
  account_id TEXT,
  subject TEXT,
  access TEXT NOT NULL,
  flush_after INTEGER NOT NULL,
  created_at INTEGER NOT NULL
);

CREATE INDEX notification_digest_items_user ON notification_digest_items(user_id, category, created_at);

CREATE INDEX notification_digest_items_flush ON notification_digest_items(flush_after);

CREATE TABLE notification_email_counters (
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  window_start INTEGER NOT NULL,
  sent INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, window_start)
);

CREATE TABLE notification_unsubscribe_tokens (
  user_id TEXT PRIMARY KEY REFERENCES accounts(id) ON DELETE CASCADE,
  token TEXT NOT NULL UNIQUE,