ClickHouse NIP-33 Déduplication d'événements adressables

Problème

Lors du stockage d'événements Nostr dans ClickHouse en utilisant ReplacingMergeTree, les événements adressables modifiés (Kind 30000-39999) apparaissent comme des doublons. Les utilisateurs modifient leur vidéo/événement, un nouvel ID d'événement est créé avec le même d_tag, mais les deux versions s'affichent au lieu de seulement la dernière.

Contexte / Conditions de déclenchement

Relay Nostr stockant des événements dans ClickHouse avec ReplacingMergeTree
Les utilisateurs signalent des vidéos/événements dupliqués après modification
La requête retourne plusieurs événements avec le même (pubkey, kind, d_tag) mais des valeurs id différentes
Utilisation du mot-clé FINAL mais les doublons apparaissent toujours
Événements Kind 30000+ (événements remplaçables paramétrés NIP-33 comme les vidéos Kind 34236)

Cause racine

Le mot-clé FINAL dans ClickHouse déduplique selon la clé ORDER BY de la table. Si votre table est définie comme :

ENGINE = ReplacingMergeTree(indexed_at)
ORDER BY (id)

Alors FINAL déduplique par id. Deux événements avec des IDs différents ne sont PAS considérés comme des doublons, même s'ils représentent le même « slot » adressable selon NIP-33.

Pour les événements adressables NIP-33, la clé de remplacement devrait être (pubkey, kind, d_tag), non id.

Solution

Option 1 : Correction au niveau de la vue (Recommandée)

Changez votre vue videos pour utiliser LIMIT 1 BY au lieu de 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;

La clause LIMIT 1 BY garde uniquement la première ligne (la plus récente par created_at) pour chaque combinaison unique de (pubkey, kind, d_tag).

Option 2 : Correction au niveau de la table (Changement incompatible)

Si vous pouvez recréer la table, utilisez une clause ORDER BY composite :

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

Cela fait fonctionner correctement FINAL pour les événements NIP-33 mais peut ne pas fonctionner pour tous les types d'événements.

Exemple de migration

-- Supprimez d'abord les vues dépendantes
DROP VIEW IF EXISTS trending_videos;
DROP VIEW IF EXISTS video_stats;
DROP VIEW IF EXISTS videos;

-- Recréez avec une déduplication correcte
CREATE VIEW videos AS
SELECT *
FROM events_local
WHERE kind IN (34235, 34236)
ORDER BY pubkey, kind, d_tag, created_at DESC
LIMIT 1 BY pubkey, kind, d_tag;

-- Recréez les vues dépendantes...

Vérification

Interrogez les vidéos d'un utilisateur spécifique et confirmez qu'il n'y a pas de doublons :

SELECT id, d_tag, created_at
FROM videos
WHERE pubkey = 'user_pubkey_here'
ORDER BY created_at DESC;

Chaque d_tag devrait apparaître une seule fois, avec la valeur created_at la plus élevée.

Exemple

Avant (cassé) :

| id       | d_tag    | created_at |
|----------|----------|------------|
| abc123   | video1   | 1769697188 | ← Édition plus récente
| def456   | video1   | 1769697150 | ← Original (devrait être masqué)

Après (corrigé) :

| id       | d_tag    | created_at |
|----------|----------|------------|
| abc123   | video1   | 1769697188 | ← Seule la dernière s'affiche

Notes

Cela s'applique à tous les événements adressables NIP-33 (Kind 30000-39999), pas seulement les vidéos
L'approche LIMIT 1 BY est une déduplication au moment de la requête, non au moment du stockage
Les anciennes versions d'événements restent en stockage mais n'apparaîtront pas dans les résultats des requêtes
Envisagez un nettoyage périodique des anciennes versions d'événements si le stockage est une préoccupation
N'oubliez pas de recréer les vues dépendantes dans le bon ordre

clickhouse-nip33-addressable-dedup