production_schema.sql

-- Production schema snapshot for PostgreSQL (generated from migrations-pg).

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  created_at INTEGER DEFAULT EXTRACT(EPOCH FROM NOW())::INTEGER,
  phone TEXT,
  phone_verified INTEGER DEFAULT 0,
  admin INTEGER DEFAULT 0,
  oauth_provider TEXT,
  oauth_sub TEXT,
  email TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username_lower
  ON users (LOWER(username));

CREATE INDEX IF NOT EXISTS idx_users_username_trgm
  ON users USING GIN (username gin_trgm_ops);

CREATE UNIQUE INDEX IF NOT EXISTS idx_users_oauth
  ON users(oauth_provider, oauth_sub)
  WHERE oauth_provider IS NOT NULL;

CREATE TABLE IF NOT EXISTS user_last_seen (
  user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  last_seen INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_user_last_seen_last_seen
  ON user_last_seen(last_seen DESC, user_id);

CREATE TABLE IF NOT EXISTS friendships (
  user_id INTEGER NOT NULL,
  friend_id INTEGER NOT NULL,
  nickname TEXT,
  created_at INTEGER DEFAULT EXTRACT(EPOCH FROM NOW())::INTEGER,
  PRIMARY KEY (user_id, friend_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (friend_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_friendships_user
  ON friendships(user_id);

CREATE INDEX IF NOT EXISTS idx_friendships_friend_id
  ON friendships(friend_id);

CREATE TABLE IF NOT EXISTS last_oy_info (
  user_id INTEGER NOT NULL,
  friend_id INTEGER NOT NULL,
  last_oy_id INTEGER,
  last_oy_type TEXT,
  last_oy_created_at INTEGER,
  last_oy_from_user_id INTEGER,
  streak_start_date INTEGER,
  PRIMARY KEY (user_id, friend_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (friend_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_last_oy_info_user
  ON last_oy_info(user_id);

CREATE TABLE IF NOT EXISTS oys (
  id SERIAL PRIMARY KEY,
  from_user_id INTEGER NOT NULL,
  to_user_id INTEGER NOT NULL,
  created_at INTEGER DEFAULT EXTRACT(EPOCH FROM NOW())::INTEGER,
  type TEXT DEFAULT 'oy',
  payload TEXT,
  FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (to_user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_oys_to_user
  ON oys(to_user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_oys_to_user_created_at_id
  ON oys(to_user_id, created_at DESC, id DESC);

CREATE INDEX IF NOT EXISTS idx_oys_from_user_created_at_id
  ON oys(from_user_id, created_at DESC, id DESC);

CREATE TABLE IF NOT EXISTS push_subscriptions (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  platform TEXT NOT NULL DEFAULT 'web' CHECK (platform IN ('web', 'ios', 'android')),
  endpoint TEXT,
  keys_p256dh TEXT,
  keys_auth TEXT,
  native_token TEXT,
  apns_environment TEXT CHECK (apns_environment IN ('sandbox', 'production')),
  created_at INTEGER DEFAULT EXTRACT(EPOCH FROM NOW())::INTEGER,
  CHECK (
    (
      platform = 'web' AND
      endpoint IS NOT NULL AND
      keys_p256dh IS NOT NULL AND
      keys_auth IS NOT NULL AND
      native_token IS NULL AND
      apns_environment IS NULL
    ) OR (
      platform = 'android' AND
      native_token IS NOT NULL AND
      endpoint IS NULL AND
      keys_p256dh IS NULL AND
      keys_auth IS NULL AND
      apns_environment IS NULL
    ) OR (
      platform = 'ios' AND
      native_token IS NOT NULL AND
      endpoint IS NULL AND
      keys_p256dh IS NULL AND
      keys_auth IS NULL
    )
  ),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_push_subscriptions_endpoint
  ON push_subscriptions(endpoint)
  WHERE endpoint IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_push_subscriptions_native_token
  ON push_subscriptions(native_token)
  WHERE native_token IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_push_subscriptions_user
  ON push_subscriptions(user_id);

CREATE TABLE IF NOT EXISTS notifications (
  id SERIAL PRIMARY KEY,
  to_user_id INTEGER NOT NULL,
  from_user_id INTEGER NOT NULL,
  type TEXT NOT NULL,
  payload TEXT NOT NULL,
  created_at INTEGER DEFAULT EXTRACT(EPOCH FROM NOW())::INTEGER,
  FOREIGN KEY (to_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_notifications_to_user
  ON notifications(to_user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_notifications_created_at
  ON notifications(created_at DESC);

CREATE TABLE IF NOT EXISTS notification_deliveries (
  id SERIAL PRIMARY KEY,
  notification_id INTEGER NOT NULL,
  endpoint TEXT NOT NULL,
  attempt INTEGER NOT NULL,
  success INTEGER NOT NULL,
  status_code INTEGER,
  error_message TEXT,
  created_at INTEGER DEFAULT EXTRACT(EPOCH FROM NOW())::INTEGER,
  FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_notification_deliveries_notification
  ON notification_deliveries(notification_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_notification_deliveries_created_at
  ON notification_deliveries(created_at DESC);

CREATE TABLE IF NOT EXISTS sessions (
  token TEXT PRIMARY KEY,
  user_id INTEGER NOT NULL,
  created_at INTEGER DEFAULT EXTRACT(EPOCH FROM NOW())::INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_sessions_user
  ON sessions(user_id);

CREATE TABLE IF NOT EXISTS passkeys (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  credential_id BYTEA NOT NULL UNIQUE,
  public_key BYTEA NOT NULL,
  counter INTEGER NOT NULL DEFAULT 0,
  transports TEXT[],
  created_at INTEGER DEFAULT EXTRACT(EPOCH FROM NOW())::INTEGER,
  last_used_at INTEGER,
  device_name TEXT
);

CREATE INDEX IF NOT EXISTS idx_passkeys_user
  ON passkeys(user_id);

CREATE INDEX IF NOT EXISTS idx_passkeys_credential
  ON passkeys(credential_id);

CREATE TABLE IF NOT EXISTS user_blocks (
  blocker_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  blocked_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at INTEGER DEFAULT EXTRACT(EPOCH FROM NOW())::INTEGER,
  PRIMARY KEY (blocker_user_id, blocked_user_id),
  CHECK (blocker_user_id <> blocked_user_id)
);

CREATE INDEX IF NOT EXISTS idx_user_blocks_blocked_user
  ON user_blocks(blocked_user_id);

CREATE TABLE IF NOT EXISTS user_reports (
  id SERIAL PRIMARY KEY,
  reporter_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  target_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  reason TEXT NOT NULL,
  details TEXT,
  created_at INTEGER DEFAULT EXTRACT(EPOCH FROM NOW())::INTEGER,
  status TEXT NOT NULL DEFAULT 'open',
  reviewed_by_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
  reviewed_at INTEGER,
  resolution_note TEXT,
  CHECK (reporter_user_id <> target_user_id)
);

CREATE INDEX IF NOT EXISTS idx_user_reports_target_created
  ON user_reports(target_user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_user_reports_reporter_created
  ON user_reports(reporter_user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_user_reports_status_created
  ON user_reports(status, created_at DESC);