Overview
The backend currently uses Redis as its only storage layer. Redis is appropriate for caching and ephemeral state but not for durable storage of airdrops, recipients, webhook endpoints, and delivery logs. A PostgreSQL layer is needed for the indexer and webhook system.
Schema (initial)
-- Airdrop campaigns
CREATE TABLE airdrops (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
contract_id TEXT NOT NULL,
creator TEXT NOT NULL,
token TEXT NOT NULL,
total_amount BIGINT NOT NULL,
expiry_ledger BIGINT NOT NULL,
status TEXT NOT NULL DEFAULT 'active',
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Individual recipients
CREATE TABLE recipients (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
airdrop_id UUID REFERENCES airdrops(id),
address TEXT NOT NULL,
amount BIGINT NOT NULL,
claimed_at TIMESTAMPTZ,
ledger BIGINT
);
-- Raw contract events
CREATE TABLE contract_events (
id BIGSERIAL PRIMARY KEY,
ledger BIGINT NOT NULL,
tx_hash TEXT NOT NULL,
event_type TEXT NOT NULL,
payload JSONB NOT NULL,
indexed_at TIMESTAMPTZ DEFAULT NOW()
);
-- Indexer cursor
CREATE TABLE indexer_state (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
);
Implementation
- Install
pg and knex (query builder + migrations)
- Create
src/db/index.js with a connection pool
- Create
src/db/migrations/ directory with numbered migrations
- Provide
npm run migrate and npm run migrate:rollback scripts
- Add
DATABASE_URL to config.js and .env.example
Acceptance Criteria
Overview
The backend currently uses Redis as its only storage layer. Redis is appropriate for caching and ephemeral state but not for durable storage of airdrops, recipients, webhook endpoints, and delivery logs. A PostgreSQL layer is needed for the indexer and webhook system.
Schema (initial)
Implementation
pgandknex(query builder + migrations)src/db/index.jswith a connection poolsrc/db/migrations/directory with numbered migrationsnpm run migrateandnpm run migrate:rollbackscriptsDATABASE_URLtoconfig.jsand.env.exampleAcceptance Criteria
pgandknexinstalledmax: 10connectionsnpm run migrateruns cleanly on fresh databasesrc/db/index.jsexports typed query helpers.env.exampleupdated withDATABASE_URLpostgresservice