coredns-powerdns-api-wrapper/PLAN.md

# PLAN.md — PowerDNS API Simulator for CoreDNS (MySQL backend)

> **Status: DELIVERED 2026-07-10.** This document was updated after completion
> to reflect the system as actually built and verified. Deviations from the
> original plan are marked **[as-built]** inline. Phase-gate evidence lives in
> `docs/EVIDENCE.md`; the empirical data-shape contract in `docs/SEMANTICS.md`.

## Goal

Implement a Go web service that exposes a **1:1 replica of the PowerDNS Authoritative HTTP API** (the subset used by ACME clients) and translates those calls into MySQL queries against the database that CoreDNS reads (mysql plugin). The sole consumer is **acme.sh's `dns_pdns` provider** for Let's Encrypt DNS-01 validation. The program must be production-ready and fully tested end-to-end before the work is considered done.

The translation contract in one sentence: **speak PowerDNS at the HTTP edge, read/write CoreDNS rows at the DB edge — in both directions.** A GET must reassemble CoreDNS rows into pdns rrsets; a PATCH must decompose pdns rrsets into CoreDNS rows.

The repository was already initialized with a minimal Go web server; it was extended, not rewritten (WebServer type, gorilla/mux `Routes`, `/version`, graceful shutdown all kept).

## Non-negotiable constraints

- **No config.yaml.** All YAML config code, struct tags, and the YAML dependency are removed. All configuration comes exclusively from **environment variables** (Docker-friendly). **[as-built]** Confirmed: `grep -ri yaml` over the repo finds nothing config-related (`update_child_repos.sh`, the last stray reference, has been deleted).
- **Exact PowerDNS API compatibility** for the endpoints acme.sh touches: same paths, same JSON shapes, same status codes, same headers. The PowerDNS API docs and the `dns_pdns.sh` source are authoritative. **[as-built]** `dns_pdns.sh` was read from the `neilpang/acme.sh` image (`/acmebin/dnsapi/dns_pdns.sh`); its scraping regexes require rrset JSON field order `name`, `type`, `ttl`, `records` (ordered Go structs, never maps) and `changetype: DELETE` bodies **without** `ttl`/`records` — both honored.
- **API key auth** via `X-API-Key` header, value from `PDNS_API_KEY`. Missing/wrong key returns `401` with body `{"error":"Unauthorized"}`.
- **Never hit the Let's Encrypt production CA.** Staging only, in every phase.
- Done means: full acme.sh workflow (staging CA) succeeds and a certificate is emitted. **[as-built]** Achieved twice: once against `go`-built binary, once against the container image.

## Configuration (environment variables)

Implemented in `config.go` exactly as specified; fail-fast errors name the missing variable.

| Variable | Purpose | Default |
|---|---|---|
| `PDNS_API_KEY` | Shared secret for `X-API-Key` | *(required, no default)* |
| `LISTEN_ADDR` | HTTP listen address | `:3000` |
| `MYSQL_DSN` | Go MySQL DSN (`user:pass@tcp(host:3306)/dbname?parseTime=true`) | *(required)* |
| `TABLE_PREFIX` | Table name prefix, mirroring the Corefile `table_prefix` directive (e.g. `coredns_`) | `coredns_` |
| `PDNS_SERVER_ID` | Server id in API paths | `localhost` |
| `DEFAULT_TTL` | TTL when acme.sh doesn't send one | `120` |
| `LOG_LEVEL` | `debug` / `info` / `warn` / `error` (validated) | `info` |
| `PUID` | UID to drop privileges to after startup | *(required)* |
| `PGID` | GID to drop privileges to after startup | *(required)* |

**[as-built]** `PUID=0` or `PGID=0` is rejected at config load ("refusing to serve as root"). `DEFAULT_TTL` must be a positive integer.

## Privilege drop (startup sequence)

Implemented in `privdrop.go`, called first thing in `main()` before any listener, goroutine, or DB connection.

- Order: `setgroups([])` → `setgid(PGID)` → `setuid(PUID)`, exactly.
- Go ≥ 1.16 applies the raw syscalls process-wide; module targets `go 1.22`, builder is go 1.22.2.
- After dropping, verification: `os.Getuid()`/`os.Getgid()` must equal `PUID`/`PGID`, and `syscall.Setuid(0)` must fail. Effective uid/gid logged at `info`. Verification failure → `os.Exit(1)`; never serve as root.
- The MySQL pool is opened **after** the drop.
- `LISTEN_ADDR` is `:3000` (>1024), so no capability retention; everything is dropped.
- **[as-built]** If the process starts as **non-root** (local dev runs), the drop is skipped with a `warn` log ("already running unprivileged"); `PUID`/`PGID` remain required config. This path is unit-tested; the root path is verified at container level (`docker top` shows uid/gid 4321/4321 with test values).

## Phase 0 — Empirical semantics discovery ✅ (findings in `docs/SEMANTICS.md`)

Ground truth was established against the **live** MariaDB and the **running** CoreDNS (never restarted or reconfigured — its behavior is part of the ground truth):

1. **CoreDNS side (verified empirically, not from plugin docs):**
   - Table `coredns_records(id, zone, name, ttl, content TEXT/JSON, record_type)`; `zone` FQDN with trailing dot; `name` **relative to zone, no trailing dot**, `''` at apex; multi-label relative names work; FQDN-in-name is NOT served.
   - **Multiple TXT values on one name = multiple rows**, one `{"text":"..."}` each. A JSON array inside one row is NOT served.
   - TXT quoting: store the **raw unquoted** token; CoreDNS adds wire quoting. Storing pdns-style `\"tok\"` yields literal quotes on the wire (wrong for ACME).
   - Queries are case-insensitive; store names lowercase.
   - **Visibility [key as-built finding]:** record lookups hit MySQL live — writes to an *existing* zone are visible **immediately**. `zone_update_interval` (120s in the Corefile) only gates the cached **zone list**, i.e. newly created zones. It was NOT lowered for dev (CoreDNS is hands-off); it didn't need to be.
2. **PowerDNS side:** rrset model `{name, type, ttl, changetype, records:[{content, disabled}]}`; `REPLACE` replaces the entire `(name, type)` set; `DELETE` removes it (and arrives without `ttl`/`records`).
3. The full mapping table (pdns operation → exact SQL, TXT quote handling, FQDN normalization) is in `docs/SEMANTICS.md` §4; every handler implements it.

## API surface implemented (what acme.sh `dns_pdns` uses)

All under `/api/v1`, all requiring `X-API-Key`; unknown `{server_id}` → 404 on every route; zone_id accepted with/without trailing dot and URL-encoded dots; `Content-Type: application/json` on all responses.

1. **`GET /zones`** — zone list from `SELECT DISTINCT zone`; `id`/`name` canonical with trailing dot (acme.sh `_get_root` substring-matches `"name":"zone."`), plus `url` and **[as-built]** `kind:"Native"` for shape parity.
2. **`GET /zones/{zone_id}`** — zone detail with `rrsets` aggregated from rows grouped by `(name, type)`. TXT contents re-quoted (`"\"tok\""`); A/AAAA→`ip`, CNAME/NS→`host`, SOA assembled as `ns mbox 0 refresh retry expire minttl` (serial not stored → 0); unparsable content passed through raw. 0 rows → 404.
3. **`PATCH /zones/{zone_id}`** — `REPLACE`: transactional delete-all + one INSERT **per record** (the two-TXT-values-on-one-`_acme-challenge` case is the critical path and is covered by tests, curl, dig, and the E2E). `DELETE`: delete-all for `(zone, name, type)`. `204` on success, `422` + `{"error":"<specific>"}` on malformed rrsets, `404` on unknown zone. **[as-built]** Write support: TXT/A/AAAA/CNAME/NS; names lowercased, must be within the zone (label-boundary checked, `notexample.com.` vs `example.com.` → 422); TTL absent/0 → `DEFAULT_TTL`.
4. **`PUT /zones/{zone_id}/notify`** — no-op (CoreDNS reads MySQL live); `200` + `{"result":"Notification queued"}`; unknown zone → 404.

## MySQL layer

- Exact live schema, table name from `TABLE_PREFIX` (`store.go`).
- Prepared statements; REPLACE (delete+insert) wrapped in a transaction, rollback on any error.
- **[as-built]** Pool: MaxOpenConns 10, MaxIdleConns 5, ConnMaxLifetime 60s (mirrors the Corefile limits); ping at startup with 5s timeout, fail fast.
- **[as-built]** Driver pinned to `go-sql-driver/mysql v1.9.3` (v1.10 forces `go 1.24`, above the module's `go 1.22`).

## Development / test environment (as actually used)

- Dev server on `:3000`, exposed via the running ngrok tunnel:
  **`https://claude-test-endpoint.ngrok-free.dev` → `http://127.0.0.1:3000`**
- CoreDNS running on port **5053**, public forward **`1.2.3.4:53` → localhost:5053**.
- **Test zone: `example.com`** (SAN: `www.example.com`) — **placeholder; this will need changing before any real run.** ACME validation resolves through the public DNS tree even against the staging CA, so the zone must be genuinely delegated from its public parent to the CoreDNS IP (`1.2.3.4`) — and Let's Encrypt additionally refuses to issue for `example.com` itself by policy (`rejectedIdentifier`). The delivery E2E therefore ran against a real delegated sub-zone, redacted to `example.com` throughout these docs. The zone rows (SOA, apex NS/A, `ns1` A → `1.2.3.4`, `www` CNAME) live in `coredns_records`.
- **dig caveat [as-built]:** this dev host's outbound UDP/53 is transparently intercepted by a middlebox (proven: an IP with no DNS server "answers"). `dig @1.2.3.4` was unreachable during part of development and later worked (verified genuine via TCP + answer-shape/serial match with the local instance). When in doubt, verify against `dig @127.0.0.1 -p 5053` (same instance) and externally via DoH (`https://dns.google/resolve?...` reports `"Response from 1.2.3.4"`).

## Test plan (all executed; evidence in `docs/EVIDENCE.md`)

### Phase 1 — Unit/handler tests ✅
27 top-level tests: auth (missing/wrong/correct key), zone listing, zone detail rrset aggregation + JSON field order, PATCH REPLACE/DELETE semantics, TXT quote handling both directions, FQDN normalization + boundary cases, notify, trailing-dot equivalence, 404/422 bodies, config fail-fast + defaults, privdrop non-root path. **[as-built]** DB-backed tests run against an isolated `test_pdnsapi_records` table on the live MariaDB (created/seeded/dropped per run; skip cleanly if DB unreachable); the real `coredns_records` is never touched.

### Phase 2 — Manual API tests with curl (localhost) ✅
```
curl -s -H "X-API-Key: $PDNS_API_KEY" http://127.0.0.1:3000/api/v1/servers/localhost/zones
curl -s -H "X-API-Key: $PDNS_API_KEY" http://127.0.0.1:3000/api/v1/servers/localhost/zones/example.com.
curl -s -X PATCH -H "X-API-Key: $PDNS_API_KEY" -H "Content-Type: application/json" \
  -d '{"rrsets":[{"name":"_acme-challenge.example.com.","type":"TXT","ttl":120,"changetype":"REPLACE","records":[{"content":"\"test-token-123\"","disabled":false}]}]}' \
  http://127.0.0.1:3000/api/v1/servers/localhost/zones/example.com.
```
Verified: wrong key → 401, missing zone → 404, malformed rrset → 422, two-record REPLACE on one `_acme-challenge` name → 204 with exactly two `{"text":...}` rows.

### Phase 3 — DNS propagation check with dig ✅
```
dig @1.2.3.4 TXT _acme-challenge.example.com +norecurse +noall +answer
# fallback if the dev host's UDP/53 interception bites: dig @127.0.0.1 -p 5053 ...
```
Both values of the two-record REPLACE served; after `changetype: DELETE` → NXDOMAIN. Writes were visible immediately (see Phase 0 visibility finding — no `zone_update_interval` wait for records on existing zones).

### Phase 4 — Public endpoint test (ngrok) ✅
Phase 2 calls repeated against `https://claude-test-endpoint.ngrok-free.dev`: identical bodies and status codes (200/204/401/404/422), PATCH-through-tunnel served by CoreDNS.

### Phase 5 — Full E2E with acme.sh (Let's Encrypt STAGING only) ✅
```
docker run --rm -v ~/acme.sh:/acme.sh \
  -e PDNS_Url="https://claude-test-endpoint.ngrok-free.dev" \
  -e PDNS_ServerId="localhost" \
  -e PDNS_Token="$PDNS_API_KEY" \
  -e PDNS_Ttl="120" \
  neilpang/acme.sh --issue --staging --dns dns_pdns \
  -d example.com -d www.example.com
```
All five checks passed: root-zone detection via `GET /zones` (server log), TXT PATCHes for **both** names (MySQL rows + dig), staging CA validated both identifiers, staging cert (issuer "(STAGING) Artificial Amaranth YE1", both SANs) saved under `~/acme.sh`, cleanup DELETEs confirmed (DB empty, NXDOMAIN).

**[as-built] flakiness note:** no `--dnssleep` is needed (propagation is immediate; acme.sh's default 20s check passes). One attempt failed with a **transient LE "secondary validation" timeout** — that is remote reachability of `1.2.3.4` from LE's vantage points, not propagation; the retry succeeded unchanged. If issuance flakes, retry before touching anything.

### Phase 6 — Production readiness ✅
- Dockerfile untouched (already compliant: 2-stage build → `scratch`, **no `USER` directive** — the binary itself drops to `PUID`/`PGID`).
- **[as-built]** makefile amendments (style preserved): `run`/`dockerrun` publish container port **3000** (was 80), pass through the nine app env vars with `--env`; `dockerrun` no longer mounts `config.yaml` and uses the `:$${APP_VERSION}` tag. `.vars` (gitignored) created locally for the build (`bryanpedini/gobuilder:1.22.2-alpine3.19`, image `git.bjphoster.com/source/coredns-powerdns-api-wrapper`).
- Privilege-drop checks: unit test (non-root path) + container-level — image run with test `PUID=4321`/`PGID=4321`; startup log `privileges dropped uid=4321 gid=4321` and host-side `docker top` shows the serving process as 4321/4321, not root.
- Graceful shutdown (SIGTERM), slog request logging at `info`, structured errors, `/healthz` (MySQL ping, unauthenticated, no data) — all in place.
- README: env var table (incl. `TABLE_PREFIX` ↔ Corefile `table_prefix`), docker run example, acme.sh staging/production usage, propagation/`--dnssleep` guidance, known limitations.
- Final Phase 5 re-run against the built image (`:0.1.0`, `--network host` since MariaDB binds to 127.0.0.1 only): cached staging authorizations were **deactivated first** so DNS-01 genuinely re-ran through the container; fresh cert issued.

## Definition of done — all satisfied 2026-07-10

- `docs/SEMANTICS.md` exists; every handler implements its mapping table. ✅
- All YAML config code and files removed; env-vars-only confirmed by grep. ✅
- All Go tests pass (`go test ./... -count=1`). ✅
- Phases 2–5 pass, evidence recorded in `docs/EVIDENCE.md`. ✅
- acme.sh issues a staging certificate for the test zone + `www` SAN with zero acme.sh-side workarounds. ✅ (zone is `example.com`, see environment section)
- Container image builds and runs the same E2E, serving process verified as `PUID`/`PGID`, not root. ✅

## Explicit non-goals (unchanged, binding)

- Full PowerDNS API coverage (zone CRUD beyond GET, cryptokeys, metadata, search, TSIG).
- DNSSEC.
- Any DNS serving by this service itself — CoreDNS remains the only resolver.