my-project-public

repository

loading code, commits, and activity

repositories

loading repo index

#1	-- ═══════════════════════════════════════════════════════════════
#2	-- AI AGENT DATABASE SCHEMA
#3	-- Cloudflare D1 (SQLite)
#4	-- ═══════════════════════════════════════════════════════════════
#5
#6	-- Drop existing tables (for fresh migration)
#7	DROP TABLE IF EXISTS wallet_balances_cache;
#8	DROP TABLE IF EXISTS goat_tool_calls;
#9	DROP TABLE IF EXISTS smart_wallets;
#10	DROP TABLE IF EXISTS api_key_history;
#11	DROP TABLE IF EXISTS transactions;
#12	DROP TABLE IF EXISTS agent_activity;
#13	DROP TABLE IF EXISTS sessions;
#14	DROP TABLE IF EXISTS agents;
#15
#16	-- ─────────────────────────────────────────────────
#17	-- AGENTS TABLE
#18	-- ─────────────────────────────────────────────────
#19	CREATE TABLE agents (
#20	id TEXT PRIMARY KEY,
#21	name TEXT NOT NULL,
#22	description TEXT,
#23	api_key_hash TEXT NOT NULL UNIQUE,
#24	api_key_prefix TEXT NOT NULL, -- First 12 chars for display
#25	wallet_address TEXT,
#26	chain TEXT NOT NULL DEFAULT 'solana-devnet',
#27	status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'suspended', 'pending')),
#28
#29	-- Permissions (JSON)
#30	permissions TEXT NOT NULL DEFAULT '{"canCreateWallet":true,"canTransfer":true,"canSwap":true,"maxTransferAmount":100,"maxDailyVolume":1000}',
#31
#32	-- Rate limits
#33	requests_per_minute INTEGER NOT NULL DEFAULT 30,
#34	requests_per_day INTEGER NOT NULL DEFAULT 1000,
#35
#36	-- Metadata
#37	metadata TEXT, -- JSON
#38
#39	-- Timestamps
#40	created_at TEXT NOT NULL DEFAULT (datetime('now')),
#41	updated_at TEXT NOT NULL DEFAULT (datetime('now')),
#42	last_active_at TEXT NOT NULL DEFAULT (datetime('now'))
#43	);
#44
#45	-- Index for API key lookup
#46	CREATE INDEX idx_agents_api_key_hash ON agents(api_key_hash);
#47	CREATE INDEX idx_agents_status ON agents(status);
#48	CREATE INDEX idx_agents_wallet ON agents(wallet_address);
#49
#50	-- ─────────────────────────────────────────────────
#51	-- SESSIONS TABLE
#52	-- ─────────────────────────────────────────────────
#53	CREATE TABLE sessions (
#54	id TEXT PRIMARY KEY,
#55	agent_id TEXT NOT NULL,
#56	token_hash TEXT NOT NULL UNIQUE,
#57	ip_address TEXT,
#58	user_agent TEXT,
#59	created_at TEXT NOT NULL DEFAULT (datetime('now')),
#60	expires_at TEXT NOT NULL,
#61	last_used_at TEXT NOT NULL DEFAULT (datetime('now')),
#62
#63	FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
#64	);
#65
#66	-- Index for session lookup
#67	CREATE INDEX idx_sessions_token ON sessions(token_hash);
#68	CREATE INDEX idx_sessions_agent ON sessions(agent_id);
#69	CREATE INDEX idx_sessions_expires ON sessions(expires_at);
#70
#71	-- ─────────────────────────────────────────────────
#72	-- AGENT ACTIVITY LOG
#73	-- ─────────────────────────────────────────────────
#74	CREATE TABLE agent_activity (
#75	id INTEGER PRIMARY KEY AUTOINCREMENT,
#76	agent_id TEXT NOT NULL,
#77	action TEXT NOT NULL, -- 'login', 'wallet_create', 'transfer', 'fund', etc.
#78	details TEXT, -- JSON with action-specific data
#79	ip_address TEXT,
#80	status TEXT NOT NULL DEFAULT 'success' CHECK(status IN ('success', 'failed', 'pending')),
#81	error_message TEXT,
#82	created_at TEXT NOT NULL DEFAULT (datetime('now')),
#83
#84	FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
#85	);
#86
#87	-- Index for activity queries
#88	CREATE INDEX idx_activity_agent ON agent_activity(agent_id);
#89	CREATE INDEX idx_activity_action ON agent_activity(action);
#90	CREATE INDEX idx_activity_created ON agent_activity(created_at);
#91
#92	-- ─────────────────────────────────────────────────
#93	-- WALLET TRANSACTIONS
#94	-- ─────────────────────────────────────────────────
#95	CREATE TABLE transactions (
#96	id TEXT PRIMARY KEY,
#97	agent_id TEXT NOT NULL,
#98	type TEXT NOT NULL CHECK(type IN ('transfer', 'swap', 'fund')),
#99	from_address TEXT,
#100	to_address TEXT,
#101	token TEXT,
#102	amount TEXT,
#103	chain TEXT NOT NULL DEFAULT 'solana-devnet',
#104	tx_hash TEXT,
#105	status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'success', 'failed')),
#106	error_message TEXT,
#107	created_at TEXT NOT NULL DEFAULT (datetime('now')),
#108	completed_at TEXT,
#109
#110	FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
#111	);
#112
#113	CREATE INDEX idx_transactions_agent ON transactions(agent_id);
#114	CREATE INDEX idx_transactions_status ON transactions(status);
#115	CREATE INDEX idx_transactions_hash ON transactions(tx_hash);
#116
#117	-- ─────────────────────────────────────────────────
#118	-- API KEYS TABLE (for tracking regenerations)
#119	-- ─────────────────────────────────────────────────
#120	CREATE TABLE api_key_history (
#121	id INTEGER PRIMARY KEY AUTOINCREMENT,
#122	agent_id TEXT NOT NULL,
#123	api_key_prefix TEXT NOT NULL,
#124	created_at TEXT NOT NULL DEFAULT (datetime('now')),
#125	revoked_at TEXT,
#126	revoke_reason TEXT,
#127
#128	FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
#129	);
#130
#131	CREATE INDEX idx_api_key_history_agent ON api_key_history(agent_id);
#132
#133	-- ─────────────────────────────────────────────────
#134	-- SMART WALLETS TABLE (Crossmint Smart Wallets)
#135	-- ─────────────────────────────────────────────────
#136	CREATE TABLE smart_wallets (
#137	id TEXT PRIMARY KEY,
#138	agent_id TEXT NOT NULL,
#139	wallet_type TEXT NOT NULL DEFAULT 'smart' CHECK(wallet_type IN ('smart', 'mpc', 'custodial')),
#140	address TEXT NOT NULL UNIQUE,
#141	chain TEXT NOT NULL DEFAULT 'solana-devnet',
#142
#143	-- Smart wallet specific fields
#144	admin_signer_address TEXT, -- Admin signer for smart wallets
#145	delegated_signer_id TEXT, -- Crossmint delegated signer ID
#146	delegated_signer_status TEXT CHECK(delegated_signer_status IN ('pending', 'active', 'rejected')),
#147
#148	-- Locator info
#149	locator TEXT, -- Crossmint wallet locator
#150	linked_user TEXT, -- Linked user identifier (email, userId, etc)
#151
#152	-- Wallet config
#153	alias TEXT, -- Wallet alias (e.g., "trading", "treasury")
#154	is_primary BOOLEAN NOT NULL DEFAULT 0,
#155
#156	-- Status
#157	status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'suspended', 'pending')),
#158
#159	-- Metadata
#160	metadata TEXT, -- JSON for additional wallet metadata
#161
#162	-- Timestamps
#163	created_at TEXT NOT NULL DEFAULT (datetime('now')),
#164	updated_at TEXT NOT NULL DEFAULT (datetime('now')),
#165	last_used_at TEXT,
#166
#167	FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
#168	);
#169
#170	CREATE INDEX idx_smart_wallets_agent ON smart_wallets(agent_id);
#171	CREATE INDEX idx_smart_wallets_address ON smart_wallets(address);
#172	CREATE INDEX idx_smart_wallets_type ON smart_wallets(wallet_type);
#173	CREATE INDEX idx_smart_wallets_chain ON smart_wallets(chain);
#174
#175	-- ─────────────────────────────────────────────────
#176	-- GOAT TOOL CALLS TABLE (Track GOAT SDK tool executions)
#177	-- ─────────────────────────────────────────────────
#178	CREATE TABLE goat_tool_calls (
#179	id INTEGER PRIMARY KEY AUTOINCREMENT,
#180	agent_id TEXT NOT NULL,
#181	wallet_id TEXT, -- References smart_wallets.id
#182	wallet_address TEXT,
#183	tool_name TEXT NOT NULL, -- 'getBalance', 'transfer', 'getTokenPrice', 'getSwapQuote'
#184	params TEXT, -- JSON of tool parameters
#185	result TEXT, -- JSON of tool result
#186	status TEXT NOT NULL DEFAULT 'success' CHECK(status IN ('success', 'failed', 'pending')),
#187	error_message TEXT,
#188	execution_time_ms INTEGER,
#189	created_at TEXT NOT NULL DEFAULT (datetime('now')),
#190
#191	FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
#192	FOREIGN KEY (wallet_id) REFERENCES smart_wallets(id) ON DELETE SET NULL
#193	);
#194
#195	CREATE INDEX idx_goat_tool_calls_agent ON goat_tool_calls(agent_id);
#196	CREATE INDEX idx_goat_tool_calls_wallet ON goat_tool_calls(wallet_id);
#197	CREATE INDEX idx_goat_tool_calls_tool ON goat_tool_calls(tool_name);
#198	CREATE INDEX idx_goat_tool_calls_created ON goat_tool_calls(created_at);
#199
#200	-- ─────────────────────────────────────────────────
#201	-- WALLET BALANCES CACHE (Optional caching for performance)
#202	-- ─────────────────────────────────────────────────
#203	CREATE TABLE wallet_balances_cache (
#204	id INTEGER PRIMARY KEY AUTOINCREMENT,
#205	wallet_address TEXT NOT NULL,
#206	chain TEXT NOT NULL DEFAULT 'solana-devnet',
#207	token TEXT NOT NULL, -- 'SOL', 'USDC', or mint address
#208	symbol TEXT,
#209	amount TEXT NOT NULL DEFAULT '0',
#210	decimals INTEGER NOT NULL DEFAULT 9,
#211	usd_value TEXT,
#212	updated_at TEXT NOT NULL DEFAULT (datetime('now')),
#213
#214	UNIQUE(wallet_address, chain, token)
#215	);
#216
#217	CREATE INDEX idx_wallet_balances_address ON wallet_balances_cache(wallet_address);
#218	CREATE INDEX idx_wallet_balances_updated ON wallet_balances_cache(updated_at);
#219

z6Mkq5mY3JWtxoxUobWcfNHm7AkRubgSWEZTkBVqZXJviFZ5/my-project-public