⚡Events

For more complex applications that need to efficiently display linked addresses in a list view, you will want to process the events in a background job. If you save these events into an SQL database, you can then write query similar to the below. Note you will still need to validate `isValidName` in your frontend.

Database Migrations:

-- +goose Up
create table name_created
(
    id          text        NOT NULL PRIMARY KEY,
    tx_hash     text        NOT NULL,
    nft_index   int4        NOT NULL,
    "name"      text        NOT NULL,
    capitalisation     text        NOT NULL,
    creator     text        NOT NULL,
    expires     int4        NOT NULL,
    timestamp   int4        NOT NULL,
    created     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX name_created_nft_index_index ON name_created (nft_index);
CREATE INDEX name_created_name_index ON name_created ("name");
CREATE INDEX name_created_creator_index ON name_created (creator);

create table name_renewed
(
    id          text        NOT NULL PRIMARY KEY,
    tx_hash     text        NOT NULL,
    nft_index   int4        NOT NULL,
    "name"      text        NOT NULL,
    renewer     text        NOT NULL,
    expires     int4        NOT NULL,
    timestamp   int4        NOT NULL,
    created     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX name_renewed_nft_index_index ON name_renewed (nft_index);
CREATE INDEX name_renewed_name_indexx ON name_renewed ("name");
CREATE INDEX name_renewed_renewer_index ON name_renewed (renewer);

create table address_set
(
    id          text        NOT NULL PRIMARY KEY,
    tx_hash     text        NOT NULL,
    nft_index   int4        NOT NULL,
    "name"      text        NOT NULL,
    new_address text        NOT NULL,
    timestamp   int4        NOT NULL,
    created     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX address_set_nft_index_index ON address_set (nft_index);
CREATE INDEX address_set_name_index ON address_set ("name");
CREATE INDEX address_set_new_address_index ON address_set (new_address);


create table capitalisation_set
(
    id          text        NOT NULL PRIMARY KEY,
    tx_hash     text        NOT NULL,
    nft_index   int4        NOT NULL,
    "name"      text        NOT NULL,
    new_capitalisation text        NOT NULL,
    timestamp   int4        NOT NULL,
    created     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX capitalisation_set_nft_index_index ON capitalisation_set (nft_index);
CREATE INDEX capitalisation_set_name_index ON capitalisation_set ("name");
CREATE INDEX capitalisation_set_new_capitalisation_index ON capitalisation_set (new_capitalisation);


create table name_deleted
(
    id          text        NOT NULL PRIMARY KEY,
    tx_hash     text        NOT NULL,
    nft_index   int4        NOT NULL,
    "name"      text        NOT NULL,
    deleter     text        NOT NULL,
    timestamp   int4        NOT NULL,
    created     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX name_deleted_nft_index_index ON name_deleted (nft_index);
CREATE INDEX name_deleted_name_index ON name_deleted ("name");
CREATE INDEX name_deleted_deleter_index ON name_deleted (deleter);


create table reverse_address_set
(
    id          text        NOT NULL PRIMARY KEY,
    tx_hash     text        NOT NULL,
    "address"   text        NOT NULL,
    new_name    text        NOT NULL,
    timestamp   int4        NOT NULL,
    created     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX reverse_address_set_address_index ON reverse_address_set ("address");
CREATE INDEX reverse_address_set_new_name_index ON reverse_address_set (new_name);


create table reverse_address_deleted
(
    id          text        NOT NULL PRIMARY KEY,
    tx_hash     text        NOT NULL,
    "address"   text        NOT NULL,
    "name"      text        NOT NULL,
    timestamp   int4        NOT NULL,
    created     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX reverse_address_deleted_address_index ON reverse_address_deleted ("address");
CREATE INDEX reverse_address_deleted_name_index ON reverse_address_deleted ("name");


-- +goose Down
drop table name_created;
drop table order_cancelled;
drop table address_set;
drop table capitalisation_set;
drop table name_deleted;
drop table reverse_address_set;
drop table reverse_address_deleted;

Query for bulk fetching names by address, just pass in an array of address strings:

WITH 
lastest_name_created AS (
	SELECT DISTINCT ON ("name") A."name", A.creator, A.capitalisation, A.expires, A."timestamp"
	FROM name_created A
	ORDER BY A."name", A."timestamp" DESC
),
lastest_address_set AS (
	SELECT DISTINCT ON ("name") A."name", A.new_address
	FROM address_set A
	ORDER BY A."name", A."timestamp" DESC
),
lastest_capitalisation_set AS (
	SELECT DISTINCT ON ("name") A."name", A.new_capitalisation
	FROM capitalisation_set A
	ORDER BY A."name", A."timestamp" DESC
),
lastest_name_renewed AS (
	SELECT DISTINCT ON ("name") A."name", A.expires
	FROM name_renewed A
	ORDER BY A."name", A."timestamp" DESC
),
lastest_name_deleted AS (
	SELECT DISTINCT ON ("name") A."name", A."timestamp"
	FROM name_deleted A
	ORDER BY A."name", A."timestamp" DESC
),
lastest_reverse_address_set AS (
	SELECT DISTINCT ON (address) A.address, A.new_name, A."timestamp"
	FROM reverse_address_set A
	order by A.address, A."timestamp" DESC
),
lastest_reverse_address_deleted AS (
	SELECT DISTINCT ON (address) A.address, A."timestamp"
	FROM reverse_address_deleted A
	order by A.address, A."timestamp" DESC
)
SELECT 
	LNC."name",
	COALESCE(LAS.new_address, LNC.creator) AS address,
	COALESCE(LCS.new_capitalisation, LNC.capitalisation) AS capitalisation,
	COALESCE(LNR.expires, LNC.expires) AS expires
FROM lastest_name_created LNC
LEFT OUTER JOIN lastest_address_set LAS
ON LNC."name" = LAS."name"
LEFT OUTER JOIN lastest_capitalisation_set LCS
ON LNC."name" = LCS."name"
LEFT OUTER JOIN lastest_name_renewed LNR
ON LNC."name" = LNR."name"
LEFT OUTER JOIN lastest_name_deleted LND
ON LNC."name" = LND."name"
LEFT OUTER JOIN lastest_reverse_address_set LRAS
ON LNC."name" = LRAS.new_name
LEFT OUTER JOIN lastest_reverse_address_deleted LRAD
ON LRAS.address = LRAD.address
WHERE 
	COALESCE(LNR.expires, LNC.expires) > extract(epoch FROM now())
	AND (LND."timestamp" IS NULL OR LND."timestamp" < LNC."timestamp")
	AND (LRAD."timestamp" IS NULL OR LRAD."timestamp" < LRAS."timestamp")
	AND LRAS."address" = COALESCE(LAS.new_address, LNC.creator)
	AND COALESCE(LAS.new_address, LNC.creator) = ANY ($1::varchar[])