# agentCRM

> A CRM for AI agents. The data plane is a hosted Postgres database, exposed as both raw SQL and a typed REST surface for built-in and custom tables.

The fastest path: `POST https://www.tryagentcrm.com/v1/workspaces` with no auth. You receive a workspace_id and an api_key. Use the api_key as a Bearer token on every `/v1/*` request.

## Quickstart

```bash
# 1. Create an anonymous workspace (no auth needed)
KEY=$(curl -sX POST https://www.tryagentcrm.com/v1/workspaces | jq -r .data.api_key)

# 2. Create a contact via the typed REST endpoint
curl -sX POST https://www.tryagentcrm.com/v1/contacts \
  -H "Authorization: Bearer $KEY" \
  -H "Content-Type: application/json" \
  --data '{"email":"sarah@acme.com","first_name":"Sarah"}'

# 3. Or drop to SQL for joins / aggregates
curl -sX POST https://www.tryagentcrm.com/v1/sql \
  -H "Authorization: Bearer $KEY" \
  -H "Content-Type: application/json" \
  --data @body.json
```

## Endpoints

- `POST /v1/workspaces` — anonymous (dev key + claim_url) or Clerk-authed (live key + ownership).
- `POST /v1/claim/:ws_id` — claim an unclaimed workspace (Clerk session required).
- `POST /v1/keys/rotate?ws=ws_<id>` — mint a new live key, revoke the old (Clerk session, user-scoped).
- `POST /v1/connection?ws=ws_<id>` — fresh direct-Postgres URL (claimed workspaces, Clerk session).
- `GET  /v1/members?ws=ws_<id>` — list workspace members (Clerk session, members only).
- `DELETE /v1/members?ws=ws_<id>&user_id=<id>` — owner removes a member; revokes their live keys.
- `POST /v1/members/invite` — owner invites by email. Body: `{ workspace_id, email, role?: 'member' | 'viewer' }`. Returns a token + accept_url.
- `POST /v1/members/accept/:token` — invitee accepts after signing in; creates membership + live key tied to them.

Data plane:

- `POST /v1/sql` — raw SQL. Body `{ sql, params? }`. Returns `{ data: { columns, rows, rowCount, command }, _meta }`.
- `GET  /v1/{table}` — list rows with filters + keyset pagination.
- `GET  /v1/{table}/:id` — single row; built-ins expand related objects.
- `GET  /v1/{table}/search?q=` — fuzzy search (trgm) for built-ins.
- `POST /v1/{table}` — create. 409 on UNIQUE collision.
- `PATCH /v1/{table}/:id` — partial update. jsonb columns merge.
- `POST /v1/{table}/:id/restore` — undo a soft delete.
- `DELETE /v1/{table}/:id` — soft delete (or `?hard=true`).
- `GET  /v1/tables` — list tables in the workspace.
- `GET  /v1/schema` — full schema graph (tables, columns, FKs, indexes) for composing joins.
- `GET  /v1/activity` — feed of created/updated/deleted/restored/conflict events from triggers.
- `GET  /v1/lists/:id/rows` — resolve a saved list (dynamic or static) with cross-object joins + column projection. List CRUD goes through `/v1/lists` like any other table.
- `POST /v1/lists/:id/members` / `DELETE /v1/lists/:id/members/:member_id` — static-list membership.

## Built-in tables

```sql
companies (id, name, domain, industry, size, custom jsonb,
           created_at, updated_at, deleted_at)

contacts  (id, email citext, first_name, last_name, phone, title,
           company_id REFERENCES companies(id),
           custom jsonb, created_at, updated_at, deleted_at)

deals     (id, name, stage, amount_cents bigint, currency, close_date,
           contact_id REFERENCES contacts(id),
           company_id REFERENCES companies(id),
           custom jsonb, created_at, updated_at, deleted_at)

emails    (id, thread_id, provider, provider_msg_id, direction,
           from_addr citext, to_addrs citext[], cc_addrs citext[],
           subject, snippet, body_text, body_html,
           sent_at, received_at,
           contact_ids text[], company_ids text[], deal_ids text[],
           custom jsonb, created_at, updated_at, deleted_at)

activity  (id, occurred_at, actor, verb, subject_type, subject_id,
           subject_repr, changes jsonb, custom jsonb)

lists     (id, name, description, target_table, kind, filter_predicate,
           filter_params jsonb, config jsonb,
           custom jsonb, created_at, updated_at, deleted_at)
           -- kind in ('dynamic','static')

list_members (id, list_id REFERENCES lists(id) ON DELETE CASCADE,
              member_id, added_at, added_by, custom jsonb)
              -- UNIQUE (list_id, member_id)
```

ID prefixes: `c_` contacts, `co_` companies, `d_` deals, `e_` emails, `a_` activity, `ws_` workspaces. IDs auto-generate; do not supply them on INSERT.

## Filter conventions on `GET /v1/{table}`

| Param shape | SQL |
|---|---|
| `?col=v` | `col = $1` |
| `?col=v1,v2` | `col = ANY($1)` |
| `?col_after=ISO` / `?col_before=ISO` | `col > $1` / `col <= $1` |
| `?col_gte=N` / `?col_lte=N` | numeric / date comparison |
| `?col_contains=substr` / `?col_starts=prefix` | ILIKE patterns |
| `?col_present=true|false` | `IS NOT NULL` / `IS NULL` |
| `?col_has=v` | array containment `col @> ARRAY[v]` |
| `?custom.key=v` | `(custom->>'key') = $1` |
| `?include_deleted=true` | bypass soft-delete filter |
| `?only_deleted=true` | show only soft-deleted rows |
| `?limit=N&cursor=...` | keyset pagination (default 50, max 200) |

## Email auto-attach

`POST /v1/emails` (or any INSERT into the table) triggers an automatic match:

- `contact_ids` ← `SELECT id FROM contacts WHERE email = ANY(from_addr, to_addrs, cc_addrs)` (citext exact, non-deleted).
- `company_ids` ← `SELECT id FROM companies WHERE lower(domain) = ANY(domains from those addresses)` (non-deleted).

Both are populated by a `BEFORE INSERT` trigger inside the tenant schema. To answer "last email from Jim", resolve Jim → contact id → `GET /v1/emails?from_contact=c_xyz&limit=1`. Use `?involves_contact=c_xyz` (via `contact_ids @> ARRAY[c_xyz]`) to capture from/to/cc all together.

## Activity feed

Every write to built-ins emits an activity row. Custom tables can opt in by `CREATE TRIGGER ... EXECUTE FUNCTION record_activity()` on their own table.

```
GET /v1/activity?since=2026-04-28T00:00:00Z
GET /v1/activity?subject_type=contact&subject_id=c_xyz
GET /v1/activity?actor=api_key:ak_...
GET /v1/activity?verb=deleted
```

Verbs: `created`, `updated`, `deleted`, `restored`, `conflict`. The `changes` column on `updated` rows is a JSON diff (`{column: {from, to}}`).

## Preinstalled Postgres extensions

- `citext` — case-insensitive text (used by `contacts.email`, `emails.from_addr`).
- `pg_trgm` — fuzzy text search (`%` operator, `similarity()`).
- `vector` — pgvector for embeddings (`vector(1536)`).
- `pgcrypto` — `gen_random_bytes`, `gen_random_uuid`.

## Conventions

- All requests/responses are JSON.
- Bearer auth on every `/v1/*` request except `/v1/workspaces` (anon allowed).
- Successful responses include `_meta`. Unclaimed workspaces have `claim_url` and `ttl_expires_at`. Read it.
- SQL errors return `{ error: "sql_error", message, code, hint, position, docs_url }` — read the SQLSTATE and self-correct.

## Workspace lifecycle

- Anonymous: 7-day TTL, `acrm_dev_*` key, claim_url in `_meta`. After claim it transitions to read-only for 7 days, then revoked at 14.
- Claimed: permanent. `acrm_live_*` key tied to a Clerk-authenticated user.

## Discovery
- OpenAPI: https://www.tryagentcrm.com/openapi.json
- This file: https://www.tryagentcrm.com/llms.txt