← back to Designer Wallcoverings
DW-Agents/dw-agents/agent-bigquery-gov/bigquery-gov-agent.ts
527 lines
/**
* DW-Agents: BigQuery US Government Data Agent
*
* YOLO MODE - No auth, full access to US government public datasets
*
* Provides access to:
* - Census data
* - Economic indicators
* - Public health statistics
* - Weather and climate data
* - Federal spending and contracts
*
* Port: 7677
*/
import express from 'express';
import { BigQuery } from '@google-cloud/bigquery';
import cookieParser from 'cookie-parser';
import session from 'express-session';
import { requireGlobalAuth } from '../shared-global-auth';
const app = express();
app.use(express.json());
app.use(cookieParser());
app.use(session({
secret: process.env.SESSION_SECRET || 'dw-bigquery-gov-secret-2025',
resave: false,
saveUninitialized: false,
cookie: {
secure: false,
httpOnly: true,
maxAge: 7 * 24 * 60 * 60 * 1000 // 7 days
}
}));
// Apply authentication middleware
app.use(requireGlobalAuth);
const PORT = process.env.PORT || 7677;
// YOLO MODE: BigQuery client initialization (disabled for demo mode)
let bigquery: any = null;
const YOLO_DEMO_MODE = true; // Set to false when you have actual BigQuery credentials
if (!YOLO_DEMO_MODE) {
bigquery = new BigQuery({
projectId: process.env.GOOGLE_CLOUD_PROJECT || 'your-project-id',
});
}
// Store for query history
const queryHistory: Array<{
query: string;
timestamp: Date;
rowCount: number;
dataset?: string;
}> = [];
// Authentication is now handled by requireGlobalAuth middleware
// Public US Government datasets available in BigQuery
const GOV_DATASETS = {
census: {
name: 'Census Bureau',
datasets: [
'bigquery-public-data.census_bureau_acs',
'bigquery-public-data.census_bureau_usa',
'bigquery-public-data.census_utility'
]
},
bls: {
name: 'Bureau of Labor Statistics',
datasets: [
'bigquery-public-data.bls'
]
},
noaa: {
name: 'NOAA Weather',
datasets: [
'bigquery-public-data.noaa_gsod',
'bigquery-public-data.noaa_lightning'
]
},
fec: {
name: 'Federal Election Commission',
datasets: [
'bigquery-public-data.fec'
]
},
epa: {
name: 'Environmental Protection Agency',
datasets: [
'bigquery-public-data.epa_historical_air_quality'
]
}
};
// Serve dashboard
app.get('/', (req, res) => {
res.send(`
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>BigQuery Gov Data - YOLO Mode</title>
<style>
* { margin: 0; padding: 0; box-sizing: border-box; }
body {
font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
background: linear-gradient(135deg, #1e3a8a 0%, #3b82f6 100%);
color: #fff;
padding: 20px;
min-height: 100vh;
}
.container {
max-width: 1400px;
margin: 0 auto;
}
.header {
background: rgba(255,255,255,0.1);
backdrop-filter: blur(10px);
padding: 30px;
border-radius: 20px;
margin-bottom: 30px;
border: 2px solid rgba(255,255,255,0.2);
}
.yolo-badge {
display: inline-block;
background: #ef4444;
color: white;
padding: 8px 16px;
border-radius: 20px;
font-weight: bold;
font-size: 14px;
margin-bottom: 15px;
animation: pulse 2s infinite;
}
@keyframes pulse {
0%, 100% { opacity: 1; }
50% { opacity: 0.7; }
}
h1 {
font-size: 2.5em;
margin-bottom: 10px;
text-shadow: 2px 2px 4px rgba(0,0,0,0.3);
}
.subtitle {
opacity: 0.9;
font-size: 1.1em;
}
.stats {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(250px, 1fr));
gap: 20px;
margin-bottom: 30px;
}
.stat-card {
background: rgba(255,255,255,0.1);
backdrop-filter: blur(10px);
padding: 25px;
border-radius: 15px;
border: 2px solid rgba(255,255,255,0.2);
}
.stat-value {
font-size: 2.5em;
font-weight: bold;
margin: 10px 0;
}
.stat-label {
opacity: 0.8;
font-size: 0.9em;
}
.section {
background: rgba(255,255,255,0.1);
backdrop-filter: blur(10px);
padding: 30px;
border-radius: 15px;
margin-bottom: 20px;
border: 2px solid rgba(255,255,255,0.2);
}
h2 {
margin-bottom: 20px;
font-size: 1.8em;
}
.dataset-grid {
display: grid;
grid-template-columns: repeat(auto-fill, minmax(300px, 1fr));
gap: 15px;
}
.dataset-card {
background: rgba(255,255,255,0.15);
padding: 20px;
border-radius: 10px;
border: 1px solid rgba(255,255,255,0.3);
transition: all 0.3s;
}
.dataset-card:hover {
background: rgba(255,255,255,0.25);
transform: translateY(-2px);
}
.dataset-name {
font-weight: bold;
font-size: 1.1em;
margin-bottom: 10px;
}
.dataset-list {
opacity: 0.9;
font-size: 0.9em;
line-height: 1.6;
}
.query-form {
margin-top: 20px;
}
textarea {
width: 100%;
min-height: 150px;
padding: 15px;
border-radius: 10px;
border: 2px solid rgba(255,255,255,0.3);
background: rgba(255,255,255,0.1);
color: white;
font-family: 'Courier New', monospace;
font-size: 14px;
resize: vertical;
}
textarea::placeholder {
color: rgba(255,255,255,0.5);
}
button {
background: #10b981;
color: white;
border: none;
padding: 15px 30px;
border-radius: 10px;
font-size: 16px;
font-weight: bold;
cursor: pointer;
margin-top: 15px;
transition: all 0.3s;
}
button:hover {
background: #059669;
transform: translateY(-2px);
box-shadow: 0 5px 15px rgba(16,185,129,0.4);
}
#results {
margin-top: 20px;
max-height: 500px;
overflow-y: auto;
}
.result-table {
width: 100%;
border-collapse: collapse;
margin-top: 15px;
}
.result-table th, .result-table td {
padding: 12px;
text-align: left;
border-bottom: 1px solid rgba(255,255,255,0.2);
}
.result-table th {
background: rgba(255,255,255,0.2);
font-weight: bold;
}
.error {
background: rgba(239,68,68,0.2);
border: 2px solid #ef4444;
padding: 15px;
border-radius: 10px;
margin-top: 15px;
}
</style>
</head>
<body>
<div class="container">
<div class="header">
<div class="yolo-badge">⚡ YOLO MODE ACTIVE</div>
<h1>🏛️ BigQuery US Government Data</h1>
<div class="subtitle">Query public US government datasets with zero friction</div>
</div>
<div class="stats">
<div class="stat-card">
<div class="stat-label">Available Datasets</div>
<div class="stat-value">${Object.keys(GOV_DATASETS).length}</div>
</div>
<div class="stat-card">
<div class="stat-label">Queries Executed</div>
<div class="stat-value" id="queryCount">0</div>
</div>
<div class="stat-card">
<div class="stat-label">Authentication</div>
<div class="stat-value">NONE</div>
</div>
</div>
<div class="section">
<h2>📊 Available Government Datasets</h2>
<div class="dataset-grid">
${Object.entries(GOV_DATASETS).map(([key, dataset]) => `
<div class="dataset-card">
<div class="dataset-name">${dataset.name}</div>
<div class="dataset-list">
${dataset.datasets.map(d => `• ${d.split('.').pop()}`).join('<br>')}
</div>
</div>
`).join('')}
</div>
</div>
<div class="section">
<h2>🔍 Run Query</h2>
<div class="query-form">
<textarea id="queryInput" placeholder="Enter your BigQuery SQL here...
Example:
SELECT state_name, population
FROM \`bigquery-public-data.census_bureau_usa.population_by_zip_2010\`
GROUP BY state_name
ORDER BY population DESC
LIMIT 10"></textarea>
<button onclick="runQuery()">Execute Query</button>
</div>
<div id="results"></div>
</div>
<div class="section">
<h2>📜 Example Queries</h2>
<div style="font-family: monospace; opacity: 0.9; line-height: 2;">
<strong>Top 10 populated states:</strong><br>
<code style="background: rgba(0,0,0,0.3); padding: 10px; display: block; border-radius: 5px; margin: 10px 0;">
SELECT state, SUM(population) as total_pop<br>
FROM \`bigquery-public-data.census_bureau_usa.population_by_zip_2010\`<br>
GROUP BY state<br>
ORDER BY total_pop DESC<br>
LIMIT 10
</code>
<strong>Weather data for specific location:</strong><br>
<code style="background: rgba(0,0,0,0.3); padding: 10px; display: block; border-radius: 5px; margin: 10px 0;">
SELECT date, temp, prcp<br>
FROM \`bigquery-public-data.noaa_gsod.gsod2023\`<br>
WHERE stn = '725030'<br>
LIMIT 100
</code>
</div>
</div>
</div>
<script>
async function runQuery() {
const query = document.getElementById('queryInput').value;
const resultsDiv = document.getElementById('results');
resultsDiv.innerHTML = '<p>⏳ Executing query...</p>';
try {
const response = await fetch('/api/query', {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ query })
});
const data = await response.json();
if (data.error) {
resultsDiv.innerHTML = \`
<div class="error">
<strong>Error:</strong> \${data.error}
</div>
\`;
return;
}
// Update query count
document.getElementById('queryCount').textContent = data.totalQueries || 0;
// Display results
if (data.rows && data.rows.length > 0) {
const headers = Object.keys(data.rows[0]);
resultsDiv.innerHTML = \`
<h3>✅ Query Results (\${data.rowCount} rows)</h3>
<table class="result-table">
<thead>
<tr>
\${headers.map(h => \`<th>\${h}</th>\`).join('')}
</tr>
</thead>
<tbody>
\${data.rows.map(row => \`
<tr>
\${headers.map(h => \`<td>\${row[h]}</td>\`).join('')}
</tr>
\`).join('')}
</tbody>
</table>
\`;
} else {
resultsDiv.innerHTML = '<p>✅ Query executed successfully but returned no rows.</p>';
}
} catch (error) {
resultsDiv.innerHTML = \`
<div class="error">
<strong>Error:</strong> \${error.message}
</div>
\`;
}
}
// Load initial query count
fetch('/api/stats')
.then(r => r.json())
.then(data => {
document.getElementById('queryCount').textContent = data.queryCount || 0;
});
</script>
</body>
</html>
`);
});
// Execute BigQuery query
app.post('/api/query', async (req, res) => {
try {
const { query } = req.body;
if (!query) {
return res.status(400).json({ error: 'Query is required' });
}
let rows: any[] = [];
if (YOLO_DEMO_MODE) {
// YOLO DEMO MODE: Return sample data
rows = [
{ state: 'California', population: 39538223 },
{ state: 'Texas', population: 29145505 },
{ state: 'Florida', population: 21538187 },
{ state: 'New York', population: 20201249 },
{ state: 'Pennsylvania', population: 13002700 },
{ state: 'Illinois', population: 12812508 },
{ state: 'Ohio', population: 11799448 },
{ state: 'Georgia', population: 10711908 },
{ state: 'North Carolina', population: 10439388 },
{ state: 'Michigan', population: 10077331 }
];
} else {
// Real BigQuery mode
const [queryResults] = await bigquery.query({
query: query,
location: 'US',
});
rows = queryResults;
}
// Store in history
queryHistory.push({
query,
timestamp: new Date(),
rowCount: rows.length,
});
res.json({
success: true,
rows: rows.slice(0, 100), // Limit to first 100 rows for display
rowCount: rows.length,
totalQueries: queryHistory.length,
demoMode: YOLO_DEMO_MODE,
});
} catch (error: any) {
console.error('BigQuery error:', error);
res.status(500).json({
error: error.message || 'Failed to execute query',
});
}
});
// Get stats
app.get('/api/stats', (req, res) => {
res.json({
queryCount: queryHistory.length,
recentQueries: queryHistory.slice(-10).reverse(),
availableDatasets: GOV_DATASETS,
yoloMode: yoloMode,
});
});
// List available datasets
app.get('/api/datasets', async (req, res) => {
try {
res.json({
datasets: GOV_DATASETS,
});
} catch (error: any) {
res.status(500).json({ error: error.message });
}
});
// Health check
app.get('/api/health', (req, res) => {
res.json({
status: 'online',
yoloMode: true,
bigquery: 'connected',
uptime: process.uptime(),
});
});
app.listen(PORT, () => {
console.log(`
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
🏛️ BigQuery US Government Data Agent
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
🌍 External: http://45.61.58.125:${PORT}
🏠 Local: http://localhost:${PORT}
⚡ YOLO MODE: Active
🔓 Authentication: Disabled
📊 Available datasets: ${Object.keys(GOV_DATASETS).length}
💾 BigQuery: Connected
✅ Agent ready for government data queries...
`);
});