Algorand indexer v3 vanilla SQL schema

CREATE TABLE IF NOT EXISTS block_header (
  round bigint PRIMARY KEY,
  realtime timestamp without time zone NOT NULL,
  rewardslevel bigint NOT NULL,
  header jsonb NOT NULL
);

CREATE TABLE IF NOT EXISTS txn (
  round bigint NOT NULL,
  intra integer NOT NULL,
  typeenum smallint NOT NULL,
  asset bigint NOT NULL, -- 0=Algos, otherwise AssetIndex
  txid bytea, -- base32 of [32]byte hash, or NULL for inner transactions.
  txn jsonb NOT NULL, -- json encoding of signed txn with apply data; inner txns exclude nested inner txns
  extra jsonb NOT NULL,
  PRIMARY KEY ( round, intra )
);


-- Optional, to make txn queries by asset fast:
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS txn_asset ON txn (asset, round, intra);

CREATE TABLE IF NOT EXISTS txn_participation (
  addr bytea NOT NULL,
  round bigint NOT NULL,
  intra integer NOT NULL
);

-- expand data.basics.AccountData
CREATE TABLE IF NOT EXISTS account (
  addr bytea primary key,
  microalgos bigint NOT NULL, -- okay because less than 2^54 Algos
  rewardsbase bigint NOT NULL,
  rewards_total bigint NOT NULL,
  deleted bool NOT NULL, -- whether or not it is currently deleted
  created_at bigint NOT NULL, -- round that the account is first used
  closed_at bigint, -- round that the account was last closed
  keytype varchar(8), -- "sig", "msig", "lsig", or NULL if unknown
  account_data jsonb NOT NULL -- trimmed AccountData that excludes the fields above and the four creatable maps; SQL 'NOT NULL' is held though the json string will be "null" iff account is deleted
);

-- data.basics.AccountData Assets[asset id] AssetHolding{}
CREATE TABLE IF NOT EXISTS account_asset (
  addr bytea NOT NULL, -- [32]byte
  assetid bigint NOT NULL,
  amount numeric(20) NOT NULL, -- need the full 18446744073709551615
  frozen boolean NOT NULL,
  deleted bool NOT NULL, -- whether or not it is currently deleted
  created_at bigint NOT NULL, -- round that the asset was added to an account
  closed_at bigint, -- round that the asset was last removed from the account
  PRIMARY KEY (addr, assetid)
);


-- Optional, to make queries of all asset balances fast /v2/assets/<assetid>/balances
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS account_asset_asset ON account_asset (assetid, addr ASC);

-- data.basics.AccountData AssetParams[index] AssetParams{}
CREATE TABLE IF NOT EXISTS asset (
  index bigint PRIMARY KEY,
  creator_addr bytea NOT NULL,
  params jsonb NOT NULL, -- data.basics.AssetParams; json string "null" iff asset is deleted
  deleted bool NOT NULL, -- whether or not it is currently deleted
  created_at bigint NOT NULL, -- round that the asset was created
  closed_at bigint -- round that the asset was closed; cannot be recreated because the index is unique
);

-- Includes indexer import state, migration state, special accounts (fee sink and
-- rewards pool) and account totals.
CREATE TABLE IF NOT EXISTS metastate (
  k text primary key,
  v jsonb
);

-- per app global state
-- roughly go-algorand/data/basics/userBalance.go AppParams
CREATE TABLE IF NOT EXISTS app (
  index bigint PRIMARY KEY,
  creator bytea NOT NULL, -- account address
  params jsonb NOT NULL, -- json string "null" iff app is deleted
  deleted bool NOT NULL, -- whether or not it is currently deleted
  created_at bigint NOT NULL, -- round that the asset was created
  closed_at bigint -- round that the app was deleted; cannot be recreated because the index is unique
);

-- per-account app local state
CREATE TABLE IF NOT EXISTS account_app (
  addr bytea,
  app bigint,
  localstate jsonb NOT NULL, -- json string "null" iff deleted from the account
  deleted bool NOT NULL, -- whether or not it is currently deleted
  created_at bigint NOT NULL, -- round that the app was added to an account
  closed_at bigint, -- round that the account_app was last removed from the account
  PRIMARY KEY (addr, app)
);