f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
304 lines
9.1 KiB
JavaScript
304 lines
9.1 KiB
JavaScript
// @vitest-environment node
|
|
/**
|
|
* AI Agent Tools PostgreSQL Integration Tests
|
|
* TDD: 🔴 RED → 🟢 GREEN → 🔵 REFACTOR
|
|
*
|
|
* Run: DATABASE_TYPE=postgres npm test -- backend/__tests__/ai-tools/ai-tools-postgres.test.js
|
|
*/
|
|
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
|
|
import pg from 'pg';
|
|
|
|
const { Pool } = pg;
|
|
|
|
// PostgreSQL connection
|
|
const pool = new Pool({
|
|
host: process.env.DB_HOST || 'localhost',
|
|
port: parseInt(process.env.DB_PORT || '5432'),
|
|
database: process.env.DB_NAME || 'godcrm_prod',
|
|
user: process.env.DB_USER || 'godcrm',
|
|
password: process.env.DB_PASSWORD || 'godcrm_dev_2026'
|
|
});
|
|
|
|
const dbQuery = async (sql, params = []) => {
|
|
const result = await pool.query(sql, params);
|
|
return result.rows;
|
|
};
|
|
|
|
const dbGet = async (sql, params = []) => {
|
|
const rows = await dbQuery(sql, params);
|
|
return rows[0];
|
|
};
|
|
|
|
describe('AI Agent Tools PostgreSQL Integration', () => {
|
|
|
|
beforeAll(async () => {
|
|
try {
|
|
await pool.query('SELECT 1');
|
|
console.log('✅ PostgreSQL connected');
|
|
} catch (err) {
|
|
console.error('❌ PostgreSQL connection failed:', err.message);
|
|
throw err;
|
|
}
|
|
});
|
|
|
|
afterAll(async () => {
|
|
await pool.end();
|
|
});
|
|
|
|
describe('list_tables tool', () => {
|
|
|
|
it('should list tables for a given space_id', async () => {
|
|
const spaceId = 12; // Known space with tables
|
|
|
|
const tables = await dbQuery(`
|
|
SELECT ut.id, ut.name, ut.icon, ut.description, p.name as project_name, p.id as project_id,
|
|
(SELECT COUNT(*) FROM table_rows WHERE table_id = ut.id) as row_count
|
|
FROM universal_tables ut
|
|
JOIN projects p ON ut.project_id = p.id
|
|
WHERE p.space_id = $1
|
|
ORDER BY p.name, ut.name
|
|
`, [spaceId]);
|
|
|
|
console.log(`📊 Found ${tables.length} tables in space ${spaceId}`);
|
|
|
|
expect(tables).toBeDefined();
|
|
expect(Array.isArray(tables)).toBe(true);
|
|
expect(tables.length).toBeGreaterThan(0);
|
|
|
|
// Each table should have required fields
|
|
tables.slice(0, 3).forEach(table => {
|
|
expect(table.id).toBeDefined();
|
|
expect(table.name).toBeDefined();
|
|
expect(table.project_name).toBeDefined();
|
|
});
|
|
});
|
|
|
|
it('should list tables for a given project_id', async () => {
|
|
const projectId = 24; // System Data project
|
|
|
|
const tables = await dbQuery(`
|
|
SELECT id, name, icon, description,
|
|
(SELECT COUNT(*) FROM table_rows WHERE table_id = ut.id) as row_count
|
|
FROM universal_tables ut
|
|
WHERE project_id = $1
|
|
`, [projectId]);
|
|
|
|
console.log(`📊 Found ${tables.length} tables in project ${projectId}`);
|
|
|
|
expect(tables).toBeDefined();
|
|
expect(Array.isArray(tables)).toBe(true);
|
|
});
|
|
|
|
it('should return empty array for non-existent space', async () => {
|
|
const fakeSpaceId = 99999;
|
|
|
|
const tables = await dbQuery(`
|
|
SELECT ut.id, ut.name
|
|
FROM universal_tables ut
|
|
JOIN projects p ON ut.project_id = p.id
|
|
WHERE p.space_id = $1
|
|
`, [fakeSpaceId]);
|
|
|
|
expect(tables).toEqual([]);
|
|
});
|
|
});
|
|
|
|
describe('get_workspace_info tool', () => {
|
|
|
|
it('should get workspace info for space', async () => {
|
|
const spaceId = 12;
|
|
|
|
// Get space info
|
|
const space = await dbGet(`SELECT * FROM spaces WHERE id = $1`, [spaceId]);
|
|
|
|
expect(space).toBeDefined();
|
|
expect(space.id).toBe(spaceId);
|
|
|
|
// Get projects count
|
|
const projectsResult = await dbGet(`
|
|
SELECT COUNT(*) as count FROM projects WHERE space_id = $1
|
|
`, [spaceId]);
|
|
|
|
expect(parseInt(projectsResult.count)).toBeGreaterThan(0);
|
|
|
|
// Get tables count
|
|
const tablesResult = await dbGet(`
|
|
SELECT COUNT(*) as count
|
|
FROM universal_tables ut
|
|
JOIN projects p ON ut.project_id = p.id
|
|
WHERE p.space_id = $1
|
|
`, [spaceId]);
|
|
|
|
console.log(`📊 Space ${spaceId}: ${projectsResult.count} projects, ${tablesResult.count} tables`);
|
|
|
|
expect(parseInt(tablesResult.count)).toBeGreaterThan(0);
|
|
});
|
|
});
|
|
|
|
describe('query_table_data tool', () => {
|
|
|
|
it('should query data from a table', async () => {
|
|
// Find a table with data
|
|
const table = await dbGet(`
|
|
SELECT ut.id, ut.name, COUNT(tr.id) as row_count
|
|
FROM universal_tables ut
|
|
LEFT JOIN table_rows tr ON tr.table_id = ut.id
|
|
GROUP BY ut.id, ut.name
|
|
HAVING COUNT(tr.id) > 0
|
|
LIMIT 1
|
|
`);
|
|
|
|
if (!table) {
|
|
console.log('⚠️ No tables with data found');
|
|
return;
|
|
}
|
|
|
|
console.log(`📊 Testing with table: ${table.name} (${table.row_count} rows)`);
|
|
|
|
const rows = await dbQuery(`
|
|
SELECT id, data FROM table_rows WHERE table_id = $1 LIMIT 10
|
|
`, [table.id]);
|
|
|
|
expect(rows.length).toBeGreaterThan(0);
|
|
expect(rows[0].data).toBeDefined();
|
|
});
|
|
|
|
it('should handle search parameter', async () => {
|
|
// Find API Keys table
|
|
const table = await dbGet(`
|
|
SELECT id FROM universal_tables WHERE name = 'AI Agents' LIMIT 1
|
|
`);
|
|
|
|
if (!table) {
|
|
console.log('⚠️ AI Agents table not found');
|
|
return;
|
|
}
|
|
|
|
// Search for 'General'
|
|
const rows = await dbQuery(`
|
|
SELECT id, data FROM table_rows
|
|
WHERE table_id = $1
|
|
AND (data::text ILIKE $2)
|
|
LIMIT 10
|
|
`, [table.id, '%General%']);
|
|
|
|
console.log(`📊 Found ${rows.length} agents matching 'General'`);
|
|
|
|
expect(Array.isArray(rows)).toBe(true);
|
|
});
|
|
});
|
|
|
|
describe('get_table_schema tool', () => {
|
|
|
|
it('should get table columns/schema', async () => {
|
|
// Find a table with columns
|
|
const table = await dbGet(`
|
|
SELECT ut.id, ut.name
|
|
FROM universal_tables ut
|
|
JOIN table_columns tc ON tc.table_id = ut.id
|
|
GROUP BY ut.id, ut.name
|
|
HAVING COUNT(tc.id) > 0
|
|
LIMIT 1
|
|
`);
|
|
|
|
if (!table) {
|
|
console.log('⚠️ No tables with columns found');
|
|
return;
|
|
}
|
|
|
|
const columns = await dbQuery(`
|
|
SELECT id, column_name, display_name, type, is_visible, is_required
|
|
FROM table_columns
|
|
WHERE table_id = $1
|
|
ORDER BY order_index
|
|
`, [table.id]);
|
|
|
|
console.log(`📊 Table ${table.name} has ${columns.length} columns`);
|
|
|
|
expect(columns.length).toBeGreaterThan(0);
|
|
expect(columns[0].column_name).toBeDefined();
|
|
expect(columns[0].type).toBeDefined();
|
|
});
|
|
});
|
|
|
|
describe('AI Agents configuration', () => {
|
|
|
|
it('should have agents with correct tools array', async () => {
|
|
const agents = await dbQuery(`
|
|
SELECT tr.id, tr.data
|
|
FROM table_rows tr
|
|
JOIN universal_tables ut ON tr.table_id = ut.id
|
|
WHERE ut.name ILIKE '%Agent%'
|
|
LIMIT 10
|
|
`);
|
|
|
|
console.log(`📊 Found ${agents.length} agents`);
|
|
|
|
agents.forEach(agent => {
|
|
const data = typeof agent.data === 'string' ? JSON.parse(agent.data) : agent.data;
|
|
|
|
if (data.tools) {
|
|
let tools = data.tools;
|
|
|
|
// Parse if string
|
|
if (typeof tools === 'string') {
|
|
try {
|
|
tools = JSON.parse(tools);
|
|
} catch {
|
|
tools = [];
|
|
}
|
|
}
|
|
|
|
// Convert to array if needed
|
|
if (!Array.isArray(tools)) {
|
|
// tools might be an object or null
|
|
tools = [];
|
|
}
|
|
|
|
// Tools can be either:
|
|
// 1. Tool IDs (numeric strings like "1027") - valid, references ai_tools table
|
|
// 2. Tool names (lowercase with underscores like "list_tables")
|
|
tools.forEach(tool => {
|
|
if (typeof tool === 'string') {
|
|
const isNumericId = /^\d+$/.test(tool);
|
|
const isToolName = /^[a-z_]+$/.test(tool);
|
|
|
|
// Either valid ID or valid name
|
|
expect(isNumericId || isToolName).toBe(true);
|
|
|
|
if (isNumericId) {
|
|
console.log(` 📌 Tool ID: ${tool}`);
|
|
}
|
|
}
|
|
});
|
|
}
|
|
});
|
|
});
|
|
|
|
it('should have agents linked to valid operators', async () => {
|
|
const agents = await dbQuery(`
|
|
SELECT tr.id, tr.data->>'name' as name, tr.data->>'operator_id' as operator_id
|
|
FROM table_rows tr
|
|
JOIN universal_tables ut ON tr.table_id = ut.id
|
|
WHERE ut.name ILIKE '%Agent%' AND tr.data->>'operator_id' IS NOT NULL
|
|
LIMIT 5
|
|
`);
|
|
|
|
for (const agent of agents) {
|
|
if (agent.operator_id) {
|
|
const operator = await dbGet(`
|
|
SELECT tr.id, tr.data->>'name' as name
|
|
FROM table_rows tr
|
|
WHERE tr.id = $1
|
|
`, [parseInt(agent.operator_id)]);
|
|
|
|
if (operator) {
|
|
console.log(`✅ Agent "${agent.name}" linked to operator "${operator.name}"`);
|
|
} else {
|
|
console.log(`⚠️ Agent "${agent.name}" has invalid operator_id: ${agent.operator_id}`);
|
|
}
|
|
}
|
|
}
|
|
});
|
|
});
|
|
});
|