Data Model & Contracts

-- users: identity + role
CREATE TABLE users (
  telegram_user_id BIGINT PRIMARY KEY,
  username         TEXT,
  first_name       TEXT,
  status           TEXT NOT NULL CHECK (status IN ('pending','active','blocked')) DEFAULT 'pending',
  role             TEXT NOT NULL CHECK (role IN ('admin','user')) DEFAULT 'user',
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  approved_at      TIMESTAMPTZ,
  approved_by      BIGINT REFERENCES users(telegram_user_id)
);
 
-- chat_messages: archived for get_chat_history (Telegram does not expose history)
CREATE TABLE chat_messages (
  id               BIGSERIAL PRIMARY KEY,
  chat_id          BIGINT  NOT NULL,
  telegram_user_id BIGINT  NOT NULL,
  message_id       BIGINT  NOT NULL,
  direction        TEXT    NOT NULL CHECK (direction IN ('inbound','outbound')),
  kind             TEXT    NOT NULL,  -- 'text' | 'photo' | 'document' | 'callback'
  text             TEXT,
  payload          JSONB,             -- raw normalized event
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX chat_messages_user_time_idx ON chat_messages (telegram_user_id, created_at DESC);
 
-- memory_kv: per-user key-value store (Phase 4 v1)
CREATE TABLE memory_kv (
  telegram_user_id BIGINT NOT NULL REFERENCES users(telegram_user_id) ON DELETE CASCADE,
  key              TEXT   NOT NULL,
  value            JSONB  NOT NULL,
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (telegram_user_id, key)
);
 
-- jobs: mirror of Inngest runs (state + result for get_job_status, session for push routing)
CREATE TABLE jobs (
  id                UUID   PRIMARY KEY,
  telegram_user_id  BIGINT NOT NULL REFERENCES users(telegram_user_id),
  type              TEXT   NOT NULL,  -- 'image.generate' etc.
  status            TEXT   NOT NULL CHECK (status IN ('queued','running','succeeded','failed','cancelled')),
  payload           JSONB  NOT NULL,
  result            JSONB,
  error             TEXT,
  inngest_run_id    TEXT,            -- correlates back to the Inngest dashboard
  mcp_session_id    TEXT,            -- which agent session created it (for push routing)
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX jobs_user_status_idx ON jobs (telegram_user_id, status);
 
-- audit_log: every admin action, every blocked-by-policy attempt
CREATE TABLE audit_log (
  id         BIGSERIAL PRIMARY KEY,
  actor_id   BIGINT,
  action     TEXT NOT NULL,    -- 'approve_user' | 'set_role' | 'blocked_request' | ...
  subject_id BIGINT,
  details    JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

send_message
  input  : { chatId: number, text: string,
             options?: { parseMode?: 'Markdown'|'HTML',
                         replyToMessageId?: number,
                         disableNotification?: boolean,
                         replyMarkup?: InlineKeyboardMarkup } }
  output : { messageId: number, chatId: number, sentAt: string }
  errors : INVALID_INPUT, NOT_FOUND (chat), USER_BLOCKED_BOT, RATE_LIMITED

send_image
  input  : { chatId: number, image: ImageSource, caption?: string,
             options?: { parseMode?, replyMarkup?, disableNotification? } }
  output : { messageId, chatId, fileId, sentAt }
  errors : INVALID_INPUT, NOT_FOUND, USER_BLOCKED_BOT, UPSTREAM_FAILURE
  note   : ImageSource is one of { url: string } | { fileId: string }
           | { bytesBase64: string, mimeType: string }. See open question on image source.

send_document
  input  : { chatId, file: FileSource, caption?, options? }
  output : { messageId, chatId, fileId, sentAt }

send_buttons
  input  : { chatId, text, layout: { rows: Button[][] } }
           Button = { label: string, callbackData: string }
                  | { label: string, url: string }
  output : { messageId, chatId }

edit_message
  input  : { chatId, messageId, newText: string,
             options?: { parseMode?, replyMarkup? } }
  output : { messageId, chatId, editedAt: string }

create_button_menu
  input  : { chatId, menuConfig: { title: string, items: Button[] } }
  output : { messageId, chatId }
  note   : convenience wrapper around send_buttons with sensible defaults.

get_user_context
  input  : { userId: number }
  output : { user: { id, username, firstName, status, role,
                     createdAt, approvedAt },
             memory: Record<string, JSON>,
             recentMessages: ChatMessage[] }   // last 20 by default

update_memory
  input  : { userId, key: string, value: JSON }
  output : { key, updatedAt: string }
  note   : upsert. Use store_memory for bulk writes.

store_memory
  input  : { userId, entries: { key: string, value: JSON }[] }
  output : { count: number, updatedAt: string }

get_chat_history
  input  : { userId, limit?: number (default 50, max 500),
             before?: string (ISO timestamp) }
  output : { messages: ChatMessage[] }
  note   : reads from chat_messages table (we archive everything inbound + outbound).

create_job
  input  : { type: 'image.generate' | string,
             payload: JSON,
             userId: number }              // telegram_user_id for concurrency key
  output : { jobId: UUID, status: 'queued', acceptedAt: string }
  note   : emits Inngest event `job.requested`. Returns immediately.

get_job_status
  input  : { jobId: UUID }
  output : { jobId, type, status, result?, error?,
             createdAt, updatedAt }

cancel_job
  input  : { jobId: UUID }
  output : { jobId, status: 'cancelled' }
  note   : best-effort. Sets DB status; the Inngest run finishes its current step
           and stops at the next checkpoint. Only valid while status in
           ('queued','running').

approve_user
  input  : { userId: number }
  output : { userId, status: 'active', approvedAt }
  errors : UNAUTHORIZED (caller not admin), NOT_FOUND

set_role
  input  : { userId, role: 'admin'|'user' }
  output : { userId, role }
  errors : UNAUTHORIZED, NOT_FOUND

method: "notifications/job_status"
params: { jobId, status: 'succeeded'|'failed'|'cancelled',
          result?, error? }

job.requested          // emitted by MCP server when create_job runs
  data: {
    jobId: UUID,
    type: 'image.generate' | string,
    userId: number,           // telegram_user_id, used as concurrency key
    mcpSessionId: string,     // for routing the eventual notification
    payload: object           // type-specific input
  }
 
job.completed          // emitted by the worker function on success
  data: {
    jobId: UUID,
    type: string,
    userId: number,
    result: object
  }
 
job.failed             // emitted by the worker function on terminal failure
  data: {
    jobId: UUID,
    type: string,
    userId: number,
    error: { code: string, message: string }
  }
 
job.cancelled          // Phase 5+ when cancel_job is wired
  data: { jobId, type, userId, reason: string }