Skip to content

feat(agent): db schema + plan flag for the in-product AI agent#120

Merged
gkhngyk merged 1 commit into
mainfrom
feat/agent-schema-and-plan
May 29, 2026
Merged

feat(agent): db schema + plan flag for the in-product AI agent#120
gkhngyk merged 1 commit into
mainfrom
feat/agent-schema-and-plan

Conversation

@gkhngyk

@gkhngyk gkhngyk commented May 28, 2026

Copy link
Copy Markdown
Contributor

Phase 2 PR A.1 of #94 — pure data layer. No application code yet; PR A.2 wires the chat API + tool loop, and PR B adds the `/dashboard/agent` route and sidebar entry.

What this PR is

File Change
`supabase/migrations/00009_agent_chat_schema.sql` New tables: `agent_conversations`, `agent_messages`, `agent_token_usage` + indexes, RLS, updated_at triggers
`web/src/config/plans.ts` New `'ai_agent'` feature + optional `aiAgentTokenQuota` numeric on `PlanLimits`
`web/src/types/supabase.ts` Hand-authored `Row`/`Insert`/`Update` entries for the three new tables
`web/src/lib/guards/plan-guard.ts`, `web/src/hooks/use-feature-gate.ts` Exclude `aiAgentTokenQuota` from the count-style `keyof Omit` so existing limit helpers keep working

Schema shape

Three tables, all scoped to a user inside an organization:

  • `agent_conversations` — one chat thread; "+ new chat" creates a row. Optional `brand_id` pins a thread to a brand so subsequent tool calls have an implicit scope.
  • `agent_messages` — ordered messages within a thread. Role + `tool_calls` / `tool_call_id` / `tool_name` / `tool_result` columns match the Vercel AI SDK message shape so we can hydrate the panel and feed the tool-calling loop without re-mapping. `prompt_tokens` / `completion_tokens` stored per assistant row for analytics.
  • `agent_token_usage` — monthly bucket per `(user, organization, year_month)` with a `UNIQUE` constraint for upsert. Quota check is one indexed `SELECT` instead of an aggregate scan over `agent_messages`.

Plan gating

`ai_agent` is the feature flag; `aiAgentTokenQuota` is the cost guard.

Plan `ai_agent` `aiAgentTokenQuota`
self_hosted none (operator's own keys / infra)
starter
growth 50,000 tokens / user / month (~65 typical turns at Claude Sonnet 4.6 averages including tool I/O)
enterprise none (governed by contract)

The chat API in PR A.2 reads `aiAgentTokenQuota` directly. The existing `isWithinLimit` / `enforceLimit` / `useFeatureGate.withinLimit` helpers handle count-style limits like `maxBrands` — quota is a different shape (cumulative usage in a window) and gets its own check.

Safety

  • RLS scopes everything to `auth.uid()` — users can only see their own conversations / messages / usage
  • Chat API will run server-side via `supabaseAdmin` so it can write tool results that the user didn't author (service_role bypasses RLS)
  • Triggers maintain `updated_at` on conversations + token_usage
  • Migration is purely additive: new tables only, no changes to existing schema or data
  • Self-host: same migration file ships in the next release; runs on `supabase db push` like the other 00009 migrations have

Verified

  • ✅ Migration applied to the cloud project; `information_schema.tables` shows all three tables present
  • ✅ `yarn typecheck` + `yarn format:check` clean
  • ✅ Existing limit / feature-gate helpers still compile after the `keyof Omit` updates

Out of scope (PR A.2 next)

  • `@ai-sdk/anthropic` + `ai` packages on the web side
  • Tool definitions wrapping the MCP read tools (`list_brands`, `get_visibility_summary`, `get_visibility_trend`, `get_competitor_comparison`, `list_citations`, `list_topics`, `list_prompts`, `list_content_opportunities`)
  • `/api/agent/chat` streaming endpoint
  • Conversation CRUD endpoints
  • Token usage upsert + quota enforcement

Out of scope (PR B after that)

Phase 2 PR A.1 of #94 — pure data layer. No application code yet; the
next PR wires the chat API + tool loop, and PR B adds the
/dashboard/agent route and sidebar entry.

Migration 00009 creates three tables, all user+org scoped with RLS:

  agent_conversations   one chat thread; "+ new chat" creates a row.
                        Optional brand_id pins a thread to a brand so
                        tool calls have an implicit scope.
  agent_messages        ordered messages within a thread. Role +
                        tool_calls / tool_call_id / tool_name /
                        tool_result columns match the Vercel AI SDK
                        message shape so we can hydrate the panel and
                        feed the tool-calling loop without re-mapping.
                        prompt_tokens / completion_tokens stored per
                        assistant row for analytics.
  agent_token_usage     monthly bucket per (user, org, year_month).
                        Quota check is one indexed SELECT instead of
                        an aggregate scan over agent_messages.

RLS scopes everything to auth.uid(); the chat API runs server-side via
supabaseAdmin so it can write tool results that the user didn't author.
Triggers maintain updated_at on conversations + token_usage.

config/plans.ts: new 'ai_agent' feature in FEATURES, new optional
aiAgentTokenQuota numeric on PlanLimits. self_hosted + enterprise get
the feature with no quota; growth gets the feature with a 50k token /
user / month cap (~65 typical turns at Claude Sonnet 4.6 averages);
starter stays off.

The existing isWithinLimit / enforceLimit / useFeatureGate.withinLimit
type-guards exclude aiAgentTokenQuota from their keyof Omit so they
keep accepting only count-style numeric limits. The agent's chat API
will read aiAgentTokenQuota directly in the next PR.

types/supabase.ts: hand-authored Row/Insert/Update entries for the
three new tables so the API code in PR A.2 has typed access.

Migration applied to the cloud project; schema verified.

Refs #94.
@gkhngyk gkhngyk merged commit f3cb097 into main May 29, 2026
4 checks passed
gkhngyk added a commit that referenced this pull request May 30, 2026
* feat(agent): end-to-end in-product AI agent chat

Phase 2 of #94. Sidebar entry under Brands, /dashboard/agent route,
streaming chat backed by Anthropic Claude Sonnet 4.6, conversation
+ message persistence, monthly token quota per the plan flag landed
in PR #120.

Backend:
- web/src/lib/agent/model.ts — direct @ai-sdk/anthropic provider.
  Issue body cross-cutting requirement says "self-hosted instances
  use their own keys (BYO)"; Vercel AI Gateway would lock self-host
  out, so we mirror the existing server/src/lib/ai-provider.js
  pattern.
- web/src/lib/agent/system-prompt.ts — analyst persona distilled
  from skills/ansvisor-aeo-coach/SKILL.md (never invent numbers,
  always call a tool first, lead with the headline, end with one
  actionable next step).
- web/src/lib/agent/tools.ts — Vercel AI SDK tool defs wrapping the
  MCP data layer fns directly (list_brands, get_visibility_summary,
  get_visibility_trend, get_competitor_comparison, list_citations,
  list_topics, list_prompts, list_content_opportunities). Same
  scoping the MCP server uses; no data path duplication.
- web/src/lib/agent/auth.ts — resolves the dashboard session into
  the McpAuthContext shape so the tools can be reused as-is.
- web/src/lib/agent/token-quota.ts — monthly bucket lookup + upsert.
  Self-host / enterprise get unlimited via aiAgentTokenQuota
  undefined.

API routes:
- POST /api/agent/chat — streaming. Verifies session, conversation
  ownership, plan flag, quota; then streamText with the tool surface
  and toUIMessageStreamResponse({ originalMessages, onFinish }).
  onFinish persists the full UIMessage[] (replace-in-place per
  turn, parts kept as jsonb in tool_calls for replay), auto-titles
  the conversation from the first user message, and bumps the
  updated_at trigger so the sidebar re-sorts.
- GET /api/agent/conversations — sidebar list.
- POST /api/agent/conversations — "+ new chat".
- GET/DELETE /api/agent/conversations/[id] — load thread, drop
  thread.

UI:
- web/src/config/dashboard.ts — "Agent" sidebar entry under
  Brands, requiredFeature 'ai_agent', Sparkles icon.
- web/src/components/layout/sidebar.tsx, web/messages/en.json —
  nav.agent translation.
- /dashboard/agent — single client component with conversation
  sidebar + chat area. useChat from @ai-sdk/react with
  DefaultChatTransport pinned to /api/agent/chat. Plan-gated render
  shows an upgrade card when the user's plan doesn't include
  ai_agent. Messages render text parts inline and tool calls as a
  compact pill so the user sees which tool ran.

Refs #94.

* fix(agent): convert conversation row from button to role=button div

HTML doesn't allow <button> nested inside <button>, which Next.js
surfaces as a hydration error. The sidebar's conversation row was a
real <button> (so the entire row was clickable) and the trash icon
was a nested <button> for delete — that's the disallowed pattern.

Swap the outer <button> for a <div role="button" tabIndex={0}> with
the same onClick plus a keyboard handler for Enter / Space so screen
readers + keyboard users keep the same affordance. Inner trash stays a
real <button>.

* fix(agent): pass conversation id synchronously to sendMessage

POST /api/agent/chat was returning 400 "conversationId required" on
the first message of a brand-new chat. Root cause: setActiveId is an
async React state update, so when sendMessage runs immediately after
in the same tick, prepareSendMessagesRequest reads the still-null
state from its closure and posts conversationId: null.

Two-part fix so neither path can drift again:

- activeIdRef mirrors activeId; useEffect keeps it in sync, and
  prepareSendMessagesRequest reads from the ref instead of the
  stale state closure. Covers the case where a user clicks an
  existing conversation in the sidebar and immediately types.
- onSubmit captures the resolved id into a local, updates the ref
  synchronously when it just created a new conversation, and
  passes the id via sendMessage's body option as well. Belt-and-
  suspenders: even if the ref hadn't been touched yet, the body
  argument arrives in prepareSendMessagesRequest's body param and
  the spread order lets it take priority.

* fix(agent): set stopWhen on streamText so the tool loop continues

streamText is single-step by default in v6 — the model fires a tool
call and ends the turn before ever seeing the result. The chat in the
test session showed exactly this: "Hangi marka için bakmamı
istersiniz?" + tool-list_brands part, then nothing. The user had to
manually re-ask before Claude (now seeing the prior tool result in
the second turn's context) answered with the brand list.

stepCountIs(20) re-runs the generation after each tool call so the
agent interprets the result and either answers or calls another tool.
20 matches the ToolLoopAgent default — plenty of headroom for the
typical 'list_brands → get_visibility_summary → answer' chain
without burning tokens forever on a pathological loop.

* fix(agent): render assistant messages through the existing Markdown component

The agent answers in markdown — lists, **bold**, headings, links —
but the chat bubble was rendering text parts as plain <p> with
whitespace-pre-wrap, so the user saw the raw markup characters in
the response. Reuse @/components/ui/markdown (the same component the
insights detail page uses for AI responses) for assistant text. User
messages stay plain — they're typed input, not authored markdown,
and the chat bubble contrast reads cleaner without prose styling.

Tool-call pills are unchanged.

* fix(agent): fill viewport height so sidebar + composer reach the bottom

Desktop has no top bar in the dashboard layout (the mobile nav is md:hidden),
so subtracting 3.5rem from 100vh left a ~56px gap below both the conversation
list and the message composer — looking like a phantom padding-bottom. On
mobile the same calc was 8px short (mobile nav is h-16 = 4rem, not 3.5rem).

Switch to h-[calc(100vh-4rem)] md:h-screen so the agent shell matches the
parent main element's border box on both breakpoints. -m-6 already cancels
the parent's p-6, so border-box height = main's border-box height and the
scroll content height = main's content area (no overflow on either axis).

* feat(agent): expandable tool call disclosure with input + output

The previous chip showed the raw AI SDK state string ('output-available')
next to the tool name — internal jargon that doesn't help a user understand
what the agent actually did. It also wasted the only interactive surface we
had on a part that carries the most debug-worthy payload of the turn.

The new ToolCallDisclosure renders the chip as a button: spinner while the
call is in-flight, wrench + chevron once it has a result. Clicking expands
an inline console-style panel with the input args and output result as
pretty-printed JSON, plus an error block if the call failed. Same pattern
Claude / ChatGPT / Cursor ship for tool runs, and it works for every tool
we already register without per-tool formatting code.

* feat(agent): bring-your-own-key on cloud, drop platform-side token quota

Cloud rollout for the in-product agent had two open problems: every turn
billed Ansvisor's Anthropic account, and the feature was gated to Growth+
to keep that bill bounded. Both go away with BYOK.

What's new
- Settings → Agent: org admins paste their own Anthropic key. Stored
  encrypted at rest (AES-256-GCM) in organizations; the master key lives
  in the new ANSVISOR_ENCRYPTION_KEY env, never touches Postgres. UI
  shows configured/last4/set-at metadata, never the plaintext.
- /api/settings/anthropic-key (GET / PUT / DELETE): GET surfaces only the
  display metadata (any member can read), PUT and DELETE are admin-only.
  Soft format validation on save; first chat turn is the rigorous check.
- /api/agent/chat + /api/agent/conversations now gate on 'is a key
  configured?' instead of token quota. Missing key returns 403 with
  code 'missing_anthropic_key' so the UI can route to Settings.
- web/src/lib/agent/key.ts resolves the key per request: cloud → org
  (decrypt envelope), self-host → ANTHROPIC_API_KEY env. Self-host gets
  the feature unconditionally; cloud gates on BYOK.
- model.ts is now a buildAgentModel(key) factory so streamText can be
  driven with the caller's key per turn.
- Agent page renders a KeyMissingState (CTA → Settings → Agent) when
  cloud and no key set, instead of letting the user submit and 403.

Plans
- ai_agent feature added to Starter; no longer a Growth+ gated feature
  on cloud. The real gate is BYOK.
- aiAgentTokenQuota removed entirely from PlanLimits — no Growth quota,
  no Omit gymnastics in isWithinLimit / useFeatureGate.withinLimit /
  plan-guard.enforceLimit. agent_token_usage table stays for analytics
  (recordAgentTokenUsage still writes on each turn).

Encryption
- AES-256-GCM, 12-byte IV per encryption, GCM auth tag attached. Stored
  as a JSON envelope { v, iv, tag, ct } so we can version-bump the
  scheme later. Master key validated at load — refuses to write unless
  ANSVISOR_ENCRYPTION_KEY decodes to exactly 32 bytes.

Migration
- 00010_org_anthropic_keys.sql adds anthropic_api_key_encrypted,
  anthropic_api_key_last4, anthropic_api_key_set_at,
  anthropic_api_key_set_by to organizations. set_by FK to profiles is
  ON DELETE SET NULL so dropping a profile preserves the audit trail.

Deploy notes
- Generate ANSVISOR_ENCRYPTION_KEY: 'openssl rand -base64 32'. Add it
  to web/.env locally and to Vercel env (production + preview +
  development) before the cloud rollout. Losing or rotating it makes
  saved keys unrecoverable — users will see the empty state and need
  to re-paste, no data loss otherwise.
gkhngyk added a commit that referenced this pull request May 31, 2026
Bundles the in-product Agent epic (#120, #121), the BYOK rollout on
cloud, the MCP toolkit expansion (#109, #110, #116, #117, #118), the
insights performance fix (#114), and the invite-accept flow rebuild
(#127, #129, #130) into a tagged release.

See CHANGELOG.md for the full notes.
gkhngyk added a commit that referenced this pull request May 31, 2026
Bundles the in-product Agent epic (#120, #121), the BYOK rollout on
cloud, the MCP toolkit expansion (#109, #110, #116, #117, #118), the
insights performance fix (#114), and the invite-accept flow rebuild
(#127, #129, #130) into a tagged release.

See CHANGELOG.md for the full notes.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant