f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
365 lines
12 KiB
JavaScript
365 lines
12 KiB
JavaScript
#!/usr/bin/env node
|
|
|
|
/**
|
|
* Standalone script to generate vector embeddings for ALL rows in table 1790 (AI Tools)
|
|
*
|
|
* Vector column config:
|
|
* Column ID: 21747
|
|
* Column name: vector_decription
|
|
* Formula: {{name}}\n{{tags}}\n{{description}}
|
|
* Model: text-embedding-3-small
|
|
*
|
|
* Usage: node generate-vectors-1790.js [--batch-size=50] [--start-offset=0] [--dry-run]
|
|
*/
|
|
|
|
import pg from 'pg';
|
|
import { getSecret } from '../services/secrets/getSecret.js';
|
|
const { Pool } = pg;
|
|
|
|
// --- Config ---
|
|
const TABLE_ID = 1790;
|
|
const COLUMN_ID = 21747;
|
|
const EMBEDDING_MODEL = 'text-embedding-3-small';
|
|
// ADR-0040: vault first, env fallback during transition (top-level await OK in ESM).
|
|
const OPENAI_API_KEY = await getSecret('openai_api_key', 'OPENAI_API_KEY');
|
|
const OPENAI_BASE_URL = 'https://api.openai.com/v1';
|
|
|
|
// Parse CLI args
|
|
const args = process.argv.slice(2);
|
|
const getArg = (name, defaultVal) => {
|
|
const arg = args.find(a => a.startsWith(`--${name}=`));
|
|
return arg ? arg.split('=')[1] : defaultVal;
|
|
};
|
|
const BATCH_SIZE = parseInt(getArg('batch-size', '50'), 10);
|
|
const START_OFFSET = parseInt(getArg('start-offset', '0'), 10);
|
|
const DRY_RUN = args.includes('--dry-run');
|
|
const SKIP_EXISTING = !args.includes('--regenerate');
|
|
|
|
// Database connection
|
|
const pool = new Pool({
|
|
host: process.env.POSTGRES_HOST || 'localhost',
|
|
port: parseInt(process.env.POSTGRES_PORT || '5432', 10),
|
|
database: process.env.POSTGRES_DB || 'godcrm_prod',
|
|
user: process.env.POSTGRES_USER || 'godcrm',
|
|
password: process.env.POSTGRES_PASSWORD || 'godcrm_dev_2026',
|
|
});
|
|
|
|
// --- Helpers ---
|
|
|
|
function applyFormula(formula, rowData) {
|
|
if (!formula) return '';
|
|
return formula.replace(/\{\{(\w+)\}\}/g, (match, key) => {
|
|
const val = rowData[key];
|
|
if (val === undefined || val === null) return '';
|
|
if (typeof val === 'object') {
|
|
if (Array.isArray(val)) return val.join(', ');
|
|
return JSON.stringify(val);
|
|
}
|
|
return String(val);
|
|
});
|
|
}
|
|
|
|
// Batch embedding - send multiple texts at once (OpenAI supports this)
|
|
async function generateEmbeddingsBatch(texts, retries = 3) {
|
|
for (let attempt = 1; attempt <= retries; attempt++) {
|
|
try {
|
|
const response = await fetch(`${OPENAI_BASE_URL}/embeddings`, {
|
|
method: 'POST',
|
|
headers: {
|
|
'Authorization': `Bearer ${OPENAI_API_KEY}`,
|
|
'Content-Type': 'application/json',
|
|
},
|
|
body: JSON.stringify({
|
|
model: EMBEDDING_MODEL,
|
|
input: texts,
|
|
}),
|
|
});
|
|
|
|
if (!response.ok) {
|
|
const errBody = await response.text();
|
|
if (response.status === 429) {
|
|
const waitTime = Math.min(attempt * 10000, 60000);
|
|
console.warn(` Rate limited. Waiting ${waitTime / 1000}s before retry ${attempt}/${retries}...`);
|
|
await sleep(waitTime);
|
|
continue;
|
|
}
|
|
throw new Error(`OpenAI API error ${response.status}: ${errBody}`);
|
|
}
|
|
|
|
const data = await response.json();
|
|
// Sort by index to maintain order
|
|
return data.data.sort((a, b) => a.index - b.index).map(d => d.embedding);
|
|
} catch (err) {
|
|
if (attempt === retries) throw err;
|
|
const waitTime = attempt * 3000;
|
|
console.warn(` Attempt ${attempt} failed: ${err.message}. Retrying in ${waitTime / 1000}s...`);
|
|
await sleep(waitTime);
|
|
}
|
|
}
|
|
}
|
|
|
|
async function generateEmbeddingSingle(text, retries = 3) {
|
|
for (let attempt = 1; attempt <= retries; attempt++) {
|
|
try {
|
|
const response = await fetch(`${OPENAI_BASE_URL}/embeddings`, {
|
|
method: 'POST',
|
|
headers: {
|
|
'Authorization': `Bearer ${OPENAI_API_KEY}`,
|
|
'Content-Type': 'application/json',
|
|
},
|
|
body: JSON.stringify({
|
|
model: EMBEDDING_MODEL,
|
|
input: text,
|
|
}),
|
|
});
|
|
|
|
if (!response.ok) {
|
|
const errBody = await response.text();
|
|
if (response.status === 429) {
|
|
const waitTime = Math.min(attempt * 5000, 30000);
|
|
console.warn(` Rate limited. Waiting ${waitTime / 1000}s before retry ${attempt}/${retries}...`);
|
|
await sleep(waitTime);
|
|
continue;
|
|
}
|
|
throw new Error(`OpenAI API error ${response.status}: ${errBody}`);
|
|
}
|
|
|
|
const data = await response.json();
|
|
return data.data[0].embedding;
|
|
} catch (err) {
|
|
if (attempt === retries) throw err;
|
|
const waitTime = attempt * 2000;
|
|
console.warn(` Attempt ${attempt} failed: ${err.message}. Retrying in ${waitTime / 1000}s...`);
|
|
await sleep(waitTime);
|
|
}
|
|
}
|
|
}
|
|
|
|
function sleep(ms) {
|
|
return new Promise(resolve => setTimeout(resolve, ms));
|
|
}
|
|
|
|
// --- Main ---
|
|
|
|
async function main() {
|
|
console.log('=== Vector Embedding Generator for Table 1790 (AI Tools) ===');
|
|
console.log(`Model: ${EMBEDDING_MODEL}`);
|
|
console.log(`Batch size: ${BATCH_SIZE}`);
|
|
console.log(`Start offset: ${START_OFFSET}`);
|
|
console.log(`Skip existing: ${SKIP_EXISTING}`);
|
|
console.log(`Dry run: ${DRY_RUN}`);
|
|
console.log('');
|
|
|
|
if (!OPENAI_API_KEY) {
|
|
console.error('ERROR: OPENAI_API_KEY environment variable is required');
|
|
process.exit(1);
|
|
}
|
|
|
|
// 1. Get all columns for this table (to map column IDs to names)
|
|
const columnsResult = await pool.query(
|
|
'SELECT id, column_name FROM table_columns WHERE table_id = $1',
|
|
[TABLE_ID]
|
|
);
|
|
const columns = columnsResult.rows;
|
|
const columnMap = {};
|
|
for (const col of columns) {
|
|
columnMap[col.id] = col.column_name;
|
|
}
|
|
console.log(`Found ${columns.length} columns in table ${TABLE_ID}`);
|
|
|
|
// 2. Get the vector column config
|
|
const vectorColResult = await pool.query(
|
|
'SELECT * FROM table_columns WHERE table_id = $1 AND id = $2',
|
|
[TABLE_ID, COLUMN_ID]
|
|
);
|
|
const vectorCol = vectorColResult.rows[0];
|
|
if (!vectorCol) {
|
|
console.error(`ERROR: Vector column ${COLUMN_ID} not found in table ${TABLE_ID}`);
|
|
process.exit(1);
|
|
}
|
|
|
|
let colConfig = {};
|
|
try {
|
|
colConfig = JSON.parse(vectorCol.config || '{}');
|
|
} catch (e) {
|
|
colConfig = {};
|
|
}
|
|
const vectorConfig = colConfig.vector || {};
|
|
const formula = vectorConfig.formula || '{{name}}\\n{{tags}}\\n{{description}}';
|
|
console.log(`Vector formula: ${formula}`);
|
|
console.log('');
|
|
|
|
// 3. Get all rows
|
|
const rowsResult = await pool.query(
|
|
'SELECT id, data FROM table_rows WHERE table_id = $1 ORDER BY id',
|
|
[TABLE_ID]
|
|
);
|
|
const allRows = rowsResult.rows;
|
|
console.log(`Total rows in table: ${allRows.length}`);
|
|
|
|
// 4. Filter rows that need embedding
|
|
const rowsToProcess = [];
|
|
let skippedExisting = 0;
|
|
let skippedEmpty = 0;
|
|
|
|
for (const row of allRows) {
|
|
let rowData = {};
|
|
try {
|
|
rowData = typeof row.data === 'string' ? JSON.parse(row.data) : (row.data || {});
|
|
} catch (e) {
|
|
rowData = {};
|
|
}
|
|
|
|
// Check if embedding already exists
|
|
if (SKIP_EXISTING) {
|
|
const existing = rowData[COLUMN_ID] || rowData[String(COLUMN_ID)];
|
|
if (existing && existing.embedding && Array.isArray(existing.embedding) && existing.embedding.length > 0) {
|
|
skippedExisting++;
|
|
continue;
|
|
}
|
|
}
|
|
|
|
// Build text from formula - map column IDs to column names
|
|
const rowDataByName = {};
|
|
for (const col of columns) {
|
|
if (rowData[col.id] !== undefined) {
|
|
rowDataByName[col.column_name] = rowData[col.id];
|
|
} else if (rowData[String(col.id)] !== undefined) {
|
|
rowDataByName[col.column_name] = rowData[String(col.id)];
|
|
} else if (rowData[col.column_name] !== undefined) {
|
|
rowDataByName[col.column_name] = rowData[col.column_name];
|
|
}
|
|
}
|
|
|
|
let text = applyFormula(formula, rowDataByName);
|
|
// Replace literal \n with actual newlines
|
|
text = text.replace(/\\n/g, '\n');
|
|
|
|
if (!text || text.trim().length === 0) {
|
|
skippedEmpty++;
|
|
continue;
|
|
}
|
|
|
|
rowsToProcess.push({ id: row.id, text: text.trim(), data: rowData });
|
|
}
|
|
|
|
console.log(`Skipped (already have embeddings): ${skippedExisting}`);
|
|
console.log(`Skipped (empty text): ${skippedEmpty}`);
|
|
console.log(`Rows needing embeddings: ${rowsToProcess.length}`);
|
|
|
|
if (rowsToProcess.length === 0) {
|
|
console.log('\nAll rows already have embeddings. Use --regenerate to force regeneration.');
|
|
await pool.end();
|
|
return;
|
|
}
|
|
|
|
if (DRY_RUN) {
|
|
console.log('\n--- DRY RUN: First 10 rows that would be processed ---');
|
|
for (const row of rowsToProcess.slice(0, 10)) {
|
|
console.log(` Row ${row.id}: "${row.text.substring(0, 120)}${row.text.length > 120 ? '...' : ''}"`);
|
|
}
|
|
console.log(`\n(${rowsToProcess.length} rows total would be processed)`);
|
|
await pool.end();
|
|
return;
|
|
}
|
|
|
|
// 5. Process in batches using OpenAI batch embedding API
|
|
const remaining = rowsToProcess.slice(START_OFFSET);
|
|
let successCount = 0;
|
|
let failCount = 0;
|
|
const startTime = Date.now();
|
|
|
|
console.log(`\nProcessing ${remaining.length} rows starting from offset ${START_OFFSET}...`);
|
|
console.log('');
|
|
|
|
for (let i = 0; i < remaining.length; i += BATCH_SIZE) {
|
|
const batch = remaining.slice(i, i + BATCH_SIZE);
|
|
const batchNum = Math.floor(i / BATCH_SIZE) + 1;
|
|
const totalBatches = Math.ceil(remaining.length / BATCH_SIZE);
|
|
|
|
process.stdout.write(`Batch ${batchNum}/${totalBatches} (rows ${i + 1}-${Math.min(i + BATCH_SIZE, remaining.length)} of ${remaining.length})... `);
|
|
|
|
try {
|
|
// Send all texts in this batch to OpenAI at once
|
|
const texts = batch.map(r => r.text);
|
|
const embeddings = await generateEmbeddingsBatch(texts);
|
|
|
|
// Update each row in the database
|
|
for (let j = 0; j < batch.length; j++) {
|
|
const row = batch[j];
|
|
const embedding = embeddings[j];
|
|
|
|
row.data[COLUMN_ID] = {
|
|
text: row.text,
|
|
embedding: embedding,
|
|
generated_at: new Date().toISOString(),
|
|
model: EMBEDDING_MODEL,
|
|
dimensions: embedding.length,
|
|
agent: 'Script: generate-vectors-1790',
|
|
};
|
|
|
|
await pool.query(
|
|
'UPDATE table_rows SET data = $1, updated_at = NOW() WHERE id = $2',
|
|
[JSON.stringify(row.data), row.id]
|
|
);
|
|
|
|
successCount++;
|
|
}
|
|
|
|
const elapsed = ((Date.now() - startTime) / 1000).toFixed(1);
|
|
const rate = (successCount / (elapsed || 1)).toFixed(1);
|
|
const eta = remaining.length > successCount
|
|
? (((remaining.length - successCount) / Math.max(parseFloat(rate), 0.1)) / 60).toFixed(1)
|
|
: 0;
|
|
console.log(`OK (${batch.length} embeddings, total: ${successCount}/${remaining.length}, ${rate}/s, ETA: ${eta}min)`);
|
|
|
|
// Small delay between batches to avoid rate limits
|
|
if (i + BATCH_SIZE < remaining.length) {
|
|
await sleep(300);
|
|
}
|
|
|
|
} catch (err) {
|
|
console.log(`ERROR: ${err.message}`);
|
|
console.log(' Falling back to individual processing for this batch...');
|
|
|
|
// Fall back to individual processing for this batch
|
|
for (const row of batch) {
|
|
try {
|
|
const embedding = await generateEmbeddingSingle(row.text);
|
|
row.data[COLUMN_ID] = {
|
|
text: row.text,
|
|
embedding: embedding,
|
|
generated_at: new Date().toISOString(),
|
|
model: EMBEDDING_MODEL,
|
|
dimensions: embedding.length,
|
|
agent: 'Script: generate-vectors-1790',
|
|
};
|
|
await pool.query(
|
|
'UPDATE table_rows SET data = $1, updated_at = NOW() WHERE id = $2',
|
|
[JSON.stringify(row.data), row.id]
|
|
);
|
|
successCount++;
|
|
await sleep(200);
|
|
} catch (rowErr) {
|
|
console.error(` Row ${row.id} failed: ${rowErr.message}`);
|
|
failCount++;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
const totalTime = ((Date.now() - startTime) / 1000).toFixed(1);
|
|
console.log('');
|
|
console.log('=== COMPLETE ===');
|
|
console.log(`Success: ${successCount}`);
|
|
console.log(`Failed: ${failCount}`);
|
|
console.log(`Total time: ${totalTime}s`);
|
|
console.log(`Average: ${(successCount / (totalTime || 1)).toFixed(1)} rows/s`);
|
|
|
|
await pool.end();
|
|
}
|
|
|
|
main().catch(err => {
|
|
console.error('Fatal error:', err);
|
|
process.exit(1);
|
|
});
|