Skip to content

Supercharge your AI agents with data from the web and beyond. Building the library for superintelligence. 🔥

7.5 KB
Raw Blame
import { sql, SQL } from "drizzle-orm";
import { NodePgDatabase } from "drizzle-orm/node-postgres";
import { db, dbIndex } from "./connection";

type DB = NodePgDatabase;

async function execRows<T = Record<string, any>>(
  database: DB,
  query: SQL,
): Promise<T[]> {
  const res = await database.execute(query);
  return (res.rows ?? []) as T[];
}

// The pg driver returns bigint/numeric columns as strings (to avoid precision
// loss), and raw db.execute() bypasses the schema's mode: "number" mapping.
// Coerce known-numeric columns back to JS numbers at this boundary.
function toNum(v: unknown): number | null {
  return v == null ? null : Number(v);
}

// ============================================================================
// Main database RPCs
// ============================================================================

export type AuthCreditUsageChunkRow = Record<string, any> & {
  team_id: string | null;
  api_key: string;
};

export async function authCreditUsageChunk(
  database: DB,
  input_key: string,
  input_credential_purpose: "general" | "hosted_mcp_oauth" = "general",
): Promise<AuthCreditUsageChunkRow[]> {
  const rows = await execRows<AuthCreditUsageChunkRow>(
    database,
    sql`select * from auth_chunk_2(input_key => ${input_key}, input_credential_purpose => ${input_credential_purpose})`,
  );
  // api_key_id is a bigint column, so the pg driver hands it back as a string.
  for (const row of rows) {
    if (row.api_key_id != null) {
      row.api_key_id_text = String(row.api_key_id);
      row.api_key_id = toNum(row.api_key_id);
    }
  }
  return rows;
}

export function authCreditUsageChunkFromTeam(
  database: DB,
  input_team: string,
): Promise<AuthCreditUsageChunkRow[]> {
  return execRows(
    database,
    sql`select * from auth_chunk_2_from_team(input_team => ${input_team})`,
  );
}

export function agentConsumeFreeRequestIfLeft(
  i_team_id: string,
): Promise<{ consumed: boolean }[]> {
  return execRows(
    db,
    sql`select * from agent_consume_free_request_if_left(i_team_id => ${i_team_id})`,
  );
}

export function diffGetLastScrape(
  i_team_id: string,
  i_url: string,
  i_tag: string | null,
): Promise<{ o_job_id: string; o_date_added: string }[]> {
  return execRows(
    db,
    sql`select * from diff_get_last_scrape_v7(i_team_id => ${i_team_id}, i_url => ${i_url}, i_tag => ${i_tag})`,
  );
}

export function monitoringClaimDueMonitors<T = Record<string, any>>(params: {
  workerId: string;
  limit: number;
  leaseSeconds: number;
}): Promise<T[]> {
  return execRows(
    db,
    sql`select * from monitoring_claim_due_monitors(p_worker_id => ${params.workerId}, p_limit => ${params.limit}, p_lease_seconds => ${params.leaseSeconds})`,
  );
}

// ============================================================================
// Index database RPCs
// ============================================================================

export async function insertOmceJobIfNeeded(
  i_domain_level: number,
  i_domain_hash: Buffer,
): Promise<void> {
  await dbIndex.execute(
    sql`select insert_omce_job_if_needed(i_domain_level => ${i_domain_level}, i_domain_hash => ${i_domain_hash})`,
  );
}

export function queryIndexAtSplitLevel(
  i_level: number,
  i_url_hash: Buffer,
  i_newer_than: string,
): Promise<{ resolved_url: string }[]> {
  return execRows(
    dbIndex,
    sql`select * from query_index_at_split_level(i_level => ${i_level}, i_url_hash => ${i_url_hash}, i_newer_than => ${i_newer_than}::timestamptz)`,
  );
}

export function queryOmceSignatures(
  i_domain_hash: Buffer,
  i_newer_than: string,
): Promise<{ signatures: any[] }[]> {
  return execRows(
    dbIndex,
    sql`select * from query_omce_signatures(i_domain_hash => ${i_domain_hash}, i_newer_than => ${i_newer_than}::timestamptz)`,
  );
}

export function queryEngpickerVerdict(
  i_domain_hash: Buffer,
): Promise<{ verdict: string }[]> {
  return execRows(
    dbIndex,
    sql`select * from query_engpicker_verdict(i_domain_hash => ${i_domain_hash})`,
  );
}

export function queryIndexAtSplitLevelWithMeta(
  i_level: number,
  i_url_hash: Buffer,
  i_newer_than: string,
): Promise<
  { resolved_url: string; title: string | null; description: string | null }[]
> {
  return execRows(
    dbIndex,
    sql`select * from query_index_at_split_level_with_meta(i_level => ${i_level}, i_url_hash => ${i_url_hash}, i_newer_than => ${i_newer_than}::timestamptz)`,
  );
}

export function queryIndexAtDomainSplitLevelWithMeta(
  i_level: number,
  i_domain_hash: Buffer,
  i_newer_than: string,
): Promise<
  { resolved_url: string; title: string | null; description: string | null }[]
> {
  return execRows(
    dbIndex,
    sql`select * from query_index_at_domain_split_level_with_meta(i_level => ${i_level}, i_domain_hash => ${i_domain_hash}, i_newer_than => ${i_newer_than}::timestamptz)`,
  );
}

export function queryDomainPriority(
  p_min_total: number,
  p_min_priority: number,
  p_lim: number,
  p_time: string,
): Promise<{ domain_hash: Buffer; priority: number }[]> {
  return execRows(
    dbIndex,
    sql`select * from query_domain_priority(p_min_total => ${p_min_total}, p_min_priority => ${p_min_priority}, p_lim => ${p_lim}, p_time => ${p_time}::timestamptz)`,
  );
}

export function queryIndexAtDomainSplitLevelOmce<T = Record<string, any>>(
  i_level: number,
  i_domain_hash: Buffer,
  i_newer_than: string,
  limit?: number,
): Promise<T[]> {
  return execRows(
    dbIndex,
    sql`select * from query_index_at_domain_split_level_omce(i_level => ${i_level}, i_domain_hash => ${i_domain_hash}, i_newer_than => ${i_newer_than}::timestamptz)${limit !== undefined ? sql` limit ${limit}` : sql``}`,
  );
}

export function queryMaxAge(
  i_domain_hash: Buffer,
): Promise<{ max_age: number | null }[]> {
  return execRows(
    dbIndex,
    sql`select * from query_max_age(i_domain_hash => ${i_domain_hash})`,
  );
}

type IndexGetRecentRow = {
  id: string;
  created_at: string;
  status: number;
  has_screenshot: boolean;
  has_screenshot_fullscreen: boolean;
  wait_time_ms: number | null;
};

// Same filters as index_get_recent_4, but also returns the per-entry
// capability columns so results can populate the Dragonfly index cache
// (services/index-cache.ts).
export async function indexGetRecent5(params: {
  url_hash: Buffer;
  max_age_ms: number;
  is_mobile: boolean;
  block_ads: boolean;
  feature_screenshot: boolean;
  feature_screenshot_fullscreen: boolean;
  location_country: string | null;
  location_languages: string[] | null;
  wait_time_ms: number;
  is_stealth: boolean;
  min_age_ms: number | null;
}): Promise<IndexGetRecentRow[]> {
  const rows = await execRows<IndexGetRecentRow>(
    dbIndex,
    sql`select * from index_get_recent_5(p_url_hash => ${params.url_hash}, p_max_age_ms => ${params.max_age_ms}, p_is_mobile => ${params.is_mobile}, p_block_ads => ${params.block_ads}, p_feature_screenshot => ${params.feature_screenshot}, p_feature_screenshot_fullscreen => ${params.feature_screenshot_fullscreen}, p_location_country => ${params.location_country}, p_location_languages => ${sql.param(params.location_languages)}::text[], p_wait_time_ms => ${params.wait_time_ms}, p_is_stealth => ${params.is_stealth}, p_min_age_ms => ${params.min_age_ms})`,
  );
  for (const row of rows) {
    row.wait_time_ms = toNum(row.wait_time_ms);
  }
  return rows;
}

export function queryTopUrlsForDomain<T = Record<string, any>>(
  p_domain_hash: Buffer,
  p_time_window: string,
  p_top_n: number,
): Promise<T[]> {
  return execRows(
    dbIndex,
    sql`select * from query_top_urls_for_domain(p_domain_hash => ${p_domain_hash}, p_time_window => ${p_time_window}::interval, p_top_n => ${p_top_n})`,
  );
}