Dune Community Sources Schema

CommunityTableSchema.sql

-- Dune Query for relevant views (postgres): https://dune.com/queries/1582621

-- Below is a HiveSQL (pseudo code) schema with corresponding postgres types:

-- TABLE 1: Order Rewards
-- Update schedule: hourly
-- Size: Currently 20k records this table will have approximately the same dimensions as number of trades on cow_prococol.

-- The first three columns are on-chain verifyable, 
-- the json data field contains data from our orderbook DB.
-- but the values contained there are governed by our DAO. Specifically,
-- CIP-14: https://snapshot.org/#/cow.eth/proposal/0x3c84fc8e3cfe9cc6df76198d5031fae6580d8f9531f5b92ca3adedbc976cb1e5
order_rewards (
	order_uid      string,  -- bytea (bytes56)
	tx_hash        string,  -- bytea (bytes32)
	solver         string,  -- bytea (bytes20 - aka address)
	data           json
);

-- TABLE 2: Raw App Data
-- Update Schedule: Sparse. We check every 5 minutes, but on average new content rarely occurs).
-- Size: < 1Mb (after almost 2 years) currently only 2285 records.

-- The content of this table is fully decentralized and verifyable via IPFS,
-- The `hash`, as onchain data, can be used to fetch the immutable `content` from IPFS.
-- Some documentation on the process:
--	https://docs.cow.fi/front-end/creating-app-ids
--	https://docs.cow.fi/front-end/creating-app-ids/get-the-digest-hash-from-the-cid
-- Onchain:
--	Order: 
--		https://explorer.cow.fi/orders/0x6177de2ad9653585af1a2879ec611f2f03f7fd2e1c8c91c3a2ba18fdcbc005c78d99f8b2710e6a3b94d9bf465a98e5273069acbd627870b1
--	AppHash: 
--		0xe4d1ab10f2c9ffe7bdd23c315b03f18cff90888d6b2bb5022bacd46ab9cddf24
-- Invertible transformation:
--	Convert to CID (somehow): Qmdjs6Y7BvTKxwyPpJK7J34xESjZXiqMht2D2wUcbWvZcX
-- Go to IPFS for content:	
--	https://gnosis.mypinata.cloud/ipfs/Qmdjs6Y7BvTKxwyPpJK7J34xESjZXiqMht2D2wUcbWvZcX

raw_app_data (
	first_block_seen  long   NOT NULL,
	hash              string UNIQUE & NOT NULL,  -- bytea (bytes32 - similar to txHash)
	content           json   NULLABLE,
);

-- TABLE 3 (NOT A COMMUNITY TABLE, but rather a SPELL)
-- I would propose that we build a spell for this (from raw_app_data), because it can change.
parsed_app_data (
	hash          string NOT NULL,  -- bytea
	app_code      string nullable,  -- text
	app_verion    string nullable,  -- text
	env_backend   string nullable,  -- text
	env_meta      string nullable,  -- text
	referrer      string nullable,  -- bytea (bytes20 - aka address)
	slippage_bips int nullable      -- numeric 
);