c-relay-pg/plans/status_publish_plan.md

Plan: Publish Kind 1 Status Events to External Relays

Config Reuse

The config key kind_1_status_posts_hours already exists in the shared config table (used by the main relay at src/api.c:975). The caching service will read the same key — no new config needed. Setting it in the admin panel controls both:

• Main relay: generates and stores the kind 1 event locally
• Caching service: publishes it to external upstream relays

Phase 1: PHP Report Endpoint (admin/api/kind_1_report.php)

A new PHP endpoint at /relay/admin/api/kind_1_report.php that returns a markdown-formatted relay status report. This serves as:

• The content template for the kind 1 event
• A preview page you can visit in the browser to see what will be published

Report Structure (Markdown)

# Relay Name v0.x.x

## Event Rate (Last Hour)
[ASCII chart — same as the 1H chart from chart.php]

## Database Overview
- Total Events: X
- Database Size: X MB
- Oldest Event: date
- Newest Event: date

## Event Kinds (Top 10)
| Kind | Count | % |
|------|-------|---|
| 1    | X     | X% |
| 7    | X     | X% |
| ...  | ...   | ... |

## Time-Based Statistics
| Period   | Events |
|----------|--------|
| 24 Hours | X      |
| 7 Days   | X      |
| 30 Days  | X      |

## Top Pubkeys
| # | Name | Pubkey | Events | % |
|---|------|--------|--------|---|
| 1 | ...  | ...    | X      | X%|
| 2 | ...  | ...    | X      | X%|

Implementation

The PHP file will:

1. Query PostgreSQL for all stats (same queries as admin/api/stats.php)
2. Call the existing chart endpoint to get the 1H ASCII chart
3. Format everything as markdown
4. Return Content-Type: text/plain; charset=utf-8

Nginx Config

Add a location block so the endpoint is accessible:

location ^~ /relay/admin/api/kind_1_report.php {
    alias /opt/c-relay-pg/admin/api/kind_1_report.php;
    fastcgi_pass unix:/run/php/php8.3-fpm.sock;
    fastcgi_index kind_1_report.php;
    include fastcgi_params;
    fastcgi_param SCRIPT_FILENAME $request_filename;
}

Phase 2: Caching Service Publishing

2a. pg_inbox_get_relay_private_key() (new function in pg_inbox.c)

Reads the relay's private key from the relay_seckey table. Returns a malloc'd hex string.

2b. Status publish tick (new function in main.c)

A periodic task in the main loop that:

• Reads kind_1_status_posts_hours from the config table
• If enabled and time to publish:
  1. Fetches the relay private key from relay_seckey
  2. Fetches the report text by calling the PHP endpoint via HTTP (or generates it directly via SQL)
  3. Creates and signs a kind 1 event using nostr_create_and_sign_event()
  4. Publishes to all connected upstream relays via nostr_relay_pool_publish_async()

Files to Modify

File	Change
admin/api/kind_1_report.php	New — PHP endpoint returning markdown report
caching/src/pg_inbox.h	Add pg_inbox_get_relay_private_key() declaration
caching/src/pg_inbox.c	Implement pg_inbox_get_relay_private_key()
caching/src/main.c	Add periodic status publish tick
nginx config	Add location for kind_1_report.php

Current State

The main relay (c_relay_pg) already has a generate_and_post_status_event() function that:

1. Generates relay statistics text via generate_stats_text()
2. Signs it as a kind 1 event with the relay's private key (from relay_seckey table)
3. Stores it locally and broadcasts to connected clients

But it never publishes to external relays — the main relay has no WebSocket client capability.

The caching service (caching_relay) has a full WebSocket relay pool (nostr_relay_pool_t) that can connect to external relays and publish events. It already connects to 26 upstream relays.

Architecture

┌─────────────────────┐     ┌──────────────────────────────┐
│  PHP Stats Endpoint │────▶│  PostgreSQL (stats data)     │
│  /relay/api/stats/  │     │                              │
└─────────────────────┘     └──────────┬───────────────────┘
                                       │ reads every N hours
                                       ▼
┌──────────────────────────────────────────────────────────┐
│  caching_relay (main loop)                                │
│                                                           │
│  1. Read relay private key from relay_seckey table        │
│  2. Generate stats text (SQL queries via pg_inbox)        │
│  3. Create + sign kind 1 event                            │
│  4. Publish to ALL connected upstream relays              │
│     (via nostr_relay_pool_publish_async)                  │
└──────────────────────────────────────────────────────────┘

Changes Required

1. PHP Stats Text Endpoint (new file: admin/api/stats_text.php)

A public (or admin-authenticated) PHP endpoint that queries PostgreSQL directly and returns the stats text. This is useful for:

• Manual preview of what the relay publishes
• The caching service could fetch it via HTTP (but we'll use direct SQL instead)

2. Caching Service: pg_inbox_generate_stats_text() (new function in pg_inbox.c)

A C function that queries PostgreSQL to generate the same stats text that the main relay's generate_stats_text() produces. It queries:

• SELECT COUNT(*) FROM events — total events
• SELECT COUNT(*) FROM events WHERE created_at > ... — time-based stats
• SELECT kind, COUNT(*) FROM events GROUP BY kind — kind distribution
• SELECT pubkey, COUNT(*) FROM events GROUP BY pubkey ORDER BY COUNT(*) DESC LIMIT 10 — top pubkeys
• SELECT pg_database_size('crelay') — database size

3. Caching Service: pg_inbox_get_relay_private_key() (new function in pg_inbox.c)

Reads the relay's private key from the relay_seckey table so the caching service can sign events as the relay.

4. Caching Service: Status publish tick (new function in main.c)

A periodic task in the main loop that:

• Checks if it's time to publish (configurable interval, e.g., every 6 hours)
• Reads the relay private key from PostgreSQL
• Generates stats text via SQL queries
• Creates and signs a kind 1 event using nostr_create_and_sign_event()
• Publishes to all connected upstream relays via nostr_relay_pool_publish_async()

5. Config: status_publish_hours (new config key)

Controls how often the status event is published. Stored in the config table. Default 0 = disabled.

Files to Modify

File	Change
admin/api/stats_text.php	New — PHP endpoint returning stats text
caching/src/pg_inbox.h	Add declarations for pg_inbox_generate_stats_text() and pg_inbox_get_relay_private_key()
caching/src/pg_inbox.c	Implement both functions
caching/src/main.c	Add periodic status publish tick in main loop
caching/src/pg_config.c	Add status_publish_hours to config loading

Data Flow

Every N hours (config: status_publish_hours):
  1. main.c checks if time to publish
  2. Calls pg_inbox_generate_stats_text() → returns malloc'd string
  3. Calls pg_inbox_get_relay_private_key() → returns hex key
  4. Creates cJSON event: kind=1, content=stats_text, tags=[]
  5. Signs with nostr_create_and_sign_event(1, content, tags, privkey, now)
  6. Publishes via nostr_relay_pool_publish_async() to all upstream relays
  7. Free resources

Dependencies

• The caching service already links nostr_core_lib which provides nostr_create_and_sign_event() and nostr_relay_pool_publish_async()
• The caching service already has a PostgreSQL connection via pg_inbox
• No new libraries needed