server/postgresSchema.js

export const APP_SCHEMA_NAME = process.env.POSTGRES_APP_SCHEMA || 'inferenceport_backend';

export const POSTGRES_SCHEMA_SQL = `
CREATE TABLE IF NOT EXISTS app_versions (
  public_url_lookup text PRIMARY KEY,
  updated_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);

CREATE TABLE IF NOT EXISTS guest_state (
  owner_lookup text PRIMARY KEY,
  expires_at timestamptz,
  updated_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);

CREATE TABLE IF NOT EXISTS chat_sessions (
  id text PRIMARY KEY,
  scope_type text NOT NULL,
  owner_lookup text NOT NULL,
  created_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL,
  expires_at timestamptz,
  payload jsonb NOT NULL
);
CREATE INDEX IF NOT EXISTS chat_sessions_owner_idx
  ON chat_sessions (owner_lookup, updated_at DESC);
CREATE INDEX IF NOT EXISTS chat_sessions_expires_idx
  ON chat_sessions (expires_at)
  WHERE expires_at IS NOT NULL;

CREATE TABLE IF NOT EXISTS session_shares (
  id text PRIMARY KEY,
  token_lookup text NOT NULL UNIQUE,
  owner_lookup text NOT NULL,
  created_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);
CREATE INDEX IF NOT EXISTS session_shares_owner_idx
  ON session_shares (owner_lookup, created_at DESC);

CREATE TABLE IF NOT EXISTS deleted_chats (
  id text PRIMARY KEY,
  owner_lookup text NOT NULL,
  purge_at timestamptz,
  deleted_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);
CREATE INDEX IF NOT EXISTS deleted_chats_owner_idx
  ON deleted_chats (owner_lookup, deleted_at DESC);
CREATE INDEX IF NOT EXISTS deleted_chats_purge_idx
  ON deleted_chats (purge_at)
  WHERE purge_at IS NOT NULL;

CREATE TABLE IF NOT EXISTS memories (
  id text PRIMARY KEY,
  owner_lookup text NOT NULL,
  created_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);
CREATE INDEX IF NOT EXISTS memories_owner_idx
  ON memories (owner_lookup, updated_at DESC);

CREATE TABLE IF NOT EXISTS media_entries (
  id text PRIMARY KEY,
  owner_lookup text NOT NULL,
  parent_id text,
  entry_type text NOT NULL,
  updated_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL,
  trashed_at timestamptz,
  purge_at timestamptz,
  expires_at timestamptz,
  size_bytes bigint NOT NULL DEFAULT 0,
  payload jsonb NOT NULL
);
CREATE INDEX IF NOT EXISTS media_entries_owner_idx
  ON media_entries (owner_lookup, updated_at DESC);
CREATE INDEX IF NOT EXISTS media_entries_parent_idx
  ON media_entries (owner_lookup, parent_id);
CREATE INDEX IF NOT EXISTS media_entries_purge_idx
  ON media_entries (purge_at)
  WHERE purge_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS media_entries_expires_idx
  ON media_entries (expires_at)
  WHERE expires_at IS NOT NULL;

CREATE TABLE IF NOT EXISTS media_blobs (
  entry_id text PRIMARY KEY REFERENCES media_entries(id) ON DELETE CASCADE,
  updated_at timestamptz NOT NULL,
  payload bytea NOT NULL
);

CREATE TABLE IF NOT EXISTS system_prompts (
  owner_lookup text PRIMARY KEY,
  updated_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);

CREATE TABLE IF NOT EXISTS feedback_tickets (
  id text PRIMARY KEY,
  status text NOT NULL,
  submitted_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);
CREATE INDEX IF NOT EXISTS feedback_tickets_status_idx
  ON feedback_tickets (status, submitted_at DESC);

CREATE TABLE IF NOT EXISTS guest_request_counters (
  key_lookup text PRIMARY KEY,
  expires_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);
CREATE INDEX IF NOT EXISTS guest_request_counters_expires_idx
  ON guest_request_counters (expires_at);

CREATE TABLE IF NOT EXISTS web_search_usage (
  key_lookup text NOT NULL,
  day_key text NOT NULL,
  updated_at timestamptz NOT NULL,
  payload jsonb NOT NULL,
  PRIMARY KEY (key_lookup, day_key)
);

CREATE TABLE IF NOT EXISTS user_settings (
  owner_lookup text PRIMARY KEY,
  updated_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);

CREATE TABLE IF NOT EXISTS user_profiles (
  owner_lookup text PRIMARY KEY,
  username_lookup text UNIQUE,
  updated_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);

CREATE TABLE IF NOT EXISTS device_sessions (
  token_lookup text PRIMARY KEY,
  user_lookup text NOT NULL,
  active boolean NOT NULL,
  created_at timestamptz NOT NULL,
  last_seen_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);
CREATE INDEX IF NOT EXISTS device_sessions_user_idx
  ON device_sessions (user_lookup, active, last_seen_at DESC);
`;