# SEMANTICS.md — CoreDNS (mysql plugin) ↔ PowerDNS API data-shape contract

Ground truth established empirically on 2026-07-10 against the live stack:
MariaDB 12.3.2 (`coredns` db, user `coredns`), CoreDNS with compiled-in `mysql`
plugin serving on `:5053`, Corefile `table_prefix coredns_`,
`zone_update_interval 120s`, plugin `ttl 300`.

## 1. Live schema (source of truth: `SHOW CREATE TABLE coredns_records`)

```sql
CREATE TABLE `coredns_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `zone` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `ttl` int(11) DEFAULT NULL,
  `content` text DEFAULT NULL,
  `record_type` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
```

- `zone`: FQDN **with trailing dot**, lowercase (`example.com.`).
- `name`: **relative to zone, no trailing dot**; empty string `''` at the apex;
  multi-label relative names work (`_p0f.www` served at `_p0f.www.example.com.`
  — verified). A name stored as FQDN is **not** served (verified).
- `content`: JSON object, one row per record value. Type-specific keys (observed
  live + plugin conventions):

  | record_type | content JSON |
  |---|---|
  | TXT | `{"text":"<raw value>"}` |
  | A | `{"ip":"1.2.3.4"}` |
  | AAAA | `{"ip":"::1"}` |
  | CNAME / NS | `{"host":"target.example.com."}` |
  | SOA | `{"ttl":300,"mbox":"hostmaster.example.com.","ns":"ns1.example.com.","refresh":44,"retry":55,"expire":66}` |

- `ttl`: integer seconds, honored as served TTL (verified: row ttl 120 served
  as 120).

## 2. Empirical CoreDNS read semantics (all verified with dig against the live instance)

| Question | Verified answer |
|---|---|
| Multiple TXT values on one name | **Multiple rows**, same `(zone,name,record_type)`, one `{"text":...}` each. Both values served. |
| JSON array inside one row (`{"text":["a","b"]}`) | **Not served** (empty answer). Never write this shape. |
| TXT quoting in DB | Store the **raw, unquoted** token. CoreDNS adds wire quoting: `{"text":"tok"}` → `"tok"` on the wire. Storing `\"tok\"` yields literal quotes inside the value (`"\"tok\""` on the wire) — wrong for ACME. |
| Query case sensitivity | Case-insensitive (`_P0A.EXAMPLE.COM` matched row `_p0a`). Store names lowercase. |
| Write visibility | Record inserts/deletes on an **existing** zone are visible **immediately** (record lookups hit the DB per query). `zone_update_interval` (120s) only gates the cached **zone list**, i.e. only newly-created zones are delayed. No CoreDNS restart, no dnssleep needed for TXT changes on `example.com.`. |

## 3. PowerDNS API semantics (from `dns_pdns.sh` in the `neilpang/acme.sh` image + pdns API docs)

rrset model: `{"name": "<fqdn.>", "type": "TXT", "ttl": N, "changetype": "REPLACE"|"DELETE", "records": [{"content": "\"token\"", "disabled": false}]}`

- `REPLACE` replaces the **entire** record set for `(name, type)`.
- `DELETE` removes the entire set; acme.sh sends it **without `ttl` and without
  `records`** — validation must accept that.
- TXT `content` on the API is **quoted**: `"\"token\""` in the JSON body.
- acme.sh specifics that the responses must satisfy:
  - `_get_root` substring-matches `"name":"<candidate>."` against the
    normalized zone-list JSON → every zone `name` must be FQDN with trailing
    dot.
  - Zone id in URLs arrives **with** trailing dot (`/zones/example.com.`);
    accept it also without a dot and with URL-encoded dots, like PowerDNS.
  - `_list_existingchallenges` regex-scrapes the zone-detail JSON: within each
    rrset object, `"name"` must appear **before** `"records"`, names must be
    FQDN with trailing dot, and TXT contents must be quoted
    (`"content":"\"tok\""`). Use ordered Go structs, never maps.
  - `PUT /zones/{id}/notify` is called after every PATCH and must succeed.

## 4. Mapping table (every handler implements exactly this)

Notation: `zone` = canonical zone FQDN with trailing dot; `rel(name)` =
lowercased rrset name with the zone suffix and trailing dot stripped (apex →
`''`); `unq(c)` = TXT content with one leading and one trailing `"` removed;
`q(t)` = `"` + t + `"`. `{p}` = `TABLE_PREFIX` (default `coredns_`).

### GET /api/v1/servers/{sid}/zones

```sql
SELECT DISTINCT zone FROM {p}records ORDER BY zone;
```
→ `[{"id":"<zone>","name":"<zone>","url":"/api/v1/servers/{sid}/zones/<zone>"}]`
(zone already carries the trailing dot; emit as-is, plus `kind: "Native"` for
shape parity).

### GET /api/v1/servers/{sid}/zones/{zone_id}

Canonicalize `zone_id` (URL-decode, lowercase, ensure trailing dot). Then:

```sql
SELECT name, ttl, content, record_type FROM {p}records WHERE zone = ?;
```
Zone unknown (0 rows) → 404. Group rows by `(name, record_type)` → one rrset
each: `name` = `rel==''` ? zone : `rel + "." + zone`; `ttl` = the rows' ttl
(first non-NULL, else plugin default 300); `records[i].content` per type:

| record_type | rrset content |
|---|---|
| TXT | `q(content.text)` |
| A / AAAA | `content.ip` |
| CNAME / NS | `content.host` |
| SOA | `content.ns + " " + content.mbox + " 0 " + refresh + " " + retry + " " + expire + " " + (content.ttl or 300)` (serial not stored; emit 0) |
| other/unparsable | raw `content` string (never omit the row) |

`records[i].disabled` = `false` always. Response: `{"id","name","kind":"Native","url","rrsets":[...]}` with struct field order `name`, `type`, `ttl`, `records`.

### PATCH /api/v1/servers/{sid}/zones/{zone_id}  (TXT via acme.sh; generic for others)

For each rrset, canonicalize `name` → `rel`. All statements for one PATCH run
in **one transaction**; any error rolls back everything.

`changetype: REPLACE` (requires `type`, `ttl` ≥ 0, ≥1 record):
```sql
DELETE FROM {p}records WHERE zone = ? AND name = ? AND record_type = ?;
-- then, one INSERT PER RECORD (multi-value = multiple rows, §2):
INSERT INTO {p}records (zone, name, ttl, content, record_type)
VALUES (?, ?, ?, ?, ?);
```
TXT insert content = `{"text": unq(api content)}` (JSON-marshalled, so inner
quotes/backslashes stay valid JSON). A/AAAA → `{"ip": c}`; CNAME/NS →
`{"host": c}`. TTL: from rrset; if absent/0 use `DEFAULT_TTL` env (120).

`changetype: DELETE` (no `ttl`/`records` required):
```sql
DELETE FROM {p}records WHERE zone = ? AND name = ? AND record_type = ?;
```

Success → `204 No Content` (empty body). Unknown zone → 404. Malformed
(unknown changetype, empty/missing name or type, REPLACE without records) →
`422` with PowerDNS error body.

### PUT /api/v1/servers/{sid}/zones/{zone_id}/notify

No-op (CoreDNS reads the DB live). Zone must exist (else 404). Return
`200` + `{"result":"Notification queued"}`.

## 5. Cross-cutting

- Auth: every `/api/v1/*` route requires `X-API-Key` equal to `PDNS_API_KEY`;
  otherwise `401` + error body. `/healthz` is unauthenticated.
- Error body, PowerDNS shape: `{"error":"<message>"}` (400/401/404/422/500).
- Unknown `{sid}` (≠ `PDNS_SERVER_ID`) → 404.
- All responses `Content-Type: application/json` (204 has no body).
- The apex TXT case (`name == zone`) maps to `rel = ''` — same SQL applies.
- ACME tokens are base64url (`A-Za-z0-9_-`), no escaping hazards; the JSON
  marshaller handles anything else.