lib/db/src/schema/toolGraph.ts

import {
  pgTable,
  text,
  timestamp,
  jsonb,
  integer,
  doublePrecision,
  index,
  uniqueIndex,
} from "drizzle-orm/pg-core";

/**
 * tool_nodes — one row per tool node in the cross-process capability
 * graph. Both the Node `api-server` and the Python `research-engine`
 * read/write this table; `owner_process` distinguishes which side hosts
 * the actual handler.
 */
export const toolNodes = pgTable(
  "tool_nodes",
  {
    id: text("id").primaryKey(),
    name: text("name").notNull().unique(),
    description: text("description").notNull().default(""),
    capabilityTags: jsonb("capability_tags").notNull().default([]),
    inputKind: text("input_kind").notNull().default("json"),
    outputKind: text("output_kind").notNull().default("json"),
    /** "verified" | "provisional" | "rejected" */
    status: text("status").notNull().default("verified"),
    /** "node" | "python" */
    ownerProcess: text("owner_process").notNull(),
    /** JSON Schema for the tool parameters (LlmTool.parameters). */
    specJson: jsonb("spec_json").notNull(),
    /** "system" | "auto" | "user:<id>" */
    createdBy: text("created_by").notNull().default("system"),
    /** Optional opaque pointer to handler module, e.g. "node:tools.search_pubmed". */
    handlerRef: text("handler_ref"),
    /** Stub handler body (auto-drafted); humans review before promoting. */
    handlerStub: text("handler_stub"),
    /** Cost / latency hints shown in subgraph prompts. */
    costHint: doublePrecision("cost_hint"),
    latencyHintMs: integer("latency_hint_ms"),
    version: integer("version").notNull().default(1),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => ({
    byStatus: index("tool_nodes_status_idx").on(t.status),
    byOwner: index("tool_nodes_owner_idx").on(t.ownerProcess),
  }),
);

/**
 * tool_edges — directed capability edges between tools. `relation`
 * captures the functional relationship (e.g. "feeds", "refines",
 * "summarizes", "alternative_to").
 */
export const toolEdges = pgTable(
  "tool_edges",
  {
    id: text("id").primaryKey(),
    fromNode: text("from_node")
      .notNull()
      .references(() => toolNodes.id, { onDelete: "cascade" }),
    toNode: text("to_node")
      .notNull()
      .references(() => toolNodes.id, { onDelete: "cascade" }),
    relation: text("relation").notNull(),
    weight: doublePrecision("weight").notNull().default(1.0),
    /**
     * Seam contract: `{produces: JSONSchema, consumes: JSONSchema}` describing
     * what `from_node` outputs and what `to_node` expects on the wire that
     * connects them. Optional — only enforced for edges added under the
     * atomic-node decomposition (Task #156). The seed-time validator
     * (`tool-graph-seed.ts`) refuses to boot when a contract is present and
     * the produces side is missing a field the consumes side marks required.
     * Used by Task #157 to compute structural seam health.
     */
    contract: jsonb("contract"),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => ({
    byFrom: index("tool_edges_from_idx").on(t.fromNode),
    byTo: index("tool_edges_to_idx").on(t.toNode),
    uniq: uniqueIndex("tool_edges_uniq").on(t.fromNode, t.toNode, t.relation),
  }),
);

/**
 * tool_node_evidence — accumulated evidence per node: invocation
 * samples, success/failure counters, planner-gap references, shadow-run
 * pointers, and per-traversal seam structural samples. The provisional
 * review UI reads this to decide approve / reject; the seam-health
 * aggregator (Task #157) consumes the `seam_health` rows to roll up
 * `tool_edge_health`.
 */
export const toolNodeEvidence = pgTable(
  "tool_node_evidence",
  {
    id: text("id").primaryKey(),
    nodeId: text("node_id")
      .notNull()
      .references(() => toolNodes.id, { onDelete: "cascade" }),
    /**
     * One of:
     *   "invocation" | "planner_gap" | "shadow_run" | "auto_extend"
     *   | "seam_health"
     * `seam_health` rows are written by the seam detector (Task #157):
     * the `nodeId` points at the downstream consumer, and the payload
     * carries `{edge_id, upstream, missing_fields, unused_fields,
     * coverage, contract_issues}` for one traversal of that edge.
     */
    kind: text("kind").notNull(),
    /** Free-form sample payload (sanitized args/result, capability tag, etc.) */
    payload: jsonb("payload").notNull(),
    /** Success counter delta when kind=invocation. */
    success: integer("success").notNull().default(0),
    failure: integer("failure").notNull().default(0),
    /** Optional shadow-session user id. */
    shadowUserId: text("shadow_user_id"),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    /**
     * For `kind="seam_health"` rows only: stamped by the per-edge
     * health aggregator the first time it folds the row into a rollup.
     * The aggregator selects rows where this column is NULL inside a
     * single transaction and immediately stamps them, giving an
     * exactly-once guarantee that no longer depends on time-based
     * watermarks (which were unsafe with sub-millisecond JS `Date`
     * precision and non-monotonic ULIDs across processes).
     */
    seamFoldedAt: timestamp("seam_folded_at", { withTimezone: true }),
  },
  (t) => ({
    byNode: index("tool_node_evidence_node_idx").on(t.nodeId),
    byKind: index("tool_node_evidence_kind_idx").on(t.kind),
    bySeamUnfolded: index("tool_node_evidence_seam_unfolded_idx").on(
      t.kind,
      t.seamFoldedAt,
    ),
  }),
);

/**
 * tool_gap_signals — rolling counters of "planner could not find a fit"
 * events per capability tag. When the counter crosses a threshold, the
 * auto-extend pass creates a provisional tool node attached to the
 * nearest verified branch.
 */
export const toolGapSignals = pgTable(
  "tool_gap_signals",
  {
    id: text("id").primaryKey(),
    capabilityTag: text("capability_tag").notNull(),
    invocationCount: integer("invocation_count").notNull().default(0),
    /** "open" | "extended" | "dismissed" */
    status: text("status").notNull().default("open"),
    /** Last context snippet that triggered the gap. */
    lastContext: jsonb("last_context"),
    /** Provisional node spawned from this signal (if any). */
    extendedNodeId: text("extended_node_id"),
    firstSeenAt: timestamp("first_seen_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    lastSeenAt: timestamp("last_seen_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => ({
    byTag: uniqueIndex("tool_gap_signals_tag_idx").on(t.capabilityTag),
    byStatus: index("tool_gap_signals_status_idx").on(t.status),
  }),
);

export type ToolNodeRow = typeof toolNodes.$inferSelect;
export type InsertToolNodeRow = typeof toolNodes.$inferInsert;
export type ToolEdgeRow = typeof toolEdges.$inferSelect;
export type InsertToolEdgeRow = typeof toolEdges.$inferInsert;
export type ToolNodeEvidenceRow = typeof toolNodeEvidence.$inferSelect;
export type InsertToolNodeEvidenceRow = typeof toolNodeEvidence.$inferInsert;
export type ToolGapSignalRow = typeof toolGapSignals.$inferSelect;
export type InsertToolGapSignalRow = typeof toolGapSignals.$inferInsert;

/**
 * tool_edge_health — rolling per-edge health summary computed by the
 * seam-health aggregator (Task #157). One row per edge (`edge_id` is
 * unique). The aggregator scans new `seam_health` evidence rows since
 * `computed_at`, folds them into exponential moving averages, and
 * advances the row. `formula_version` is bumped whenever the scoring
 * formula changes so old rows can be re-derived from raw evidence.
 *
 * `health_score` is a single 0..1 number combining structural coverage
 * (how much of the consumes-side schema the upstream actually filled)
 * and contract-issue rate (how often the seam diverges from its
 * declared contract). The admin viz colours edges by this value and
 * `listUnhealthyEdges()` surfaces low-scoring edges.
 */
export const toolEdgeHealth = pgTable(
  "tool_edge_health",
  {
    id: text("id").primaryKey(),
    edgeId: text("edge_id")
      .notNull()
      .references(() => toolEdges.id, { onDelete: "cascade" }),
    /** Total seam_health evidence rows ever folded in. */
    traversalCount: integer("traversal_count").notNull().default(0),
    /** Sum of contract_issues counts across all observed traversals. */
    contractIssueCount: integer("contract_issue_count").notNull().default(0),
    /** Sum of distinct missing-field occurrences. */
    missingFieldCount: integer("missing_field_count").notNull().default(0),
    /** Exponential moving average of structural coverage in [0,1]. */
    emaCoverage: doublePrecision("ema_coverage").notNull().default(1.0),
    /** Exponential moving average of overall health in [0,1]. */
    emaHealthScore: doublePrecision("ema_health_score").notNull().default(1.0),
    /** Most recent {field: count} for missing fields (rolling). */
    topMissingFields: jsonb("top_missing_fields").notNull().default({}),
    /** Most recent contract_issue codes (rolling). */
    topContractIssues: jsonb("top_contract_issues").notNull().default({}),
    /** Version of the scoring formula used to compute this row. */
    formulaVersion: integer("formula_version").notNull().default(1),
    lastSampleAt: timestamp("last_sample_at", { withTimezone: true }),
    /**
     * Highest evidence-row id folded into this rollup. Used as the
     * watermark for the next recompute pass. We track the id (a ULID,
     * which is temporally monotonic with sufficient entropy) instead
     * of `created_at` because Postgres timestamptz has microsecond
     * precision while JS `Date` only has milliseconds — round-tripping
     * the timestamp through Drizzle truncates to ms and would cause
     * same-millisecond rows to be folded twice on subsequent passes.
     */
    lastFoldedEvidenceId: text("last_folded_evidence_id"),
    computedAt: timestamp("computed_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => ({
    byEdge: uniqueIndex("tool_edge_health_edge_idx").on(t.edgeId),
    byScore: index("tool_edge_health_score_idx").on(t.emaHealthScore),
  }),
);

export type ToolEdgeHealthRow = typeof toolEdgeHealth.$inferSelect;
export type InsertToolEdgeHealthRow = typeof toolEdgeHealth.$inferInsert;

/**
 * tool_deprecation_candidates — one row per verified node that the
 * deprecation detector (Task #159) has flagged. Status drives the
 * human-review state machine:
 *   - `open`      — newly proposed, awaiting review.
 *   - `deferred`  — reviewer chose "Defer"; do not re-propose until
 *                   `defer_until`.
 *   - `rejected`  — reviewer chose "Reject"; do not re-propose until
 *                   `re_arm_until`.
 *   - `approved`  — reviewer chose "Deprecate"; the node has been
 *                   flipped to `deprecated`. Row is preserved as audit;
 *                   archive job removes it together with the node row
 *                   when archive cutoff passes.
 *
 * `proposal_context` carries the supporting metric snapshot the
 * detector saw (last invocation timestamp, traversal counts, the sibling
 * that made it `redundant`, the splice id that made it `superseded`),
 * plus a `dependencies` list of upstream nodes that still reference
 * this node so the reviewer can act with full context.
 *
 * `decided_by` records the reviewer user id (or "system" for the
 * detector itself when it first creates the row).
 */
export const toolDeprecationCandidates = pgTable(
  "tool_deprecation_candidates",
  {
    id: text("id").primaryKey(),
    nodeId: text("node_id")
      .notNull()
      .references(() => toolNodes.id, { onDelete: "cascade" }),
    /** "cold" | "superseded" | "redundant" */
    classification: text("classification").notNull(),
    /** "open" | "deferred" | "rejected" | "approved" */
    status: text("status").notNull().default("open"),
    proposalContext: jsonb("proposal_context").notNull().default({}),
    /** While status="deferred", do not re-propose until this timestamp. */
    deferUntil: timestamp("defer_until", { withTimezone: true }),
    /** While status="rejected", do not re-propose until this timestamp. */
    reArmUntil: timestamp("re_arm_until", { withTimezone: true }),
    decidedBy: text("decided_by"),
    decidedAt: timestamp("decided_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => ({
    byNode: uniqueIndex("tool_deprecation_candidates_node_idx").on(t.nodeId),
    byStatus: index("tool_deprecation_candidates_status_idx").on(t.status),
  }),
);

export type ToolDeprecationCandidateRow =
  typeof toolDeprecationCandidates.$inferSelect;
export type InsertToolDeprecationCandidateRow =
  typeof toolDeprecationCandidates.$inferInsert;

/**
 * tool_nodes_archive / tool_edges_archive / tool_node_evidence_archive
 * (Task #159) — cold-storage mirrors of the hot tables. The weekly
 * archive job moves rows that have been `deprecated` for ≥
 * DEPRECATION_ARCHIVE_AFTER_DAYS (default 180) into the archive in a
 * single transaction. Columns mirror the source schema exactly except:
 *   - no foreign keys (the hot row is gone after the move),
 *   - an extra `archived_at` timestamp.
 *
 * No restoration path: archived rows are read-only audit. Reviving an
 * archived capability requires re-spawning through the normal #158
 * flow.
 */
export const toolNodesArchive = pgTable(
  "tool_nodes_archive",
  {
    id: text("id").primaryKey(),
    name: text("name").notNull(),
    description: text("description").notNull().default(""),
    capabilityTags: jsonb("capability_tags").notNull().default([]),
    inputKind: text("input_kind").notNull().default("json"),
    outputKind: text("output_kind").notNull().default("json"),
    status: text("status").notNull(),
    ownerProcess: text("owner_process").notNull(),
    specJson: jsonb("spec_json").notNull(),
    createdBy: text("created_by").notNull().default("system"),
    handlerRef: text("handler_ref"),
    handlerStub: text("handler_stub"),
    costHint: doublePrecision("cost_hint"),
    latencyHintMs: integer("latency_hint_ms"),
    version: integer("version").notNull().default(1),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
    archivedAt: timestamp("archived_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => ({
    byName: index("tool_nodes_archive_name_idx").on(t.name),
    byArchivedAt: index("tool_nodes_archive_archived_at_idx").on(t.archivedAt),
  }),
);

export const toolEdgesArchive = pgTable(
  "tool_edges_archive",
  {
    id: text("id").primaryKey(),
    fromNode: text("from_node").notNull(),
    toNode: text("to_node").notNull(),
    relation: text("relation").notNull(),
    weight: doublePrecision("weight").notNull().default(1.0),
    contract: jsonb("contract"),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    archivedAt: timestamp("archived_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => ({
    byFrom: index("tool_edges_archive_from_idx").on(t.fromNode),
    byTo: index("tool_edges_archive_to_idx").on(t.toNode),
  }),
);

export const toolNodeEvidenceArchive = pgTable(
  "tool_node_evidence_archive",
  {
    id: text("id").primaryKey(),
    nodeId: text("node_id").notNull(),
    kind: text("kind").notNull(),
    payload: jsonb("payload").notNull(),
    success: integer("success").notNull().default(0),
    failure: integer("failure").notNull().default(0),
    shadowUserId: text("shadow_user_id"),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    seamFoldedAt: timestamp("seam_folded_at", { withTimezone: true }),
    archivedAt: timestamp("archived_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => ({
    byNode: index("tool_node_evidence_archive_node_idx").on(t.nodeId),
  }),
);

export type ToolNodeArchiveRow = typeof toolNodesArchive.$inferSelect;
export type ToolEdgeArchiveRow = typeof toolEdgesArchive.$inferSelect;
export type ToolNodeEvidenceArchiveRow =
  typeof toolNodeEvidenceArchive.$inferSelect;

/**
 * tool_summary_paths (Task #160) — first-class planner abstractions for
 * `composition_alias` chains. One row per ordered atomic chain that
 * resolves into an alias (e.g. summarize_literature_bucket =
 * select_papers_for_bucket → extract_paper_summary →
 * aggregate_bucket_summary). The seed builder rebuilds these from
 * `tool_edges` (`composes_into` + `feeds`) so the chain is the single
 * source of truth and cannot drift from the actual atomic graph.
 *
 * Columns:
 *   - description_derived  — auto-rendered from head + tail descriptions
 *                            and the chain composition. Recomputed every
 *                            seed; admins should NOT edit this directly.
 *   - description_override — admin-supplied free-form text. Wins over
 *                            `description_derived` when non-null.
 *   - expansion_node_names — ordered jsonb array of constituent atomic
 *                            node names (head → ... → tail).
 *   - alias_node_id        — pointer to the composition_alias node this
 *                            summary path resolves into.
 *   - status               — "active" (built and current) or
 *                            "pending_rebuild" (a splice landed under
 *                            #158 and the chain may have changed).
 */
export const toolSummaryPaths = pgTable(
  "tool_summary_paths",
  {
    id: text("id").primaryKey(),
    name: text("name").notNull().unique(),
    aliasNodeId: text("alias_node_id").references(() => toolNodes.id, {
      onDelete: "set null",
    }),
    descriptionDerived: text("description_derived").notNull().default(""),
    descriptionOverride: text("description_override"),
    expansionNodeNames: jsonb("expansion_node_names").notNull().default([]),
    headAtomName: text("head_atom_name"),
    tailAtomName: text("tail_atom_name"),
    capabilityTags: jsonb("capability_tags").notNull().default([]),
    estCostHint: doublePrecision("est_cost_hint"),
    estLatencyMsHint: integer("est_latency_ms_hint"),
    status: text("status").notNull().default("active"),
    version: integer("version").notNull().default(1),
    traversalCount: integer("traversal_count").notNull().default(0),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => ({
    byStatus: index("tool_summary_paths_status_idx").on(t.status),
  }),
);

export type ToolSummaryPathRow = typeof toolSummaryPaths.$inferSelect;
export type InsertToolSummaryPathRow =
  typeof toolSummaryPaths.$inferInsert;

/**
 * tool_spawn_templates (Task #161) — cross-seam template library that
 * the #158 spawning pipeline learns from. When a provisional node is
 * promoted, the originating seam's structural fingerprint, sub-shape
 * fingerprints, and the final (post-edit) handler skeleton are
 * persisted here. Future spawns search this table; an exact or near
 * match is offered to the human reviewer as a starting template.
 *
 * Privacy invariants (enforced by tests, not by this schema alone):
 *   - `fingerprint_hash` is salted-hashed (env `TEMPLATE_FINGERPRINT_SALT`).
 *   - `missing_field_types` / `unused_field_types` are sorted lists of
 *     JSONSchema *type strings* — they carry no field names, no values,
 *     no domain-specific identifiers.
 *   - `handler_skeleton` / `spec_skeleton` are contract-shaped, not
 *     payload-shaped. The reviewer's edits are persisted only after
 *     the spec stripper drops every property NOT declared in the
 *     skeleton's schema.
 *   - `source_node_name` is the synthesized auto-extender name
 *     (`auto_<tag>_<ulid_suffix>`), never a tag derived from a user
 *     payload. The capability tag itself is a curated label.
 *
 * `status="demoted"` rows remain in the table for audit but are
 * filtered out of `searchTemplates()`, so the proposer silently stops
 * offering low-track-record templates while the admin UI can still
 * surface them. There is no delete path: templates are append-only.
 *
 * `fingerprint_algo_version` lets a future migration re-fingerprint
 * old rows in one shot; mismatched versions are excluded from match
 * results until they are re-fingerprinted.
 */
export const toolSpawnTemplates = pgTable(
  "tool_spawn_templates",
  {
    id: text("id").primaryKey(),
    /** Salted SHA-256 of the canonical structural fingerprint. */
    fingerprintHash: text("fingerprint_hash").notNull(),
    fingerprintAlgoVersion: integer("fingerprint_algo_version")
      .notNull()
      .default(1),
    /**
     * One of: "missing_required_field" | "type_mismatch" |
     * "capability_gap" | "unknown". Picked from the dominant seam
     * symptom at the time of spawn.
     */
    failureMode: text("failure_mode").notNull(),
    /**
     * Sorted JSONSchema type strings of consumes-side fields that the
     * upstream did NOT produce. e.g. ["array","string"]. Field names
     * are deliberately stripped to keep the fingerprint structural.
     */
    missingFieldTypes: jsonb("missing_field_types").notNull().default([]),
    /**
     * Sorted JSONSchema type strings of upstream fields that the
     * downstream did not declare. Same anonymisation rule.
     */
    unusedFieldTypes: jsonb("unused_field_types").notNull().default([]),
    /**
     * Schema-shape fingerprint of the downstream consumer's input
     * schema (sorted, name-stripped). Used as the dominant near-match
     * signal.
     */
    downstreamInputSchemaFingerprint: text(
      "downstream_input_schema_fingerprint",
    ).notNull(),
    /** Schema-shape fingerprint of the promoted node's input schema. */
    promotedInputSchemaFingerprint: text(
      "promoted_input_schema_fingerprint",
    ).notNull(),
    /** Schema-shape fingerprint of the promoted node's output schema. */
    promotedOutputSchemaFingerprint: text(
      "promoted_output_schema_fingerprint",
    ).notNull(),
    /**
     * Contract-shaped handler skeleton — code template with parameter
     * placeholders rather than concrete values. Stored as text.
     */
    handlerSkeleton: text("handler_skeleton").notNull().default(""),
    /**
     * Tool-spec skeleton (description, parameters JSON-Schema). Stored
     * as jsonb so the proposer can deep-clone it safely.
     */
    specSkeleton: jsonb("spec_skeleton").notNull().default({}),
    /**
     * Curated capability tag the source node carried — kept verbatim
     * because tags are an admin-controlled vocabulary, not user data.
     */
    capabilityTag: text("capability_tag").notNull().default(""),
    /** Synthesised name of the source promoted node. */
    sourceNodeName: text("source_node_name").notNull(),
    /** Times the proposer offered this template to a reviewer. */
    offeredCount: integer("offered_count").notNull().default(0),
    /** Times a reviewer chose Use / Use+edit. */
    reuseCount: integer("reuse_count").notNull().default(0),
    /** Times a reuse-marked candidate ultimately got promoted. */
    successCount: integer("success_count").notNull().default(0),
    /** Times a reviewer chose Start fresh. */
    rejectCount: integer("reject_count").notNull().default(0),
    /** "active" | "demoted" */
    status: text("status").notNull().default("active"),
    demotedAt: timestamp("demoted_at", { withTimezone: true }),
    demotedReason: text("demoted_reason"),
    version: integer("version").notNull().default(1),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => ({
    byFingerprint: index("tool_spawn_templates_fingerprint_idx").on(
      t.fingerprintHash,
    ),
    byStatus: index("tool_spawn_templates_status_idx").on(t.status),
    byFailureMode: index("tool_spawn_templates_failure_mode_idx").on(
      t.failureMode,
    ),
    uniq: uniqueIndex("tool_spawn_templates_uniq_idx").on(
      t.fingerprintHash,
      t.fingerprintAlgoVersion,
    ),
  }),
);

export type ToolSpawnTemplateRow = typeof toolSpawnTemplates.$inferSelect;
export type InsertToolSpawnTemplateRow =
  typeof toolSpawnTemplates.$inferInsert;

/**
 * tool_goals — Mode B (#169). One row per declared search goal.
 * Each Goal references an existing dataset node and an existing
 * evaluator node (both must be `verified` at submit time). The Goal's
 * `budget` and `constraints` drive the search loop.
 */
export const toolGoals = pgTable(
  "tool_goals",
  {
    id: text("id").primaryKey(),
    name: text("name").notNull(),
    description: text("description").notNull().default(""),
    datasetNodeId: text("dataset_node_id").notNull(),
    evaluatorNodeId: text("evaluator_node_id").notNull(),
    /** {wallClockMs?: number, maxIterations?: number, maxCandidates?: number} */
    budget: jsonb("budget").notNull().default({}),
    /** Free-form structured constraints (e.g. {maxNodes:8, allowedRelations:[...]}) */
    constraints: jsonb("constraints").notNull().default({}),
    /** "active" | "archived" */
    status: text("status").notNull().default("active"),
    createdBy: text("created_by").notNull().default("system"),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => ({
    byStatus: index("tool_goals_status_idx").on(t.status),
  }),
);

/**
 * tool_goal_runs — one row per actual search invocation against a goal.
 * Owns candidate subgraphs and the structured run log.
 */
export const toolGoalRuns = pgTable(
  "tool_goal_runs",
  {
    id: text("id").primaryKey(),
    goalId: text("goal_id")
      .notNull()
      .references(() => toolGoals.id, { onDelete: "cascade" }),
    /** "running" | "completed" | "failed" | "cancelled" */
    status: text("status").notNull().default("running"),
    /** Snapshot of the GoalSpec at run-start so later edits to the
     *  parent Goal don't retroactively change what was searched. */
    goalSnapshot: jsonb("goal_snapshot").notNull().default({}),
    /** Best metric observed in this run (numeric). */
    bestMetric: doublePrecision("best_metric"),
    /** Candidate id of the winner (FK lookup against tool_goal_candidates). */
    bestCandidateId: text("best_candidate_id"),
    iterations: integer("iterations").notNull().default(0),
    candidatesEvaluated: integer("candidates_evaluated").notNull().default(0),
    error: text("error"),
    startedAt: timestamp("started_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    finishedAt: timestamp("finished_at", { withTimezone: true }),
  },
  (t) => ({
    byGoal: index("tool_goal_runs_goal_idx").on(t.goalId),
    byStatus: index("tool_goal_runs_status_idx").on(t.status),
  }),
);

/**
 * tool_goal_candidates — every candidate subgraph the search loop
 * produced, with its primitive lineage and evaluator score. Subgraph
 * is stored as a list of node ids + edge tuples (the live graph rows
 * remain the source of truth; candidate rows reference them by id).
 */
export const toolGoalCandidates = pgTable(
  "tool_goal_candidates",
  {
    id: text("id").primaryKey(),
    runId: text("run_id")
      .notNull()
      .references(() => toolGoalRuns.id, { onDelete: "cascade" }),
    /** Generation index within the run (0-based). */
    generation: integer("generation").notNull().default(0),
    /** "expand" | "compose" | "replace" | "tune" | "seed" */
    primitive: text("primitive").notNull(),
    /** Optional parent candidate id (the subgraph this was derived from). */
    parentCandidateId: text("parent_candidate_id"),
    /** {nodeIds: string[], edges: Array<{from,to,relation}>} */
    subgraph: jsonb("subgraph").notNull(),
    /** Contract validation pass/fail aggregate over the candidate's edges. */
    contractOk: integer("contract_ok").notNull().default(1),
    contractIssues: jsonb("contract_issues").notNull().default([]),
    /** Numeric metric from the evaluator (null when not evaluated). */
    metric: doublePrecision("metric"),
    /** Anything the evaluator returned beyond the metric. */
    evaluatorPayload: jsonb("evaluator_payload"),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => ({
    byRun: index("tool_goal_candidates_run_idx").on(t.runId),
    byMetric: index("tool_goal_candidates_metric_idx").on(t.metric),
  }),
);

/**
 * tool_promotion_audit — append-only audit of every status transition
 * driven by the auto-promotion gate or the rollback action. One row
 * per transition. The `evidenceSnapshot` payload captures whatever the
 * promoter saw at decision time so an outside reviewer can reconstruct
 * the call.
 */
export const toolPromotionAudit = pgTable(
  "tool_promotion_audit",
  {
    id: text("id").primaryKey(),
    nodeId: text("node_id").notNull(),
    /** "auto_promote" | "rollback" | "manual_promote" */
    action: text("action").notNull(),
    fromStatus: text("from_status").notNull(),
    toStatus: text("to_status").notNull(),
    actor: text("actor").notNull().default("system"),
    /** Free-form snapshot of metrics/evidence that justified the action. */
    evidenceSnapshot: jsonb("evidence_snapshot").notNull().default({}),
    /** Optional candidate / run ids when the transition came out of Mode B. */
    runId: text("run_id"),
    candidateId: text("candidate_id"),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => ({
    byNode: index("tool_promotion_audit_node_idx").on(t.nodeId),
    byAction: index("tool_promotion_audit_action_idx").on(t.action),
  }),
);

export type ToolGoalRow = typeof toolGoals.$inferSelect;
export type InsertToolGoalRow = typeof toolGoals.$inferInsert;
export type ToolGoalRunRow = typeof toolGoalRuns.$inferSelect;
export type InsertToolGoalRunRow = typeof toolGoalRuns.$inferInsert;
export type ToolGoalCandidateRow = typeof toolGoalCandidates.$inferSelect;
export type InsertToolGoalCandidateRow = typeof toolGoalCandidates.$inferInsert;
export type ToolPromotionAuditRow = typeof toolPromotionAudit.$inferSelect;
export type InsertToolPromotionAuditRow = typeof toolPromotionAudit.$inferInsert;