# Domain verification on AISend: queries behind every number

Companion data for https://aisend.app/blog/why-domain-verification-fails

Snapshot: 2026-11-17, about 09:40-10:10 UTC. `SELECT` only. Output is counts; customer domain names and emails are never published.

## Setup

Run against AISend's production Postgres database, read-only (tables `domains`, `users`).

## Q1. Raw table (55 rows, 33 accounts; 32 verified, 23 pending)

```sql
SELECT status, count(*), min(created_at)::date, max(created_at)::date FROM domains GROUP BY 1;
-- pending 23 (2026-03-21..2026-11-13) | verified 32 (2026-03-16..2026-11-02)
SELECT count(*) AS total, count(DISTINCT user_id) AS accounts FROM domains;   -- 55 | 33
SELECT enumlabel FROM pg_enum e JOIN pg_type t ON t.oid = e.enumtypid WHERE typname = 'domain_status';  -- pending, verified, failed
```

## Q2. Cohort definition (23 domains)

Exclusions, applied in this order:

1. Our own product domains: `domains.name` matches `(aisend|bugsly|cvposh|seobolt|growfast|remixviral|autoshort|storyshort|airdropfi|bettercv|billsai|example\.)` -> 17 rows (all inspected by eye: they are our brands and their `mail.` subdomains).
2. Founder or internal test accounts: owner email on one of our product domains, on `example.`, containing `test|probe|dogfood|audit|check`, or one of two founder aliases -> 8 more rows (7 verified founder-owned domains, 1 pending "audit/check"-named account).
3. Banned accounts (`users.banned_at IS NOT NULL`) -> 7 more rows (2 pending, 5 verified).

```sql
WITH c AS (
  SELECT d.id, d.status, d.name, d.created_at, d.verified_at, d.user_id
  FROM domains d JOIN users u ON u.id = d.user_id
  WHERE NOT (d.name ~* '(aisend|bugsly|cvposh|seobolt|growfast|remixviral|autoshort|storyshort|airdropfi|bettercv|billsai|example\.)')
    AND NOT (u.email ~* '@(aisend|bugsly|cvposh|seobolt|growfast|remixviral|autoshort|storyshort|airdropfi|bettercv|billsai)\.|example\.|test|probe|<two founder aliases>|dogfood|audit|check')
    AND u.banned_at IS NULL
)
SELECT status, count(*) n, count(DISTINCT user_id) accounts FROM c GROUP BY 1;
-- pending 19 (19 accounts) | verified 4 (4 accounts)
```

Breakdown of the exclusion layers (counts only; the reasons are booleans, no addresses printed):

```sql
SELECT count(*) total,
  count(*) FILTER (WHERE d.name ~* '(aisend|bugsly|cvposh|seobolt|growfast|remixviral|autoshort|storyshort|airdropfi|bettercv|billsai|example\.)') own_domain,
  count(*) FILTER (WHERE u.banned_at IS NOT NULL) banned
FROM domains d JOIN users u ON u.id = d.user_id;     -- 55 | 17 | 7
```

## Q3. Age of the pending domains (2 under 7 days, 0 from 7 to 30, 17 over 30)

```sql
-- append to the cohort CTE `c`
SELECT count(*) FILTER (WHERE created_at > now() - interval '7 days')                                       AS lt7d,
       count(*) FILTER (WHERE created_at <= now() - interval '7 days' AND created_at > now() - interval '30 days') AS d7_30,
       count(*) FILTER (WHERE created_at <= now() - interval '30 days')                                      AS gt30d
FROM c WHERE status = 'pending';
-- 2 | 0 | 17
```

## Q4. Time to verify (n = 4; median 7.8 min = 0.1295 h; values 0.02, 0.06, 0.20, 1.52 hours)

```sql
-- append to the cohort CTE `c`
SELECT round(extract(epoch FROM (verified_at - created_at))/3600.0, 2) AS hrs FROM c WHERE status = 'verified' ORDER BY 1;
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch FROM (verified_at - created_at))/3600.0) AS med_hrs FROM c WHERE status = 'verified';
```

`verified_at` is set by the first successful `verifyDomain` call (`packages/api/src/services/domain.ts`: "Preserve the FIRST verification timestamp"), so it is time to the first successful check, not the instant DNS propagated.

## Q5. Apex vs subdomain (3 of 16 apex verified; 1 of 7 subdomains)

```sql
-- append to the cohort CTE `c`; 'subdomain' = name has more than one dot (two-label names are apex)
SELECT (name !~ '^[^.]+\.[^.]+$') AS is_subdomain, status, count(*) FROM c GROUP BY 1,2 ORDER BY 1,2;
-- false/pending 13, false/verified 3, true/pending 6, true/verified 1
```

## Q6. Number of DKIM records per domain (SES Easy DKIM issues three)

```sql
SELECT array_length(string_to_array(dkim_host, ','), 1) AS n, count(*) FROM domains GROUP BY 1;
-- 3: 49 rows | 1: 4 rows | NULL: 2 rows
```

## Q7. Public-DNS snapshot of the 19 pending domains

Step 1: export the rows (names stay on the local machine in a temp file that is deleted afterwards; the article never prints them):

```sql
SELECT d.id, d.name, d.dkim_host, d.dkim_value, d.created_at::date
FROM domains d JOIN users u ON u.id = d.user_id
WHERE d.status = 'pending' AND <same cohort filters as Q2>;
```

Step 2: run the DNS script (below). It prints counts only. For every domain it asks 1.1.1.1 and 8.8.8.8 (union of answers, timeouts retried and never counted as answers):

- NS of the registrable domain (heuristic zone: last two labels, three for `co.xx`-style suffixes). No answer -> category A.
- For each DKIM host: CNAME equals the expected value -> ok; a CNAME with another value -> wrong; A/AAAA answers in Cloudflare ranges -> proxied; `host.<zone>` carrying the expected CNAME -> doubled host; otherwise missing. A wildcard probe (`zz-probe-x9f3.<domain>`) separates a wildcard from a real record.
- TXT at the domain: how many `v=spf1` records.

Result on 2026-11-17 (second corrected run; the first run counted dig's "connection timed out" text as an answer and was discarded):

```
cohort 19
11 A. registrable zone has no NS records (not registered or not delegated)
 1 D. DKIM names answer with Cloudflare addresses (proxied)
 1 G. a wildcard or address record answers instead of the CNAME
 6 H. no DKIM CNAMEs published at all
domains (with a live zone) that already have an SPF record at the name: 5 ; with >1: 0
```

Independent check of category A: `dig NS <registrable domain>` returned status NXDOMAIN from both 1.1.1.1 and 8.8.8.8 for all 11 (8 returned NS records). NXDOMAIN means the name is not in DNS at all (never registered, expired, or a typo); it does not tell which.

## DNS script

The script is published next to this file: https://aisend.app/data/domain-verification-dns-snapshot.py

## External sources

- Resend, add a domain: https://resend.com/docs/add-a-domain (subdomain recommendation; "make sure not to use proxying features (e.g., Cloudflare's orange cloud)" for CNAMEs; often within 15 minutes, up to 72 hours).
- Resend, verified domains: https://resend.com/docs/dashboard/domains/introduction (subdomain benefits; keep an established sender if moving providers).
- AWS SES identities: https://docs.aws.amazon.com/ses/latest/dg/creating-identities.html (DKIM verifies domain ownership; domain verification covers subdomains and addresses).
- AWS SES DKIM troubleshooting: https://docs.aws.amazon.com/ses/latest/dg/troubleshoot-dkim.html (wait up to 72 hours; providers that append the domain, example `example._domainkey.example.com.example.com`).
- AWS SES SPF: https://docs.aws.amazon.com/ses/latest/dg/send-email-authentication-spf.html (default MAIL FROM subdomain of amazonses.com; own SPF only needed for a custom MAIL FROM).
- Cloudflare proxy status: https://developers.cloudflare.com/dns/proxy-status/ (only A, AAAA, CNAME can be proxied; CNAMEs that prove domain ownership should not be proxied).
- RFC 7208 section 4.5 (more than one SPF record -> permerror): https://datatracker.ietf.org/doc/html/rfc7208#section-4.5
