clickhouse-nip33-addressable-dedup

ClickHouse NIP-33 Addressable Event Deduplication

Problem

When storing Nostr events in ClickHouse using ReplacingMergeTree, edited addressable events (Kind 30000-39999) appear as duplicates. Users edit their video/event, a new event ID is created with the same d_tag, but both versions are shown instead of just the latest.

Context / Trigger Conditions

• Nostr relay storing events in ClickHouse with ReplacingMergeTree
• Users report seeing duplicate videos/events after editing
• Query returns multiple events with same (pubkey, kind, d_tag) but different id values
• Using FINAL keyword but duplicates still appear
• Kind 30000+ events (NIP-33 parameterized replaceable events like Kind 34236 videos)

Root Cause

The FINAL keyword in ClickHouse deduplicates based on the table's ORDER BY key. If your table is defined as:

ENGINE = ReplacingMergeTree(indexed_at)
ORDER BY (id)

Then FINAL deduplicates by id. Two events with different IDs are NOT considered duplicates, even if they represent the same addressable "slot" per NIP-33.

For NIP-33 addressable events, the replacement key should be (pubkey, kind, d_tag), not id.

Solution

Option 1: Fix at View Level (Recommended)

Change your videos view to use LIMIT 1 BY instead of FINAL:

CREATE VIEW videos AS
SELECT
    id,
    pubkey,
    created_at,
    kind,
    content,
    tags,
    d_tag,
    title,
    thumbnail,
    video_url
FROM events_local
WHERE kind IN (34235, 34236)
ORDER BY pubkey, kind, d_tag, created_at DESC
LIMIT 1 BY pubkey, kind, d_tag;

The LIMIT 1 BY clause keeps only the first row (latest by created_at) for each unique combination of (pubkey, kind, d_tag).

Option 2: Fix at Table Level (Breaking Change)

If you can recreate the table, use a composite ORDER BY:

CREATE TABLE events_addressable (
    ...
) ENGINE = ReplacingMergeTree(created_at)
ORDER BY (pubkey, kind, d_tag);

This makes FINAL work correctly for NIP-33 events but may not work for all event types.