c-relay-pg/plans/profile_cache_plan.md

# Profile (kind-0) Cache Plan

Make username/profile resolution a first-class c-relay-pg feature backed by a
dedicated `profiles` table, replacing the three independent ad-hoc
implementations that exist today. Phase 1 (name/metadata cache) is scoped for
implementation now; Phase 2 (image caching) is designed here but deferred.

## 0. Scope Boundary

**In scope:** `src/` (schema + C data layer) and `admin/` (the PHP admin).

**Out of scope:** the top-level [`api/`](../api/index.js) directory — the legacy
embedded JS admin compiled into the binary via
[`src/embedded_web_content.h`](../src/embedded_web_content.h) and served by
[`handle_embedded_file_request()`](../src/api.c:1006). It is being replaced by
`admin/` and is deliberately left untouched.

Two consequences worth being explicit about:

1. **Do not "fix" the duplicated logic in `api/index.js`.** It carries its own
   copies of the profile-name preference ([`api/index.js:962`](../api/index.js:962))
   and profile-picture handling ([`api/index.js:986`](../api/index.js:986)), plus
   the same class of unescaped-`innerHTML` issue described in §2A.2. These are
   knowingly left as-is because the whole tree is slated for removal. Note this
   means the XSS exposure persists for as long as the embedded UI remains
   reachable on the relay's HTTP port — a reason to prioritize retiring `api/`,
   tracked separately from this plan.
2. **The C-side changes still matter to both.** [`src/api.c`](../src/api.c) serves
   the JSON that the embedded UI consumes, so the batching work in §2.5 benefits
   `api/` incidentally. The C API must therefore stay backward-compatible: keep
   emitting the existing `name` field (now the resolved value) alongside the new
   `display_name` / `best_name` fields, so the legacy frontend keeps working
   unchanged until it is deleted.

---

## 1. Current State

Profile display-name resolution was introduced alongside the caching service and
never generalized. There are **three separate implementations**, none cached:

### 1.1 C backend — per-pubkey query inside a loop

[`postgres_db_get_profile_metadata()`](../src/db_ops_postgres.c:2523) runs:

```sql
SELECT content FROM events WHERE pubkey = $1 AND kind = 0
ORDER BY created_at DESC LIMIT 1
```

then `cJSON_Parse`es `content` and copies out eight known fields
(`name`, `display_name`, `picture`, `about`, `nip05`, `website`, `lud16`, `lud06`).

Dispatched through [`db_get_profile_metadata()`](../src/db_ops.c:189); the SQLite
backend is a `NULL` stub ([`src/db_ops.c:389`](../src/db_ops.c:389)).

Called from three places, **always inside a row loop** — a classic N+1:

| Call site | Loop over | Queries per response |
|---|---|---|
| [`query_top_pubkeys()`](../src/api.c:503) | top 10 pubkeys | 10 |
| [`src/api.c:1646`](../src/api.c:1646) | top pubkeys (2nd copy) | 10 |
| caching follows status [`src/config.c:4298`](../src/config.c:4298) | every followed pubkey | 1 per follow (unbounded) |

The `config.c` loop additionally issues a per-pubkey kind-count query
([`src/config.c:4326`](../src/config.c:4326)), an outbox lookup
([`src/config.c:4346`](../src/config.c:4346)) and a relay-progress query
([`src/config.c:4356`](../src/config.c:4356)) — so a relay following 500 authors
performs ~2000 queries to render one admin panel.

The `display_name || name` preference logic is **duplicated verbatim** at all
three call sites.

### 1.2 PHP admin — repeated LATERAL joins

[`admin/api/stats.php:90`](../admin/api/stats.php:90) and
[`admin/api/caching.php:23`](../admin/api/caching.php:23) each hand-roll:

```sql
LEFT JOIN LATERAL (
  SELECT content FROM events WHERE pubkey = e.pubkey AND kind = 0
  ORDER BY created_at DESC LIMIT 1
) p ON true
... p.content::json->>'name', p.content::json->>'display_name'
```

with the same `$display_name ?: $name` fallback repeated in PHP. `content::json`
re-parses the JSON text on every single admin page poll. The cast is also
fragile: a malformed kind-0 `content` raises a PostgreSQL error that aborts the
whole query (the `try/catch` then silently returns an empty result set).

### 1.3 Browser JS — fetches from public relays

[`loadUserProfile()`](../admin/assets/app.js:626) opens WebSocket connections to
**third-party public relays** to fetch the logged-in admin's own kind-0, even
though the relay's own database very likely has it. A third variant of the
name-preference logic lives at [`admin/assets/app.js:650`](../admin/assets/app.js:650)
(`profile.name || profile.display_name || profile.displayName`) — note this one
prefers `name` over `display_name`, the **opposite** of the C and PHP versions,
so the same user can render under two different names in one UI.

Profile images are hotlinked straight to whatever URL the kind-0 contains
([`admin/assets/app.js:653`](../admin/assets/app.js:653)), which leaks the admin's
IP to arbitrary hosts and breaks silently on dead links.

### 1.4 Conclusion

A cache table is clearly warranted:

- Kind 0 is **replaceable** — the unique index
  [`uq_events_replaceable_pubkey_kind`](../src/pg_schema.sql:82) guarantees at
  most one kind-0 row per pubkey. A `profiles` table is therefore a strict 1:1
  projection of existing data and can be rebuilt from scratch at any time. No
  risk of divergence-by-design.
- Profiles change rarely but are read constantly.
- Parsing JSON at write time (once per profile update) instead of read time
  (every page poll × every row) is a large, cheap win.
- One canonical name-preference rule fixes the inconsistency across the three
  layers.

---

## 2. Phase 1 — `profiles` Table

### 2.1 Schema

Added to [`src/pg_schema.sql`](../src/pg_schema.sql) before the `COMMIT;` at
[line 405](../src/pg_schema.sql:405), and mirrored into
[`src/pg_schema.h`](../src/pg_schema.h) as escaped C string literals.

```sql
CREATE TABLE IF NOT EXISTS profiles (
    pubkey        TEXT PRIMARY KEY,
    event_id      TEXT NOT NULL,
    created_at    BIGINT NOT NULL,
    name          TEXT NOT NULL DEFAULT '',
    display_name  TEXT NOT NULL DEFAULT '',
    about         TEXT NOT NULL DEFAULT '',
    picture       TEXT NOT NULL DEFAULT '',
    banner        TEXT NOT NULL DEFAULT '',
    nip05         TEXT NOT NULL DEFAULT '',
    website       TEXT NOT NULL DEFAULT '',
    lud16         TEXT NOT NULL DEFAULT '',
    lud06         TEXT NOT NULL DEFAULT '',
    raw_content   TEXT NOT NULL DEFAULT '',
    parse_ok      BOOLEAN NOT NULL DEFAULT TRUE,
    updated_at    BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT
);

CREATE INDEX IF NOT EXISTS idx_profiles_name ON profiles(name)
    WHERE name <> '';
CREATE INDEX IF NOT EXISTS idx_profiles_display_name ON profiles(display_name)
    WHERE display_name <> '';
CREATE INDEX IF NOT EXISTS idx_profiles_nip05 ON profiles(nip05)
    WHERE nip05 <> '';
```

Notes:
- **`name` and `display_name` are both stored verbatim, always.** Storing both
  is free (they are short strings on a table with one row per pubkey), and it
  means the question "which field do Nostr clients actually populate?" can be
  answered from real data later rather than guessed at now — see
  [§2.9](#29-which-field-do-people-actually-use). Neither field is ever
  discarded, overwritten by the other, or collapsed into a single value at write
  time.
- There is deliberately **no generated `best_name` column.** An earlier draft
  had one; it was wrong. A `STORED` generated column freezes the preference rule
  into the schema, so changing which field is displayed would require a schema
  migration and a full-table rewrite. Display preference is a presentation
  decision and belongs at read time.
- `raw_content` keeps the original JSON so non-standard fields (including
  `displayName`, the camelCase variant some clients emit — see
  [`admin/assets/app.js:650`](../admin/assets/app.js:650)) remain reachable
  without re-querying `events`.
- `parse_ok = FALSE` records "we saw a kind-0 but its content was not valid
  JSON" — distinct from "no profile at all" (row absent). This makes the
  malformed-JSON case explicit instead of an aborted query.
- Empty-string defaults rather than `NULL` keep the C accessors branch-free.

### 2.1.1 Display preference as configuration

The preference rule lives in the existing `config` table
([`src/pg_schema.sql:192`](../src/pg_schema.sql:192)) so it can be changed at
runtime through the normal admin config path, with no migration:

```sql
INSERT INTO config (key, value, data_type, description, category, requires_restart)
VALUES ('profile_name_preference', 'display_name',
        'string', 'Which kind-0 field to prefer for display: display_name or name',
        'display', 0)
ON CONFLICT (key) DO NOTHING;
```

Valid values: `display_name` (prefer `display_name`, fall back to `name`) or
`name` (the reverse). Default `display_name`, matching the current C and PHP
behaviour so nothing visibly changes on upgrade.

Each layer gets **one** resolver that reads this key — replacing the four
scattered inline copies with one function per layer, while keeping the choice
adjustable:

```c
// Applies profile_name_preference; falls back to the other field when the
// preferred one is empty. Returns "" when neither is set (never NULL).
const char* profile_display_name(const cJSON* profile);
```

Every profile object returned to a UI carries `name`, `display_name`, **and** the
resolved `best_name`, so a consumer can render the resolved label while still
having both raw values available.

### 2.2 Population — PostgreSQL trigger

A trigger keeps the table correct regardless of which process writes the event
(relay ingest, the caching inbox poller, or a manual `psql` insert), so no
writer can bypass it.

```sql
CREATE OR REPLACE FUNCTION sync_profile_from_event() RETURNS TRIGGER AS $$
DECLARE
    j JSONB;
BEGIN
    IF NEW.kind <> 0 THEN
        RETURN NEW;
    END IF;

    BEGIN
        j := NEW.content::jsonb;
        IF jsonb_typeof(j) <> 'object' THEN
            j := NULL;
        END IF;
    EXCEPTION WHEN others THEN
        j := NULL;
    END;

    INSERT INTO profiles (
        pubkey, event_id, created_at,
        name, display_name, about, picture, banner,
        nip05, website, lud16, lud06,
        raw_content, parse_ok, updated_at
    ) VALUES (
        NEW.pubkey, NEW.id, NEW.created_at,
        COALESCE(j->>'name',''),
        COALESCE(j->>'display_name',''),
        COALESCE(j->>'about',''),
        COALESCE(j->>'picture',''),
        COALESCE(j->>'banner',''),
        COALESCE(j->>'nip05',''),
        COALESCE(j->>'website',''),
        COALESCE(j->>'lud16',''),
        COALESCE(j->>'lud06',''),
        NEW.content, (j IS NOT NULL),
        EXTRACT(EPOCH FROM NOW())::BIGINT
    )
    ON CONFLICT (pubkey) DO UPDATE SET
        event_id     = EXCLUDED.event_id,
        created_at   = EXCLUDED.created_at,
        name         = EXCLUDED.name,
        display_name = EXCLUDED.display_name,
        about        = EXCLUDED.about,
        picture      = EXCLUDED.picture,
        banner       = EXCLUDED.banner,
        nip05        = EXCLUDED.nip05,
        website      = EXCLUDED.website,
        lud16        = EXCLUDED.lud16,
        lud06        = EXCLUDED.lud06,
        raw_content  = EXCLUDED.raw_content,
        parse_ok     = EXCLUDED.parse_ok,
        updated_at   = EXCLUDED.updated_at
      -- Never let an older kind-0 overwrite a newer one.
      WHERE EXCLUDED.created_at >= profiles.created_at;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_events_sync_profile ON events;
CREATE TRIGGER trg_events_sync_profile
AFTER INSERT OR UPDATE OF content ON events
FOR EACH ROW EXECUTE FUNCTION sync_profile_from_event();
```

The `NEW.kind <> 0` early return means the cost for the 99.9% of events that are
not profiles is one integer comparison — negligible next to the two triggers
already firing on every insert
([`trg_events_set_derived_fields`](../src/pg_schema.sql:160),
[`trg_events_sync_event_tags`](../src/pg_schema.sql:166),
[`trg_notify_event_stored`](../src/pg_schema.sql:283)).

**Deletion:** add a companion `AFTER DELETE` trigger removing the `profiles` row
when its backing kind-0 is deleted (NIP-09 via [`src/nip009.c`](../src/nip009.c)),
guarded on `OLD.kind = 0 AND profiles.event_id = OLD.id` so a delete of a
superseded event does not drop a current profile.

### 2.3 One-time backfill

Existing databases already hold kind-0 events. Following the established
guarded-migration pattern used for `d_tag_value`
([`src/pg_schema.sql:57`](../src/pg_schema.sql:57)), the backfill runs only on
the version transition, not on every boot:

```sql
DO $$
BEGIN
    IF COALESCE((SELECT value FROM schema_info WHERE key = 'version'), '0') < '6' THEN
        INSERT INTO profiles (pubkey, event_id, created_at, name, display_name,
                              about, picture, banner, nip05, website, lud16, lud06,
                              raw_content, parse_ok)
        SELECT e.pubkey, e.id, e.created_at,
               COALESCE(c.j->>'name',''), COALESCE(c.j->>'display_name',''),
               COALESCE(c.j->>'about',''), COALESCE(c.j->>'picture',''),
               COALESCE(c.j->>'banner',''), COALESCE(c.j->>'nip05',''),
               COALESCE(c.j->>'website',''), COALESCE(c.j->>'lud16',''),
               COALESCE(c.j->>'lud06',''),
               e.content, (c.j IS NOT NULL)
          FROM events e
          LEFT JOIN LATERAL (
              SELECT CASE WHEN e.content ~ '^\s*\{' THEN
                       (SELECT x FROM jsonb(e.content::jsonb) AS x)
                     END AS j
          ) c ON true
         WHERE e.kind = 0
        ON CONFLICT (pubkey) DO NOTHING;
    END IF;
END
$$;
```

The `::jsonb` cast can still raise on malformed content. Implementation should
use a small `PL/pgSQL` loop with a per-row `EXCEPTION` block, or a
`safe_jsonb(text)` helper function marked `IMMUTABLE` that returns `NULL` on
parse failure — cleaner and reusable by the trigger too. Prefer the
`safe_jsonb()` helper and use it in both the trigger and the backfill.

Bump `EMBEDDED_PG_SCHEMA_VERSION` from `"5"` to `"6"` in
[`src/pg_schema.h:4`](../src/pg_schema.h:4) and the `schema_info` insert at
[`src/pg_schema.sql:260`](../src/pg_schema.sql:260).

`postgres_db_apply_schema()` ([`src/db_ops_postgres.c:138`](../src/db_ops_postgres.c:138))
runs the whole embedded script at startup, so existing deployments upgrade
automatically. There is no generator script for `pg_schema.h` — it is a
hand-maintained mirror, so **both files must be edited and kept identical**.

### 2.4 C API

Replace the single-row helper with a batch-capable pair in
[`src/db_ops.h`](../src/db_ops.h:144):

```c
// Single profile from the profiles cache. NULL if no profile is cached.
// Result object always contains "name", "display_name" (each possibly "")
// and the resolved "best_name". Caller must cJSON_Delete().
cJSON* db_get_profile(const char* pubkey);

// Batch lookup: one query for many pubkeys. Returns an object keyed by
// pubkey hex -> profile object. Pubkeys with no cached profile are absent.
// Caller must cJSON_Delete().
cJSON* db_get_profiles(const char** pubkeys, int count);
```

`db_get_profiles()` issues a single `WHERE pubkey = ANY($1::text[])` query,
collapsing the N+1 loops into one round trip.

`db_get_profile_metadata()` is retained as a deprecated thin wrapper over
`db_get_profile()` so nothing breaks mid-refactor, then removed once all call
sites are migrated.

SQLite stubs in [`src/db_ops.c:389`](../src/db_ops.c:389) continue returning
`NULL` — the `profiles` table is PostgreSQL-only, consistent with how the
caching tables are handled.

### 2.5 C call-site migration

| File | Change |
|---|---|
| [`src/api.c:502`](../src/api.c:502) | Collect the 10 pubkeys, one `db_get_profiles()` call, then attach `name`/`display_name`/`best_name`/`picture` from the map. Replace the inline preference logic with `profile_display_name()`. |
| [`src/api.c:1645`](../src/api.c:1645) | Same. Consider factoring the two near-identical blocks into one shared `api_attach_profile_fields()` helper. |
| [`src/config.c:4297`](../src/config.c:4297) | Batch all followed pubkeys up front (they are already fully enumerated by the outer query) and look them up from the returned map inside the loop. |

The `config.c` loop's other per-row queries (kind counts, relay progress) are
out of scope here but are noted as the next optimization target — they can
become two `GROUP BY` queries executed once.

### 2.6 PHP migration

Add one helper to [`admin/lib/helpers.php`](../admin/lib/helpers.php):

```php
/**
 * Batch-resolve profiles from the cache.
 * Returns [pubkey_hex => ['name'=>..., 'display_name'=>...,
 *                         'best_name'=>..., 'picture'=>..., 'nip05'=>...]].
 */
function profile_map(array $pubkeys): array

/** Applies the profile_name_preference config key. Never returns null. */
function profile_display_name(array $profile): string
```

`profile_map()` is a single parameterized `WHERE pubkey = ANY(...)` query against
`profiles`, returning both raw name fields plus the resolved label. Then:

- [`admin/api/stats.php:90`](../admin/api/stats.php:90) — drop the
  `LEFT JOIN LATERAL` and the `content::json` casts; the top-pubkeys query
  becomes a plain `GROUP BY e.pubkey`, and names come from `profile_map()`.
  This also removes the `GROUP BY e.pubkey, p.content` grouping-by-a-JSON-blob
  wart.
- [`admin/api/caching.php:23`](../admin/api/caching.php:23) — same; or simply
  `LEFT JOIN profiles p ON p.pubkey = fp.pubkey` and select `p.name,
  p.display_name`, which is a cheap indexed join now that no subquery or parsing
  is involved.
- Replace both copies of the `$display_name ?: $name` fallback with
  `profile_display_name()`.

### 2.7 JS migration

- Add a read-only admin endpoint (`admin/api/profile.php?pubkey=...`) returning
  the cached profile.
- [`loadUserProfile()`](../admin/assets/app.js:626) tries that endpoint first and
  only falls back to public relays if the relay has no cached kind-0 for the
  logged-in admin (a real possibility for a fresh relay), then keeps the existing
  render path.
- Consume the server-provided `best_name` instead of re-deriving a preference in
  the browser, so all three layers finally agree and the JS copy at
  [`admin/assets/app.js:650`](../admin/assets/app.js:650) — which currently
  prefers `name`, the opposite of C and PHP — stops disagreeing. Keep
  `displayName` (camelCase) handling only in the public-relay fallback path,
  where raw client JSON is parsed directly.

---

## 2A. Hostile Characters in Names

Nostr names are attacker-controlled free-form UTF-8. The guiding principle:

> **Store bytes verbatim. Neutralize at the point of rendering.**

Sanitizing at write time would be wrong — it is lossy, irreversible, and the
"correct" transformation differs per output context (HTML body vs. attribute vs.
JSON vs. CSV vs. terminal log). A name mangled on the way into the cache can
never be recovered, and the cache would no longer faithfully mirror the kind-0
event. So the cache table stores exactly what the user published.

**But "it's a frontend issue" is only ~90% true.** There is one true storage-layer
concern, and one place where the current frontend is actively unsafe.

### 2A.1 Storage-layer concern: NUL bytes (must handle at write time)

PostgreSQL `TEXT` **cannot** store `U+0000`. A kind-0 containing `\u0000` in its
JSON string makes `->>` yield a value that PostgreSQL refuses to store, raising
`ERROR: unsupported Unicode escape sequence` — which would abort the trigger and
therefore **reject the whole event insert**. That turns a cosmetic nuisance into
a denial-of-service on event ingestion.

This is not a presentation problem and must be handled in the trigger:

```sql
-- Strip NUL only; everything else is preserved byte-for-byte.
replace(COALESCE(j->>'name',''), E'\\u0000', '')
```

Implement as a small `sanitize_pg_text(text)` helper used for every extracted
string column. It removes **only** characters PostgreSQL structurally cannot
store — not "weird" characters generally. Invalid UTF-8 byte sequences are
already rejected earlier by `cJSON` parsing and by the `safe_jsonb()` helper
(the row lands with `parse_ok = FALSE`), so no additional handling is needed.

A defensive `byte_size` guard is also worth adding: cap stored name fields at a
sane length (e.g. 1 KB) so a megabyte-long "name" cannot bloat the table or the
admin JSON payloads. Truncation is recorded in `raw_content`, which keeps the
full original.

### 2A.2 Live vulnerability: stored XSS in the admin UI

This must be fixed as part of this work, because the whole point of the change is
to route more user-controlled names into more admin pages.

[`admin/assets/app.js:142`](../admin/assets/app.js:142) interpolates the name
directly into `innerHTML`:

```js
tbody.innerHTML = d.top_pubkeys.map((p, i) =>
    `<tr><td>${i+1}</td><td>${p.name || '<i>unknown</i>'}</td>...`
```

and [`admin/assets/app.js:411`](../admin/assets/app.js:411) does the same for the
caching-follows table. A user who sets their kind-0 `name` to
`<img src=x onerror="...">` achieves **script execution in the relay
administrator's authenticated browser session** merely by posting enough events
to appear in the top-pubkeys list. No privileged access is required.

The codebase is already inconsistent about this: the header name at
[`app.js:652`](../admin/assets/app.js:652) correctly uses `textContent` and is
safe. The table renderers are not.

**Fix:** add an escaping helper and apply it to every interpolated
user-controlled value in `innerHTML` template strings:

```js
const esc = (s) => String(s ?? '').replace(/[&<>"']/g,
    c => ({'&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;',"'":'&#39;'}[c]));
```

Auditing the surrounding rows shows the same pattern applied to other
user-controlled fields — event `content` ([`app.js:380`](../admin/assets/app.js:380)),
DM content ([`app.js:441`](../admin/assets/app.js:441)), config values
([`app.js:219`](../admin/assets/app.js:219)), and auth-rule `pattern_value`
([`app.js:251`](../admin/assets/app.js:251)) — so the sweep should cover all of
them, not just names. Preferring `textContent` / `createElement` over `innerHTML`
in these renderers is the more durable fix where it is not too invasive.

Note the PHP side is already correct: [`e()`](../admin/lib/helpers.php:9) wraps
`htmlspecialchars(..., ENT_QUOTES, 'UTF-8')` and is used for server-rendered
output. The gap is purely in the JS-built tables.

### 2A.3 Presentation-layer nuisances (frontend, cosmetic)

These stay unsanitized in the database and are handled with CSS/formatting:

| Issue | Effect | Mitigation |
|---|---|---|
| Bidi overrides (`U+202E` RTL) | Reverses surrounding text, spoofs other names | Render names in a `<bdi>` element — purpose-built for exactly this, isolates bidi without altering the value |
| Zalgo / stacked combining marks | Vertical overflow past row bounds | `overflow: hidden` + fixed line-height on the name cell |
| Zero-width chars (`U+200B`, `U+FEFF`) | Invisible; two names look identical | Optional: reveal-on-hover indicator; do not strip |
| Newlines / tabs | Break single-line table layout | CSS `white-space: nowrap` + `text-overflow: ellipsis` |
| Very long names | Blow out column width | CSS `max-width` + ellipsis (value stays intact in a `title` tooltip) |
| Emoji / astral-plane chars | None — legitimate usage | Nothing; ensure JS length math uses code points, not UTF-16 units, when truncating |

Truncation in JS deserves care: `substring()` on a UTF-16 string can split a
surrogate pair and emit a replacement glyph. Use `Array.from(str).slice(0, n)` or
CSS-based ellipsis (preferred — no string surgery at all).

### 2A.4 Terminal/log safety

Names flow into `DEBUG_*` output. ANSI escape sequences in a name can manipulate
a maintainer's terminal. Log rendering should escape non-printable bytes, or
simply avoid logging profile names at all — the pubkey is the useful identifier
in logs anyway.

### 2.9 Which field do people actually use?

Storing both fields turns this into an empirical question rather than a guess.
Once the table is populated, one aggregate query answers it against real data
from your relay's own corpus:

```sql
SELECT count(*) FILTER (WHERE name <> '' AND display_name <> '')  AS both,
       count(*) FILTER (WHERE name <> '' AND display_name =  '')  AS name_only,
       count(*) FILTER (WHERE name =  '' AND display_name <> '')  AS display_only,
       count(*) FILTER (WHERE name =  '' AND display_name =  '')  AS neither,
       count(*) FILTER (WHERE name <> '' AND display_name <> ''
                          AND name <> display_name)               AS both_differ,
       count(*) AS total
  FROM profiles;
```

`both_differ` is the number that matters: it counts profiles where the preference
setting actually changes what gets rendered. If it is near zero, the setting is
academic and either default is fine. If it is large, the setting earns its keep.

Worth surfacing as a small panel on the admin stats page — it is one cheap
aggregate over a table with one row per pubkey, and it makes
`profile_name_preference` self-documenting: you can see the impact of the choice
before making it. Add it once the table has accumulated real data.

### 2.8 Verification

- Fresh database: relay starts, `profiles` exists, posting a kind-0 populates
  exactly one row with `name` and `display_name` both preserved verbatim.
- Upgrade path: start against a database with pre-existing kind-0 events, confirm
  the backfill fills every row once and does **not** re-run on the next restart.
- Replaceable-update: publish a newer kind-0, confirm the row updates; replay an
  older one, confirm the row does **not** regress.
- Malformed content: store a kind-0 whose content is not JSON; confirm the insert
  still succeeds, `parse_ok = FALSE`, and the admin pages render without error.
- **NUL byte:** publish a kind-0 whose `name` contains `\u0000`; confirm the
  event is still accepted, the profile row is created, and the relay does not
  error. This is the regression test for the ingest-DoS path in §2A.1.
- **XSS:** publish a kind-0 with `name` set to
  `<img src=x onerror="window.__xss=1">`, load the stats and caching pages, and
  confirm the markup is rendered as visible text and `window.__xss` is
  undefined.
- **Bidi/Zalgo:** publish names containing `U+202E` and stacked combining marks;
  confirm table layout and neighbouring rows are unaffected.
- Consistency: the same pubkey shows an identical name in the stats table, the
  caching follows table, and the header.
- Query-count check: confirm the top-pubkeys API response issues one profile
  query rather than ten.
- Both-fields check: query `profiles` for a pubkey whose kind-0 sets `name` and
  `display_name` to different values; confirm both are stored distinctly.

---

## 3. Phase 2 — Image Caching (design only, deferred)

Recorded here so Phase 1's schema does not need reworking later.

### 3.1 Motivation

Today the admin UI hotlinks `picture` URLs directly
([`admin/assets/app.js:653`](../admin/assets/app.js:653)). Problems: the admin's
browser reveals its IP to arbitrary third-party hosts on every page load; dead
or slow hosts degrade the UI; images can be arbitrarily large; there is no way to
show avatars offline.

### 3.2 Proposed schema

```sql
CREATE TABLE IF NOT EXISTS profile_images (
    pubkey        TEXT PRIMARY KEY,
    source_url    TEXT NOT NULL,
    mime_type     TEXT NOT NULL DEFAULT '',
    byte_size     INTEGER NOT NULL DEFAULT 0,
    sha256        TEXT NOT NULL DEFAULT '',
    etag          TEXT NOT NULL DEFAULT '',
    image_data    BYTEA,
    fetch_state   TEXT NOT NULL DEFAULT 'pending',
    fetch_attempts INTEGER NOT NULL DEFAULT 0,
    last_error    TEXT,
    fetched_at    BIGINT NOT NULL DEFAULT 0,
    updated_at    BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,
    CHECK (fetch_state IN ('pending','ok','failed','skipped','too_large'))
);
CREATE INDEX IF NOT EXISTS idx_profile_images_pending
    ON profile_images(fetch_state, fetch_attempts) WHERE fetch_state = 'pending';
```

`BYTEA` in PostgreSQL rather than the filesystem keeps backup/restore and the
container story single-artifact, matching how everything else in this project is
stored. Avatars are small; a cap keeps total size bounded.

### 3.3 Fetch worker

`libcurl` is already linked ([`Makefile:6`](../Makefile:6)) but currently unused
in `src/`. A worker modeled on
[`caching_inbox_poller.c`](../src/caching_inbox_poller.c) — two-state
idle/active polling, config-gated, off the main libwebsockets thread — would:

1. Enqueue `pending` rows when `profiles.picture` changes (trigger or poll).
2. Fetch with a hard timeout, a max-bytes ceiling (~256 KB), redirect limit,
   and `Content-Type` allow-list (`image/png|jpeg|webp|gif`).
3. Send `If-None-Match` on refresh, honour `304`.
4. Exponential backoff, capped `fetch_attempts`, terminal `failed`.

New config keys following existing naming: `profile_image_cache_enabled`
(default **off**), `profile_image_max_bytes`, `profile_image_refresh_days`,
`profile_image_fetch_concurrency`.

### 3.4 Serving

A relay HTTP route `/avatar/<pubkey>` handled in
[`handle_embedded_file_request()`](../src/api.c:1006) (called from
[`src/websockets.c:1261`](../src/websockets.c:1261)), returning the bytes with a
long `Cache-Control` and an `ETag`, falling back to a generated identicon or
`404` when uncached. The UI then only ever loads images from the relay's own
origin.

### 3.5 Risks to weigh before committing

- **Outbound HTTP from the relay** is a new capability and a real SSRF surface —
  needs a private-IP/localhost block-list and scheme restriction. This is the
  main reason to keep it default-off and deferred.
- Database growth: bounded by `max_bytes × profile count`; needs a documented
  ceiling and a prune path.
- Content risk: the relay would be re-serving arbitrary third-party bytes under
  its own origin. Strict `Content-Type` enforcement plus
  `Content-Security-Policy` / `X-Content-Type-Options: nosniff` on the route.

---

## 4. Files Touched (Phase 1)

| File | Change |
|---|---|
| [`src/pg_schema.sql`](../src/pg_schema.sql) | `profiles` table (both name fields, no generated column), `safe_jsonb()`, `sanitize_pg_text()`, sync + delete triggers, guarded backfill, `profile_name_preference` config default, version → 6 |
| [`src/pg_schema.h`](../src/pg_schema.h) | Mirror the above as C string literals; bump `EMBEDDED_PG_SCHEMA_VERSION` |
| [`src/db_ops.h`](../src/db_ops.h) | Declare `db_get_profile()` / `db_get_profiles()` |
| [`src/db_ops_postgres.h`](../src/db_ops_postgres.h) | Declare the postgres implementations |
| [`src/db_ops_postgres.c`](../src/db_ops_postgres.c) | Implement both against `profiles`; retire the events-table query |
| [`src/db_ops.c`](../src/db_ops.c) | Dispatch entries + SQLite stubs |
| [`src/config.h`](../src/config.h) / [`src/config.c`](../src/config.c) | `profile_display_name()` resolver; batch profile lookup in the caching follows loop |
| [`src/api.c`](../src/api.c) | Batch both top-pubkeys loops; shared attach helper emitting `name` + `display_name` + `best_name` |
| [`admin/lib/helpers.php`](../admin/lib/helpers.php) | `profile_map()` + `profile_display_name()` |
| [`admin/api/stats.php`](../admin/api/stats.php) | Use `profile_map()`; drop LATERAL + JSON casts |
| [`admin/api/caching.php`](../admin/api/caching.php) | Join `profiles`; drop LATERAL + JSON casts |
| `admin/api/profile.php` | New: single-profile lookup endpoint |
| [`admin/assets/app.js`](../admin/assets/app.js) | **`esc()` helper + XSS sweep of all `innerHTML` renderers (§2A.2)**; local-first profile load; consume server `best_name` |
| [`admin/assets/index.css`](../admin/assets/index.css) | `nowrap` / `overflow` / `max-width` + ellipsis on name cells (§2A.3) |
| `tests/` | New script covering populate / upgrade / replace / malformed / NUL / XSS / bidi cases |

---

## 5. Sequencing

```mermaid
graph TD
    A[Add profiles table + safe_jsonb + sanitize_pg_text + triggers to pg_schema.sql] --> B[Mirror into pg_schema.h and bump version to 6]
    B --> C[Guarded one-time backfill + profile_name_preference config default]
    C --> D[Implement db_get_profile and db_get_profiles]
    D --> E[Migrate api.c and config.c to batch lookups]
    E --> F[Add profile_map helper and migrate PHP endpoints]
    F --> X[Fix stored XSS: esc helper and innerHTML sweep in app.js]
    X --> G[Add profile.php endpoint and update app.js profile load]
    G --> Y[CSS hardening for hostile name rendering]
    Y --> Z[Add name-field usage panel to stats page]
    G --> H[Tests: populate, upgrade, replace, malformed, consistency]
    H --> I[Phase 2 image caching - deferred]
```