ETL to QE, Update 73, The SQL Schema was Still Over Engineered

Date: 2025-04-24

So here's my schema for Nostr events,

CREATE TABLE IF NOT EXISTS normalized_nostr_events_t (
    id VARCHAR NOT NULL UNIQUE,
    created_at INTEGER NOT NULL,
    kind integer NOT NULL,
    pubkey VARCHAR NOT NULL,
    sig VARCHAR,
    content VARCHAR,
    tags VARCHAR,
    raw_event VARCHAR,
    is_verified BOOLEAN
);

CREATE TABLE IF NOT EXISTS nostr_event_tags_t (
    id VARCHAR,
    first_tag VARCHAR,
    tags JSONB,
    CONSTRAINT fk_nostr_event_relay_metadata
        FOREIGN KEY (id)
        REFERENCES normalized_nostr_events_t (id)
        ON DELETE CASCADE,
    CONSTRAINT unique_nostr_event_tags UNIQUE (id, first_tag, tags)
);

And here is the trigger function used to insert the events,

CREATE OR REPLACE FUNCTION insert_nostr_event_tags()
RETURNS TRIGGER AS $$
DECLARE
    nested_tags JSONB;
    item JSONB;
    first_tag_extracted VARCHAR;
BEGIN
    -- Loop through the JSONB array 'tags' from the NEW record
    FOR item IN
        SELECT jsonb_array_elements(NEW.raw_event::jsonb->'tags') AS tag
    LOOP
        -- Check if the tag matches the pattern
        first_tag_extracted := item::jsonb->>0;
        IF first_tag_extracted ~ '^[A-Za-z]{1,2}$' THEN
            -- Insert into nostr_event_tags
            INSERT INTO nostr_event_tags_t (
                id,
                first_tag,
                tags
            ) VALUES (
                NEW.id,
                first_tag_extracted,  -- Insert the tag directly
                item::jsonb
            ) ON CONFLICT (id, first_tag, tags) DO NOTHING;
        ELSE
            -- Insert into non_standard_nostr_event_tags
            INSERT INTO non_standard_nostr_event_tags_t (
                id,
                first_tag,
                tags_hashed,
                tags
            ) VALUES (
                NEW.id,
                first_tag_extracted,  -- Insert the tag directly
                md5(item),
                item
            ) ON CONFLICT (id, first_tag, tags_hashed) DO NOTHING;
        END IF;
    END LOOP;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER run_insert_nostr_event_tags_on_normalized_nostr_events
AFTER INSERT ON normalized_nostr_events_t
FOR EACH ROW
EXECUTE PROCEDURE insert_nostr_event_tags();

So since the TRIGGER to intert date from normalized_nostr_events_t into nostr_event_tags_t function doesn't actually get triggered unless there is an insert the constraints don't actually matter.

Backlinks

• Project Update Posts