# AI Explorer Supabase Content Connector

The Supabase counterpart of the WordPress plugin (`wordpress-plugin/ai-explorer-media-connector`).
A Supabase-backed website deploys this Edge Function into **its own** project; AI Explorer Media
then talks to it exactly the way it talks to WordPress — same signed envelope, same lifecycle,
same error codes, same read-back verification.

## Why an Edge Function and not direct Postgres

AI Explorer Media never connects to a foreign Postgres and never stores a foreign service-role key.
The site keeps its own credentials; the only thing shared is an HMAC secret used to sign requests.

## Deploy

```bash
supabase functions deploy aemc-content --no-verify-jwt
supabase secrets set \
  AEMC_CONNECTION_ID="conn_$(openssl rand -hex 8)" \
  AEMC_SHARED_SECRET="$(openssl rand -hex 32)" \
  AEMC_CONTENT_TABLE=content \
  AEMC_SITE_NAME="My site" \
  AEMC_SITE_URL="https://example.com" \
  AEMC_FUNCTION_BASE="/aemc-content"
```

Then pair it in AI Explorer Media → Website → Connections → Add connection → Supabase, using:

- **Base URL** — `https://<project-ref>.functions.supabase.co`
- **Function path** — `/aemc-content`
- **Connection ID** — the `AEMC_CONNECTION_ID` value
- **Shared secret** — the `AEMC_SHARED_SECRET` value (stored in Vault, never in a table)

## Contract

| Route | Method | Purpose |
| --- | --- | --- |
| `/health` | GET | Unsigned liveness probe (no site data) |
| `/discover` | GET | Site name, content types, granted capabilities |
| `/posts` | GET / POST | List (Trash excluded by default) / create as DRAFT |
| `/posts/{id}` | GET / PATCH / DELETE | Read (`?include_trashed=1`) / update / trash (`?force=true` purges) |
| `/posts/{id}/publish` | POST | Draft → Published |
| `/posts/{id}/unpublish` | POST | Published → Draft |
| `/posts/{id}/archive` | POST | Active → Archived (recoverable, not Trash) |
| `/posts/{id}/restore` | POST | Trash → actual restored lifecycle |

Canonical lifecycle: `DRAFT`, `PUBLISHED`, `ARCHIVED`, `TRASHED` (plus `SCHEDULED`).
Whatever vocabulary the site's own table uses is reported in `remote_status` and mapped, never assumed.

## Security

- Every non-health request is HMAC-SHA256 signed: `METHOD\nPATH[?sorted-query]\nTIMESTAMP\nNONCE\nsha256(body)`.
- 300-second clock window, single-use nonces (replay rejected), constant-time signature compare.
- Permanent delete is refused unless the row is already `TRASHED`.
- Updates require `expected_updated_at`; a mismatch is `AEMC_CONFLICT`, never a blind overwrite.
- Errors are closed codes (`AEMC_*`); raw Postgres text is never returned.

## Unsupported capabilities

Anything the connector does not grant in `/discover` is surfaced by AI Explorer
Media as UNSUPPORTED rather than emulated locally. As of connector 1.4.0,
media, taxonomies, revisions, SEO and GEO are all supported.

## Revisions (STEP 07.L.4)

History is stored in the website's own project, exactly like WordPress keeps
`wp_revisions`. Create the table once; the connector snapshots the current
document before every write, so a restore is additive (v1..v3 survive and the
restore becomes v4).

```sql
create table public.content_revisions (
  id uuid primary key default gen_random_uuid(),
  content_id uuid not null references public.content(id) on delete cascade,
  version int not null,
  title text,
  body text,
  excerpt text,
  status text,
  author_name text,
  changed_fields text[] not null default '{}',
  created_at timestamptz not null default now(),
  unique (content_id, version)
);
```

Override the table name with `AEMC_REVISIONS_TABLE`. If the table is absent the
site still saves normally — it simply has no history.

Routes: `GET /posts/{id}/revisions`, `GET /posts/{id}/revisions/{revisionId}`,
`POST /posts/{id}/revisions/{revisionId}/restore`.

## SEO + GEO (STEP 07.L.5)

SEO and GEO are FIELDS of the document, stored in two JSONB columns. They are
written on their own: the body, title, slug, status and taxonomy are never part
of an SEO or GEO request, so a metadata save cannot rewrite content.

```sql
alter table public.content
  add column if not exists seo jsonb not null default '{}'::jsonb,
  add column if not exists geo jsonb not null default '{}'::jsonb;
```

Override the column names with `AEMC_SEO_COLUMN` / `AEMC_GEO_COLUMN`.

Routes: `GET|POST /posts/{id}/seo`, `GET|POST /posts/{id}/geo`. A POST merges
only the keys it received and answers with the stored state, so the app can
verify REQUESTED == REMOTE before reporting SYNCED.

## Real-time change events (v1.5.0)

The connector pushes a **thin, signed** envelope whenever content changes, so
AI Explorer Media learns about remote edits within seconds instead of waiting
for the next reconciliation pass:

```bash
supabase secrets set \
  AEMC_EVENTS_ENABLED=true \
  AEMC_EVENT_ENDPOINT="https://<your-app>/api/public/hooks/website-sync-event"
```

- The envelope carries identity only — event type, object id, remote
  `updated_at`, changed fields. Never the body, never credentials.
- It is signed with the same HMAC-SHA256 canonical string as every other
  request, using `AEMC_SHARED_SECRET`. HTTPS endpoints only.
- The request id of the write that caused the change travels back as
  `correlation_id`. That is how AI Explorer Media recognises its **own** push
  and refuses to re-apply it (no echo loops).
- Delivery is best effort. A dropped event is not data loss: scheduled
  reconciliation still converges the mirror.
- Writes made directly against Postgres (SQL editor, another app) emit no
  event by design — reconciliation picks those up.

## v1.6.0 — STEP 07.L.22 schema audit (read-only)

The connector adapts to the host project's existing schema through environment
variables; it never requires new tables.

| Variable | Default | Purpose |
| --- | --- | --- |
| `AEMC_CONTENT_TABLE` | `content` | posts/documents table |
| `AEMC_MEDIA_TABLE` | `media` | media library table |
| `AEMC_TERMS_TABLE` | `terms` | categories + tags (`taxonomy` column) |
| `AEMC_CONTENT_TERMS_TABLE` | `content_terms` | post↔term join |
| `AEMC_REVISIONS_TABLE` | `content_revisions` | revision history |
| `AEMC_SEO_COLUMN` / `AEMC_GEO_COLUMN` | `seo` / `geo` | jsonb columns on the content table |
| `AEMC_MEDIA_BUCKET` | `content-media` | storage bucket |

### Column and status mapping (v1.7.0)

Hosts differ in more than table names. These map the fields that vary most;
each accepts a comma-separated candidate list, tried in order, and resolves to
`null` when no candidate exists on the row — never an invented value.

| Variable | Default | Purpose |
| --- | --- | --- |
| `AEMC_BODY_COLUMN` | `body,content,content_html,html` | authoritative article body |
| `AEMC_AUTHOR_COLUMN` | `author_name,author,author_id` | string, uuid, or object |
| `AEMC_FEATURED_MEDIA_COLUMN` | `featured_media_id,featured_image_id,media_id,featured_image` | media id or plain URL |
| `AEMC_STATUS_DRAFT` / `_PUBLISHED` / `_ARCHIVED` / `_TRASHED` / `_SCHEDULED` | the canonical name | host literal for each state |

The status vocabulary is host-defined. When the host enum has no trash label,
the connector detects that once (Postgres `22P02`) and stops emitting the
trash filter instead of failing the whole list route.

Tables the host does not have return `200` with an empty `rows` array and an
explicit `unavailable` block (`stage`, `table`, `pg_code`, `message`) rather
than a `500`.

### `GET /diagnostics/columns` (signed, read-only)

Column NAMES and value KINDS (`string`, `uuid`, `timestamp`, `number`,
`object`, `array`, `null`) from one sample row per table — never column
values, article bodies, or secrets. Also returns the distinct `status`
literals in use, the configured status map, and whether a trash status
exists. This is the evidence used to configure the mapping variables above.

### `GET /diagnostics/schema` (signed, read-only)

Single-row read of every configured table — a zero-row `head` probe can report
a non-existent relation as readable, which is exactly how `media` and `terms`
appeared healthy while every data route returned `42P01`. Returns, per table:
`readable`, and on failure the real Postgres `pg_code`, `message`, `details`,
`hint`. Also reports SET/MISSING for `SUPABASE_URL`,
`SUPABASE_SERVICE_ROLE_KEY`, `AEMC_CONNECTION_ID`, `AEMC_SHARED_SECRET`
(values are never returned). Data-route failures now carry the same safe
detail block instead of a bare `AEMC_PROVIDER_ERROR`.

Typical result on a mismatched host schema: `pg_code: 42P01` (table missing —
point the `AEMC_*_TABLE` variables at the real tables) or `42703` (column
mismatch — needs a mapping layer, not a new table).