This repository has been archived on 2026-11-17. You can view files and clone it, but cannot push or open issues or pull requests.
gitgram-ai/scripts/verify-neon.ts
2026-06-22 17:45:56 +03:00

416 lines
15 KiB
TypeScript

#!/usr/bin/env npx tsx
/**
* Neon PostgreSQL Production Verification Script
*
* Connects to the Neon database using DATABASE_URL, then runs:
* 1. Connection probe (SELECT 1)
* 2. Latency probe (SELECT now())
* 3. Replication lag check
* 4. Schema probes (table existence, columns, indexes)
* 5. Migration drift check
*
* Exit codes:
* 0 - All checks passed
* 1 - One or more checks failed
* 2 - Configuration error (missing DATABASE_URL)
*
* Usage:
* DATABASE_URL=postgres://... npx tsx scripts/verify-neon.ts
* DATABASE_URL=postgres://... npx tsx scripts/verify-neon.ts --json
*/
import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
import { sql } from "drizzle-orm";
// ============================================================================
// Configuration
// ============================================================================
const DATABASE_URL = process.env.DATABASE_URL;
const OUTPUT_JSON = process.argv.includes("--json");
if (!DATABASE_URL) {
console.error("ERROR: DATABASE_URL environment variable is required");
process.exit(2);
}
// ============================================================================
// Expected schema definition (mirrored from db-health.ts for standalone use)
// ============================================================================
const EXPECTED_TABLES: Record<string, { columns: string[]; indexes: string[] }> = {
users: {
columns: ["id", "email", "name", "plan", "polar_customer_id", "storage_used_bytes", "created_at", "updated_at"],
indexes: ["users_pkey"],
},
api_keys: {
columns: ["id", "user_id", "key_hash", "key_prefix", "name", "scopes", "expires_at", "allowed_ips", "last_used_at", "created_at", "revoked_at"],
indexes: ["api_keys_pkey"],
},
sessions: {
columns: ["id", "user_id", "token_hash", "ip_address", "user_agent", "device_info", "last_active_at", "expires_at", "revoked", "revoked_at", "created_at"],
indexes: ["sessions_pkey", "idx_auth_sessions_user", "idx_auth_sessions_token_hash", "idx_auth_sessions_expires", "idx_auth_sessions_revoked"],
},
subscriptions: {
columns: ["id", "user_id", "polar_customer_id", "polar_product_id", "status", "plan", "current_period_start", "current_period_end", "cancel_at_period_end", "canceled_at", "created_at", "updated_at"],
indexes: ["subscriptions_pkey", "idx_subscriptions_user", "idx_subscriptions_status", "idx_subscriptions_polar_customer"],
},
organizations: {
columns: ["id", "name", "slug", "owner_id", "plan", "metadata", "created_at", "updated_at"],
indexes: ["organizations_pkey", "idx_organizations_slug", "idx_organizations_owner"],
},
projects: {
columns: ["id", "user_id", "org_id", "name", "root_path", "created_at", "updated_at", "last_synced_at"],
indexes: ["projects_pkey", "idx_projects_user", "idx_projects_org"],
},
checkpoints: {
columns: ["id", "project_id", "user_id", "org_id", "parent_id", "name", "description", "scope", "manifest_hash", "root_tree_hash", "trigger_type", "linked_checkpoint_id", "created_at", "metadata"],
indexes: ["checkpoints_pkey", "idx_checkpoints_project", "idx_checkpoints_user", "idx_checkpoints_org", "idx_checkpoints_created"],
},
checkpoint_files: {
columns: ["checkpoint_id", "file_path", "blob_hash", "size", "mode", "mtime_ms", "change_type"],
indexes: ["checkpoint_files_pkey", "idx_checkpoint_files_path"],
},
blobs: {
columns: ["hash", "user_id", "size", "mime_type", "content", "storage_type", "created_at"],
indexes: ["blobs_pkey"],
},
trees: {
columns: ["id", "project_id", "hash", "parent_path", "entries", "created_at"],
indexes: ["trees_pkey", "idx_trees_project_hash", "idx_trees_hash", "idx_trees_parent_path"],
},
chat_sessions: {
columns: ["id", "project_id", "user_id", "tool", "started_at", "ended_at", "metadata"],
indexes: ["chat_sessions_pkey", "idx_sessions_project", "idx_sessions_user"],
},
chat_messages: {
columns: ["id", "session_id", "project_id", "user_id", "checkpoint_id", "role", "content", "content_hash", "is_context_injection", "timestamp", "metadata"],
indexes: ["chat_messages_pkey", "idx_messages_session", "idx_messages_project", "idx_messages_timestamp"],
},
webhooks: {
columns: ["id", "user_id", "url", "secret", "events", "is_active", "created_at", "updated_at", "last_triggered_at", "failure_count"],
indexes: ["webhooks_pkey", "idx_webhooks_user", "idx_webhooks_active"],
},
repositories: {
columns: ["id", "project_id", "user_id", "name", "root_path", "last_indexed_at", "index_status", "file_count", "symbol_count", "created_at", "updated_at", "metadata"],
indexes: ["repositories_pkey", "idx_repositories_project", "idx_repositories_user"],
},
audit_logs: {
columns: ["id", "user_id", "action", "resource_type", "resource_id", "ip_address", "user_agent", "metadata", "created_at"],
indexes: ["audit_logs_pkey", "idx_audit_logs_user", "idx_audit_logs_action", "idx_audit_logs_resource_type", "idx_audit_logs_created_at"],
},
posts: {
columns: ["id", "author_user_id", "repository_id", "project_id", "type", "caption", "code_snippet", "language", "linked_checkpoint_id", "visibility", "published_at", "created_at", "updated_at"],
indexes: ["posts_pkey", "idx_posts_repo_created", "idx_posts_author_created"],
},
follows: {
columns: ["id", "follower_user_id", "target_type", "target_user_id", "target_repository_id", "created_at"],
indexes: ["follows_pkey", "idx_follows_target_user", "idx_follows_target_repo"],
},
trend_scores: {
columns: ["id", "repository_id", "window_start", "window_end", "score", "signals", "created_at"],
indexes: ["trend_scores_pkey", "idx_trend_scores_repo_window"],
},
};
const EXPECTED_MIGRATIONS = [
"0001_add_storage_used_bytes",
"0002_add_knowledge_base_tables",
"0003_add_canonical_time_context_governance",
"0004_add_gitgram_social_layer",
];
// ============================================================================
// Database connection
// ============================================================================
const neonSql = neon(DATABASE_URL);
const db = drizzle(neonSql);
// ============================================================================
// Probe functions
// ============================================================================
interface ProbeResult {
name: string;
status: "pass" | "fail" | "warn";
message: string;
duration_ms: number;
details?: Record<string, unknown>;
}
async function probeConnection(): Promise<ProbeResult> {
const start = Date.now();
try {
await db.execute(sql`SELECT 1 AS ping`);
return {
name: "connection",
status: "pass",
message: "Database connection successful",
duration_ms: Date.now() - start,
};
} catch (error) {
return {
name: "connection",
status: "fail",
message: `Connection failed: ${error instanceof Error ? error.message : "unknown"}`,
duration_ms: Date.now() - start,
};
}
}
async function probeLatency(): Promise<ProbeResult> {
const start = Date.now();
try {
await db.execute(sql`SELECT now() AS server_time`);
const ms = Date.now() - start;
const status = ms > 2000 ? "fail" : ms > 500 ? "warn" : "pass";
return {
name: "latency",
status,
message: `Query latency: ${ms}ms`,
duration_ms: ms,
};
} catch (error) {
return {
name: "latency",
status: "fail",
message: `Latency probe failed: ${error instanceof Error ? error.message : "unknown"}`,
duration_ms: Date.now() - start,
};
}
}
async function probeReplication(): Promise<ProbeResult> {
const start = Date.now();
try {
const result = await db.execute(
sql`SELECT CASE
WHEN pg_is_in_recovery() THEN
EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))
ELSE 0
END AS lag_seconds`
);
const ms = Date.now() - start;
const lag = parseFloat((result as any[])[0]?.lag_seconds ?? "0");
const status = lag > 30 ? "fail" : lag > 5 ? "warn" : "pass";
return {
name: "replication",
status,
message: `Replication lag: ${lag.toFixed(1)}s`,
duration_ms: ms,
details: { lag_seconds: lag },
};
} catch {
return {
name: "replication",
status: "pass",
message: "Replication check: not available (Neon serverless mode)",
duration_ms: Date.now() - start,
};
}
}
async function probeTable(
tableName: string,
expectedColumns: string[],
expectedIndexes: string[]
): Promise<ProbeResult> {
const start = Date.now();
try {
const colResult = await db.execute(
sql`SELECT column_name FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = ${tableName}
ORDER BY ordinal_position`
);
const actualColumns = (colResult as any[]).map((r: any) => r.column_name);
if (actualColumns.length === 0) {
return {
name: `schema:${tableName}`,
status: "fail",
message: `Table '${tableName}' does not exist`,
duration_ms: Date.now() - start,
};
}
const missingCols = expectedColumns.filter((c) => !actualColumns.includes(c));
const idxResult = await db.execute(
sql`SELECT indexname FROM pg_indexes
WHERE schemaname = 'public' AND tablename = ${tableName}
ORDER BY indexname`
);
const actualIndexes = (idxResult as any[]).map((r: any) => r.indexname);
const missingIdxs = expectedIndexes.filter((i) => !actualIndexes.includes(i));
const messages: string[] = [];
let status: "pass" | "fail" | "warn" = "pass";
if (missingCols.length > 0) {
status = "fail";
messages.push(`missing columns: [${missingCols.join(", ")}]`);
}
if (missingIdxs.length > 0) {
if (status !== "fail") status = "warn";
messages.push(`missing indexes: [${missingIdxs.join(", ")}]`);
}
return {
name: `schema:${tableName}`,
status,
message: messages.length > 0
? `${tableName}: ${messages.join("; ")}`
: `${tableName}: OK (${actualColumns.length} columns, ${actualIndexes.length} indexes)`,
duration_ms: Date.now() - start,
details: {
columns: actualColumns.length,
indexes: actualIndexes.length,
missingColumns: missingCols,
missingIndexes: missingIdxs,
},
};
} catch (error) {
return {
name: `schema:${tableName}`,
status: "fail",
message: `Schema probe for '${tableName}' failed: ${error instanceof Error ? error.message : "unknown"}`,
duration_ms: Date.now() - start,
};
}
}
async function probeMigrationDrift(): Promise<ProbeResult> {
const start = Date.now();
try {
const tableCheck = await db.execute(
sql`SELECT EXISTS (
SELECT FROM information_schema.tables
WHERE table_schema = 'public' AND table_name = '__drizzle_migrations'
) AS exists`
);
if (!(tableCheck as any[])[0]?.exists) {
return {
name: "migration_drift",
status: "warn",
message: "Migration tracking table not found (may use db:push)",
duration_ms: Date.now() - start,
};
}
const migResult = await db.execute(
sql`SELECT tag FROM __drizzle_migrations ORDER BY created_at`
);
const applied = (migResult as any[]).map((r: any) => r.tag);
const missing = EXPECTED_MIGRATIONS.filter((m) => !applied.includes(m));
const extra = applied.filter((m: string) => !EXPECTED_MIGRATIONS.includes(m));
if (missing.length > 0) {
return {
name: "migration_drift",
status: "fail",
message: `Missing migrations: [${missing.join(", ")}]`,
duration_ms: Date.now() - start,
details: { applied, missing, extra },
};
}
if (extra.length > 0) {
return {
name: "migration_drift",
status: "warn",
message: `Unexpected migrations: [${extra.join(", ")}]`,
duration_ms: Date.now() - start,
details: { applied, missing, extra },
};
}
return {
name: "migration_drift",
status: "pass",
message: `All ${EXPECTED_MIGRATIONS.length} expected migrations applied`,
duration_ms: Date.now() - start,
details: { applied },
};
} catch (error) {
return {
name: "migration_drift",
status: "fail",
message: `Migration drift check failed: ${error instanceof Error ? error.message : "unknown"}`,
duration_ms: Date.now() - start,
};
}
}
// ============================================================================
// Main
// ============================================================================
async function main() {
const results: ProbeResult[] = [];
// Health probes
console.log("=== Neon Production Verification ===\n");
console.log("[1/4] Connection probe...");
results.push(await probeConnection());
console.log("[2/4] Latency probe...");
results.push(await probeLatency());
console.log("[3/4] Replication probe...");
results.push(await probeReplication());
// Schema probes
console.log("[4/4] Schema probes...");
const tableNames = Object.keys(EXPECTED_TABLES);
for (const tableName of tableNames) {
const def = EXPECTED_TABLES[tableName];
results.push(await probeTable(tableName, def.columns, def.indexes));
}
// Migration drift
console.log(" Migration drift check...");
results.push(await probeMigrationDrift());
// Output
if (OUTPUT_JSON) {
const passed = results.filter((r) => r.status === "pass").length;
const warned = results.filter((r) => r.status === "warn").length;
const failed = results.filter((r) => r.status === "fail").length;
console.log(
JSON.stringify(
{
timestamp: new Date().toISOString(),
overall: failed > 0 ? "unhealthy" : warned > 0 ? "degraded" : "healthy",
summary: { total: results.length, passed, warned, failed },
results,
},
null,
2
)
);
} else {
console.log("\n=== Results ===\n");
for (const r of results) {
const icon = r.status === "pass" ? "PASS" : r.status === "warn" ? "WARN" : "FAIL";
console.log(` [${icon}] ${r.name}: ${r.message} (${r.duration_ms}ms)`);
}
const passed = results.filter((r) => r.status === "pass").length;
const warned = results.filter((r) => r.status === "warn").length;
const failed = results.filter((r) => r.status === "fail").length;
console.log(`\nTotal: ${results.length} | Passed: ${passed} | Warnings: ${warned} | Failed: ${failed}`);
}
// Exit code
const hasFails = results.some((r) => r.status === "fail");
process.exit(hasFails ? 1 : 0);
}
main().catch((err) => {
console.error("Fatal error:", err);
process.exit(1);
});