Events
Index ANS events into SQL for efficient list views.
For more complex applications that need to efficiently show linked addresses in a list view, you'll want to process ANS events in a background job. If you store those events in an SQL database, you can then query them directly.
You should still validate names with
isValidNamein your frontend — see Resolving a Name.
Database migrations
These tables capture the ANS event stream. The example uses goose migration annotations.
-- +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 int8 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 int8 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_index 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 name_renewed;
drop table address_set;
drop table capitalisation_set;
drop table name_deleted;
drop table reverse_address_set;
drop table reverse_address_deleted;
Bulk fetch names by address
Pass an array of address strings as $1. For each address it finds the latest state of every linked, unexpired, non‑deleted name — reducing the event log to a current view with DISTINCT ON + COALESCE.
WITH
latest_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
),
latest_address_set AS (
SELECT DISTINCT ON ("name") A."name", A.new_address
FROM address_set A
ORDER BY A."name", A."timestamp" DESC
),
latest_capitalisation_set AS (
SELECT DISTINCT ON ("name") A."name", A.new_capitalisation
FROM capitalisation_set A
ORDER BY A."name", A."timestamp" DESC
),
latest_name_renewed AS (
SELECT DISTINCT ON ("name") A."name", A.expires
FROM name_renewed A
ORDER BY A."name", A."timestamp" DESC
),
latest_name_deleted AS (
SELECT DISTINCT ON ("name") A."name", A."timestamp"
FROM name_deleted A
ORDER BY A."name", A."timestamp" DESC
),
latest_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
),
latest_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 latest_name_created LNC
LEFT OUTER JOIN latest_address_set LAS ON LNC."name" = LAS."name"
LEFT OUTER JOIN latest_capitalisation_set LCS ON LNC."name" = LCS."name"
LEFT OUTER JOIN latest_name_renewed LNR ON LNC."name" = LNR."name"
LEFT OUTER JOIN latest_name_deleted LND ON LNC."name" = LND."name"
LEFT OUTER JOIN latest_reverse_address_set LRAS ON LNC."name" = LRAS.new_name
LEFT OUTER JOIN latest_reverse_address_deleted LRAD ON LRAS.address = LRAD.address
WHERE
COALESCE(LNR.expires, LNC.expires) > (extract(epoch FROM now()) * 1000)::bigint
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[])