my-project-public

repository

loading code, commits, and activity

repositories

loading repo index

my-project-public — gitlawb

#1	-- ═══════════════════════════════════════════════════════════════
#2	-- AI AGENT DATABASE SCHEMA - PostgreSQL (Neon)
#3	-- ═══════════════════════════════════════════════════════════════
#4
#5	-- Drop existing tables (for fresh migration)
#6	DROP TABLE IF EXISTS deployment_tool_permissions CASCADE;
#7	DROP TABLE IF EXISTS goat_tools CASCADE;
#8	DROP TABLE IF EXISTS deployment_wallets CASCADE;
#9	DROP TABLE IF EXISTS agent_execution_logs CASCADE;
#10	DROP TABLE IF EXISTS agent_deployments CASCADE;
#11	DROP TABLE IF EXISTS wallet_balances_cache CASCADE;
#12	DROP TABLE IF EXISTS goat_tool_calls CASCADE;
#13	DROP TABLE IF EXISTS smart_wallets CASCADE;
#14	DROP TABLE IF EXISTS api_key_history CASCADE;
#15	DROP TABLE IF EXISTS transactions CASCADE;
#16	DROP TABLE IF EXISTS agent_activity CASCADE;
#17	DROP TABLE IF EXISTS sessions CASCADE;
#18	DROP TABLE IF EXISTS agents CASCADE;
#19
#20	-- ─────────────────────────────────────────────────
#21	-- AGENTS TABLE
#22	-- ─────────────────────────────────────────────────
#23	CREATE TABLE agents (
#24	id TEXT PRIMARY KEY,
#25	name TEXT NOT NULL,
#26	description TEXT,
#27	api_key_hash TEXT NOT NULL UNIQUE,
#28	api_key_prefix TEXT NOT NULL, -- First 12 chars for display
#29	wallet_address TEXT,
#30	chain TEXT NOT NULL DEFAULT 'solana-devnet',
#31	status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'suspended', 'pending')),
#32
#33	-- Permissions (JSON)
#34	permissions TEXT NOT NULL DEFAULT '{"canCreateWallet":true,"canTransfer":true,"canSwap":true,"maxTransferAmount":100,"maxDailyVolume":1000}',
#35
#36	-- Rate limits
#37	requests_per_minute INTEGER NOT NULL DEFAULT 30,
#38	requests_per_day INTEGER NOT NULL DEFAULT 1000,
#39
#40	-- Metadata
#41	metadata TEXT, -- JSON
#42
#43	-- Timestamps
#44	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
#45	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
#46	last_active_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
#47	);
#48
#49	-- Index for API key lookup
#50	CREATE INDEX idx_agents_api_key_hash ON agents(api_key_hash);
#51	CREATE INDEX idx_agents_status ON agents(status);
#52	CREATE INDEX idx_agents_wallet ON agents(wallet_address);
#53
#54	-- ─────────────────────────────────────────────────
#55	-- SESSIONS TABLE
#56	-- ─────────────────────────────────────────────────
#57	CREATE TABLE sessions (
#58	id TEXT PRIMARY KEY,
#59	agent_id TEXT NOT NULL,
#60	token_hash TEXT NOT NULL UNIQUE,
#61	ip_address TEXT,
#62	user_agent TEXT,
#63	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
#64	expires_at TIMESTAMPTZ NOT NULL,
#65	last_used_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
#66
#67	FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
#68	);
#69
#70	-- Index for session lookup
#71	CREATE INDEX idx_sessions_token ON sessions(token_hash);
#72	CREATE INDEX idx_sessions_agent ON sessions(agent_id);
#73	CREATE INDEX idx_sessions_expires ON sessions(expires_at);
#74
#75	-- ─────────────────────────────────────────────────
#76	-- AGENT ACTIVITY LOG
#77	-- ─────────────────────────────────────────────────
#78	CREATE TABLE agent_activity (
#79	id SERIAL PRIMARY KEY,
#80	agent_id TEXT NOT NULL,
#81	action TEXT NOT NULL, -- 'login', 'wallet_create', 'transfer', 'fund', etc.
#82	details TEXT, -- JSON with action-specific data
#83	ip_address TEXT,
#84	status TEXT NOT NULL DEFAULT 'success' CHECK(status IN ('success', 'failed', 'pending')),
#85	error_message TEXT,
#86	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
#87
#88	FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
#89	);
#90
#91	-- Index for activity queries
#92	CREATE INDEX idx_activity_agent ON agent_activity(agent_id);
#93	CREATE INDEX idx_activity_action ON agent_activity(action);
#94	CREATE INDEX idx_activity_created ON agent_activity(created_at);
#95
#96	-- ─────────────────────────────────────────────────
#97	-- WALLET TRANSACTIONS
#98	-- ─────────────────────────────────────────────────
#99	CREATE TABLE transactions (
#100	id TEXT PRIMARY KEY,
#101	agent_id TEXT NOT NULL,
#102	type TEXT NOT NULL CHECK(type IN ('transfer', 'swap', 'fund')),
#103	from_address TEXT,
#104	to_address TEXT,
#105	token TEXT,
#106	amount TEXT,
#107	chain TEXT NOT NULL DEFAULT 'solana-devnet',
#108	tx_hash TEXT,
#109	status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'success', 'failed')),
#110	error_message TEXT,
#111	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
#112	completed_at TIMESTAMPTZ,
#113
#114	FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
#115	);
#116
#117	CREATE INDEX idx_transactions_agent ON transactions(agent_id);
#118	CREATE INDEX idx_transactions_status ON transactions(status);
#119	CREATE INDEX idx_transactions_hash ON transactions(tx_hash);
#120
#121	-- ─────────────────────────────────────────────────
#122	-- API KEYS TABLE (for tracking regenerations)
#123	-- ─────────────────────────────────────────────────
#124	CREATE TABLE api_key_history (
#125	id SERIAL PRIMARY KEY,
#126	agent_id TEXT NOT NULL,
#127	api_key_prefix TEXT NOT NULL,
#128	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
#129	revoked_at TIMESTAMPTZ,
#130	revoke_reason TEXT,
#131
#132	FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
#133	);
#134
#135	CREATE INDEX idx_api_key_history_agent ON api_key_history(agent_id);
#136
#137	-- ─────────────────────────────────────────────────
#138	-- SMART WALLETS TABLE (Crossmint Smart Wallets)
#139	-- ─────────────────────────────────────────────────
#140	CREATE TABLE smart_wallets (
#141	id TEXT PRIMARY KEY,
#142	agent_id TEXT NOT NULL,
#143	wallet_type TEXT NOT NULL DEFAULT 'smart' CHECK(wallet_type IN ('smart', 'mpc', 'custodial')),
#144	address TEXT NOT NULL UNIQUE,
#145	chain TEXT NOT NULL DEFAULT 'solana-devnet',
#146
#147	-- Smart wallet specific fields
#148	admin_signer_address TEXT, -- Admin signer for smart wallets
#149	delegated_signer_id TEXT, -- Crossmint delegated signer ID
#150	delegated_signer_status TEXT CHECK(delegated_signer_status IN ('pending', 'active', 'rejected')),
#151
#152	-- Locator info
#153	locator TEXT, -- Crossmint wallet locator
#154	linked_user TEXT, -- Linked user identifier (email, userId, etc)
#155
#156	-- Wallet config
#157	alias TEXT, -- Wallet alias (e.g., "trading", "treasury")
#158	is_primary BOOLEAN NOT NULL DEFAULT FALSE,
#159
#160	-- Status
#161	status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'suspended', 'pending')),
#162
#163	-- Metadata
#164	metadata TEXT, -- JSON for additional wallet metadata
#165
#166	-- Timestamps
#167	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
#168	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
#169	last_used_at TIMESTAMPTZ,
#170
#171	FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
#172	);
#173
#174	CREATE INDEX idx_smart_wallets_agent ON smart_wallets(agent_id);
#175	CREATE INDEX idx_smart_wallets_address ON smart_wallets(address);
#176	CREATE INDEX idx_smart_wallets_type ON smart_wallets(wallet_type);
#177	CREATE INDEX idx_smart_wallets_chain ON smart_wallets(chain);
#178
#179	-- ─────────────────────────────────────────────────
#180	-- GOAT TOOL CALLS TABLE (Track GOAT SDK tool executions)
#181	-- ─────────────────────────────────────────────────
#182	CREATE TABLE goat_tool_calls (
#183	id SERIAL PRIMARY KEY,
#184	agent_id TEXT NOT NULL,
#185	wallet_id TEXT, -- References smart_wallets.id
#186	wallet_address TEXT,
#187	tool_name TEXT NOT NULL, -- 'getBalance', 'transfer', 'getTokenPrice', 'getSwapQuote'
#188	params TEXT, -- JSON of tool parameters
#189	result TEXT, -- JSON of tool result
#190	status TEXT NOT NULL DEFAULT 'success' CHECK(status IN ('success', 'failed', 'pending')),
#191	error_message TEXT,
#192	execution_time_ms INTEGER,
#193	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
#194
#195	FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
#196	FOREIGN KEY (wallet_id) REFERENCES smart_wallets(id) ON DELETE SET NULL
#197	);
#198
#199	CREATE INDEX idx_goat_tool_calls_agent ON goat_tool_calls(agent_id);
#200	CREATE INDEX idx_goat_tool_calls_wallet ON goat_tool_calls(wallet_id);
#201	CREATE INDEX idx_goat_tool_calls_tool ON goat_tool_calls(tool_name);
#202	CREATE INDEX idx_goat_tool_calls_created ON goat_tool_calls(created_at);
#203
#204	-- ─────────────────────────────────────────────────
#205	-- WALLET BALANCES CACHE (Optional caching for performance)
#206	-- ─────────────────────────────────────────────────
#207	CREATE TABLE wallet_balances_cache (
#208	id SERIAL PRIMARY KEY,
#209	wallet_address TEXT NOT NULL,
#210	chain TEXT NOT NULL DEFAULT 'solana-devnet',
#211	token TEXT NOT NULL, -- 'SOL', 'USDC', or mint address
#212	symbol TEXT,
#213	amount TEXT NOT NULL DEFAULT '0',
#214	decimals INTEGER NOT NULL DEFAULT 9,
#215	usd_value TEXT,
#216	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
#217
#218	UNIQUE(wallet_address, chain, token)
#219	);
#220
#221	CREATE INDEX idx_wallet_balances_address ON wallet_balances_cache(wallet_address);
#222	CREATE INDEX idx_wallet_balances_updated ON wallet_balances_cache(updated_at);
#223
#224	-- ═══════════════════════════════════════════════════════════════
#225	-- DEPLOYMENT SCHEMA - Extended tables for agent deployment
#226	-- ═══════════════════════════════════════════════════════════════
#227
#228	-- Agent deployments table
#229	CREATE TABLE agent_deployments (
#230	id TEXT PRIMARY KEY,
#231	agent_id TEXT NOT NULL,
#232	name TEXT NOT NULL,
#233	description TEXT,
#234	status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'deploying', 'running', 'stopped', 'error')),
#235	wallet_address TEXT,
#236	chain TEXT DEFAULT 'solana-devnet',
#237	public_key TEXT,
#238	delegated_signer_id TEXT,
#239	delegated_signer_status TEXT CHECK (delegated_signer_status IN ('pending', 'active', 'rejected')),
#240	configuration TEXT DEFAULT '{}',
#241	capabilities TEXT DEFAULT '[]',
#242	created_at TIMESTAMPTZ DEFAULT NOW(),
#243	deployed_at TIMESTAMPTZ,
#244	last_active_at TIMESTAMPTZ,
#245	metadata TEXT,
#246	FOREIGN KEY (agent_id) REFERENCES agents(id)
#247	);
#248
#249	-- Index for faster queries
#250	CREATE INDEX idx_deployments_agent_id ON agent_deployments(agent_id);
#251	CREATE INDEX idx_deployments_status ON agent_deployments(status);
#252	CREATE INDEX idx_deployments_wallet ON agent_deployments(wallet_address);
#253
#254	-- Agent execution logs
#255	CREATE TABLE agent_execution_logs (
#256	id SERIAL PRIMARY KEY,
#257	deployment_id TEXT NOT NULL,
#258	action TEXT NOT NULL,
#259	input TEXT,
#260	output TEXT,
#261	status TEXT DEFAULT 'success',
#262	error TEXT,
#263	tokens_used INTEGER DEFAULT 0,
#264	execution_time_ms INTEGER DEFAULT 0,
#265	created_at TIMESTAMPTZ DEFAULT NOW(),
#266	FOREIGN KEY (deployment_id) REFERENCES agent_deployments(id)
#267	);
#268
#269	CREATE INDEX idx_execution_logs_deployment ON agent_execution_logs(deployment_id);
#270
#271	-- ─────────────────────────────────────────────────
#272	-- DEPLOYMENT WALLETS TABLE (Link deployments to smart wallets)
#273	-- ─────────────────────────────────────────────────
#274	CREATE TABLE deployment_wallets (
#275	id TEXT PRIMARY KEY,
#276	deployment_id TEXT NOT NULL,
#277	wallet_id TEXT NOT NULL,
#278	wallet_type TEXT NOT NULL DEFAULT 'smart' CHECK (wallet_type IN ('smart', 'mpc', 'custodial')),
#279	wallet_address TEXT NOT NULL,
#280	chain TEXT DEFAULT 'solana-devnet',
#281	role TEXT DEFAULT 'primary' CHECK (role IN ('primary', 'treasury', 'trading', 'gas')),
#282
#283	-- Signer configuration
#284	signer_type TEXT DEFAULT 'api-key' CHECK (signer_type IN ('api-key', 'delegated', 'admin')),
#285	admin_signer_address TEXT,
#286	delegated_signer_id TEXT,
#287	delegated_signer_status TEXT CHECK (delegated_signer_status IN ('pending', 'active', 'rejected')),
#288
#289	-- Permissions for this wallet in deployment
#290	permissions TEXT DEFAULT '{"canTransfer":true,"canSwap":true,"maxTransferAmount":100}',
#291
#292	created_at TIMESTAMPTZ DEFAULT NOW(),
#293	updated_at TIMESTAMPTZ DEFAULT NOW(),
#294
#295	FOREIGN KEY (deployment_id) REFERENCES agent_deployments(id) ON DELETE CASCADE,
#296	FOREIGN KEY (wallet_id) REFERENCES smart_wallets(id) ON DELETE CASCADE
#297	);
#298
#299	CREATE INDEX idx_deployment_wallets_deployment ON deployment_wallets(deployment_id);
#300	CREATE INDEX idx_deployment_wallets_wallet ON deployment_wallets(wallet_id);
#301	CREATE INDEX idx_deployment_wallets_address ON deployment_wallets(wallet_address);
#302
#303	-- ─────────────────────────────────────────────────
#304	-- GOAT TOOL DEFINITIONS (Available GOAT SDK tools)
#305	-- ─────────────────────────────────────────────────
#306	CREATE TABLE goat_tools (
#307	id TEXT PRIMARY KEY,
#308	name TEXT NOT NULL UNIQUE,
#309	description TEXT,
#310	category TEXT DEFAULT 'general' CHECK (category IN ('general', 'wallet', 'defi', 'nft', 'data')),
#311	schema_json TEXT NOT NULL, -- JSON Schema for parameters
#312	requires_wallet BOOLEAN DEFAULT TRUE,
#313	requires_signing BOOLEAN DEFAULT FALSE,
#314	enabled BOOLEAN DEFAULT TRUE,
#315	created_at TIMESTAMPTZ DEFAULT NOW()
#316	);
#317
#318	-- Insert default GOAT tools
#319	INSERT INTO goat_tools (id, name, description, category, schema_json, requires_wallet, requires_signing) VALUES
#320	('tool_get_balance', 'getBalance', 'Get wallet balance for SOL or any token', 'wallet', '{"type":"object","properties":{"address":{"type":"string"},"token":{"type":"string"}},"required":["address"]}', TRUE, FALSE),
#321	('tool_transfer', 'transfer', 'Transfer tokens between wallets', 'wallet', '{"type":"object","properties":{"toAddress":{"type":"string"},"token":{"type":"string"},"amount":{"type":"string"}},"required":["toAddress","token","amount"]}', TRUE, TRUE),
#322	('tool_get_token_price', 'getTokenPrice', 'Get current token price from CoinGecko', 'data', '{"type":"object","properties":{"token":{"type":"string"},"currency":{"type":"string","default":"usd"}},"required":["token"]}', FALSE, FALSE),
#323	('tool_get_swap_quote', 'getSwapQuote', 'Get swap quote from Jupiter aggregator', 'defi', '{"type":"object","properties":{"inputToken":{"type":"string"},"outputToken":{"type":"string"},"amount":{"type":"string"},"slippageBps":{"type":"number"}},"required":["inputToken","outputToken","amount"]}', FALSE, FALSE),
#324	('tool_execute_swap', 'executeSwap', 'Execute a token swap via Jupiter', 'defi', '{"type":"object","properties":{"inputToken":{"type":"string"},"outputToken":{"type":"string"},"amount":{"type":"string"},"slippageBps":{"type":"number"}},"required":["inputToken","outputToken","amount"]}', TRUE, TRUE),
#325	('tool_airdrop_devnet', 'airdropDevnet', 'Request devnet SOL airdrop', 'wallet', '{"type":"object","properties":{"address":{"type":"string"},"amount":{"type":"number","default":1}},"required":["address"]}', TRUE, FALSE);
#326
#327	-- ─────────────────────────────────────────────────
#328	-- DEPLOYMENT TOOL PERMISSIONS (Which tools a deployment can use)
#329	-- ─────────────────────────────────────────────────
#330	CREATE TABLE deployment_tool_permissions (
#331	id SERIAL PRIMARY KEY,
#332	deployment_id TEXT NOT NULL,
#333	tool_id TEXT NOT NULL,
#334	enabled BOOLEAN DEFAULT TRUE,
#335	max_daily_calls INTEGER DEFAULT 1000,
#336	calls_today INTEGER DEFAULT 0,
#337	last_reset_at TIMESTAMPTZ DEFAULT NOW(),
#338	created_at TIMESTAMPTZ DEFAULT NOW(),
#339
#340	FOREIGN KEY (deployment_id) REFERENCES agent_deployments(id) ON DELETE CASCADE,
#341	FOREIGN KEY (tool_id) REFERENCES goat_tools(id) ON DELETE CASCADE,
#342	UNIQUE(deployment_id, tool_id)
#343	);
#344
#345	CREATE INDEX idx_deployment_tool_perms_deployment ON deployment_tool_permissions(deployment_id);
#346	CREATE INDEX idx_deployment_tool_perms_tool ON deployment_tool_permissions(tool_id);
#347

z6Mkq5mY3JWtxoxUobWcfNHm7AkRubgSWEZTkBVqZXJviFZ5/my-project-public