catalogues.ts

import type { SupabaseClient } from "@supabase/supabase-js";
import { DEFAULT_SETTING_BLOBS, parseSettingsJson } from "@/lib/settings-value";

/**
 * Framework catalogues the compliance modules score against.
 *
 * These are product reference data, not sample data. ISMS, AIMS and AI Verify
 * exist to assess an estate against these clauses — with an empty catalogue the
 * modules render "0/0 controls (0%)" and the audit dashboard reports that the
 * criteria catalogue is missing, which reads as a broken install.
 *
 * They briefly lived in supabase/seed.sql after the seed/schema split. That was
 * the wrong side of the line: the split is meant to separate *structure* from
 * *sample rows*, and a framework clause list is neither — it is part of what the
 * product is. Provisioned idempotently by the app so every install has it, in
 * every deployment mode, without anyone having to run a seed file.
 */

/**
 * ISO/IEC 27001:2022 Annex A — all 93 controls.
 *
 * The catalogue used to hold only the 20 controls the automated scanners could
 * evidence, which quietly redefined the standard as "the part we implemented".
 * An operator opening the ISMS module saw a 20-control Annex A and had no way
 * to know the other 73 existed. A management system has to show the whole
 * standard and be honest about which parts a machine can answer: see
 * AUTO_EVIDENCED_CONTROLS below.
 */
const ISMS_CONTROLS: Array<[string, string, string, string]> = [
  // Organizational controls (37)
  ["A.5.1", "Organizational", "Policies for information security", "Management-direction policies covering humans and digital employees alike."],
  ["A.5.2", "Organizational", "Information security roles and responsibilities", "Named ownership for security, including who is accountable for each agent."],
  ["A.5.3", "Organizational", "Segregation of duties", "No single person or agent can both propose and approve a privileged change."],
  ["A.5.4", "Organizational", "Management responsibilities", "Management requires everyone, and every agent, to apply the policy."],
  ["A.5.5", "Organizational", "Contact with authorities", "Route to regulators and law enforcement is defined before it is needed."],
  ["A.5.6", "Organizational", "Contact with special interest groups", "Membership of security and AI-governance forums for early warning."],
  ["A.5.7", "Organizational", "Threat intelligence", "Threat information is collected and fed into controls, including prompt-injection and model threats."],
  ["A.5.8", "Organizational", "Information security in project management", "Security requirements are set at project start, not retrofitted."],
  ["A.5.9", "Organizational", "Inventory of information and other associated assets", "A register of assets, including every digital employee and its workspace."],
  ["A.5.10", "Organizational", "Acceptable use of information and other associated assets", "Rules for how people and agents may use company information."],
  ["A.5.11", "Organizational", "Return of assets", "Assets and access are recovered when people leave or agents are retired."],
  ["A.5.12", "Organizational", "Classification of information", "Information is classified so protection matches sensitivity."],
  ["A.5.13", "Organizational", "Labelling of information", "Classification is visible on the information itself."],
  ["A.5.14", "Organizational", "Information transfer", "Rules for transferring information inside and outside the organisation."],
  ["A.5.15", "Organizational", "Access control", "Who and what may reach each system, including agent API keys."],
  ["A.5.16", "Organizational", "Identity management", "Every human and every agent has its own distinct identity."],
  ["A.5.17", "Organizational", "Authentication information", "Allocation, rotation and protection of secrets, including agent keys."],
  ["A.5.18", "Organizational", "Access rights", "Access is granted, reviewed and revoked on a defined cycle."],
  ["A.5.19", "Organizational", "Information security in supplier relationships", "Security expectations of model providers and other suppliers."],
  ["A.5.20", "Organizational", "Addressing information security within supplier agreements", "Contracts with suppliers carry the security terms."],
  ["A.5.21", "Organizational", "Managing information security in the ICT supply chain", "Risk from upstream components, packages and model weights."],
  ["A.5.22", "Organizational", "Monitoring, review and change management of supplier services", "Supplier service changes are reviewed, not absorbed silently."],
  ["A.5.23", "Organizational", "Information security for use of cloud services", "Cloud versus on-device decisions and the subprocessor register."],
  ["A.5.24", "Organizational", "Information security incident management planning and preparation", "An incident process exists before the first incident."],
  ["A.5.25", "Organizational", "Assessment and decision on information security events", "Events are triaged into incidents on defined criteria."],
  ["A.5.26", "Organizational", "Response to information security incidents", "Incidents are responded to per documented procedure."],
  ["A.5.27", "Organizational", "Learning from information security incidents", "Incidents change the controls afterwards."],
  ["A.5.28", "Organizational", "Collection of evidence", "Evidence is collected in a form that survives scrutiny."],
  ["A.5.29", "Organizational", "Information security during disruption", "Security is maintained while the business is disrupted."],
  ["A.5.30", "Organizational", "ICT readiness for business continuity", "ICT can meet the continuity objectives."],
  ["A.5.31", "Organizational", "Legal, statutory, regulatory and contractual requirements", "Applicable law is identified and met, including PDPA and AI regulation."],
  ["A.5.32", "Organizational", "Intellectual property rights", "IP in data, models and generated output is respected."],
  ["A.5.33", "Organizational", "Protection of records", "Records are protected from loss and falsification."],
  ["A.5.34", "Organizational", "Privacy and protection of PII", "Personal data is protected, including personal data reaching agent memory."],
  ["A.5.35", "Organizational", "Independent review of information security", "Security is reviewed independently at planned intervals."],
  ["A.5.36", "Organizational", "Compliance with policies, rules and standards for information security", "Conformance is checked, not assumed."],
  ["A.5.37", "Organizational", "Documented operating procedures", "Operating procedures are written down and available."],
  // People controls (8)
  ["A.6.1", "People", "Screening", "Background verification before access is granted."],
  ["A.6.2", "People", "Terms and conditions of employment", "Security responsibilities are in the employment terms."],
  ["A.6.3", "People", "Information security awareness, education and training", "People are trained, including on safe use of AI systems."],
  ["A.6.4", "People", "Disciplinary process", "A defined consequence for policy violation."],
  ["A.6.5", "People", "Responsibilities after termination or change of employment", "Obligations that survive the role change."],
  ["A.6.6", "People", "Confidentiality or non-disclosure agreements", "NDAs are in place and current."],
  ["A.6.7", "People", "Remote working", "Security for work performed away from the premises."],
  ["A.6.8", "People", "Information security event reporting", "A channel for people to report events quickly."],
  // Physical controls (14)
  ["A.7.1", "Physical", "Physical security perimeters", "Perimeters protect areas holding information and assets."],
  ["A.7.2", "Physical", "Physical entry", "Entry to secure areas is controlled."],
  ["A.7.3", "Physical", "Securing offices, rooms and facilities", "Physical protection of working areas."],
  ["A.7.4", "Physical", "Physical security monitoring", "Premises are monitored for unauthorised access."],
  ["A.7.5", "Physical", "Protecting against physical and environmental threats", "Protection from fire, flood and similar threats."],
  ["A.7.6", "Physical", "Working in secure areas", "Rules for working inside secure areas."],
  ["A.7.7", "Physical", "Clear desk and clear screen", "Screen lock and desk hygiene on devices running digital employees."],
  ["A.7.8", "Physical", "Equipment siting and protection", "Equipment is placed to reduce risk."],
  ["A.7.9", "Physical", "Security of assets off-premises", "Protection of assets taken outside."],
  ["A.7.10", "Physical", "Storage media", "Media is managed through its whole life."],
  ["A.7.11", "Physical", "Supporting utilities", "Power and cooling failures do not compromise security."],
  ["A.7.12", "Physical", "Cabling security", "Power and data cabling is protected."],
  ["A.7.13", "Physical", "Equipment maintenance", "Equipment is maintained so it stays available and secure."],
  ["A.7.14", "Physical", "Secure disposal or re-use of equipment", "Data is removed before disposal or reuse."],
  // Technological controls (34)
  ["A.8.1", "Technological", "User endpoint devices", "Operator workstations running the Workforce and its digital employees."],
  ["A.8.2", "Technological", "Privileged access rights", "Local administrator and elevated rights are restricted."],
  ["A.8.3", "Technological", "Information access restriction", "File-level restrictions, including credential file permissions."],
  ["A.8.4", "Technological", "Access to source code", "Read and write access to source and agent instructions is controlled."],
  ["A.8.5", "Technological", "Secure authentication", "Authentication is strong enough for what it protects."],
  ["A.8.6", "Technological", "Capacity management", "Resources are sized for demand, including model and compute quota."],
  ["A.8.7", "Technological", "Protection against malware", "Endpoint malware protection and platform integrity."],
  ["A.8.8", "Technological", "Management of technical vulnerabilities", "Pending operating system and package security updates."],
  ["A.8.9", "Technological", "Configuration management", "Configuration is defined, applied and monitored for drift."],
  ["A.8.10", "Technological", "Information deletion", "Retention and deletion of agent memory, conversations and documents."],
  ["A.8.11", "Technological", "Data masking", "Sensitive data is masked where full values are not needed."],
  ["A.8.12", "Technological", "Data leakage prevention", "Controls against data leaving, including through prompts and tool calls."],
  ["A.8.13", "Technological", "Information backup", "Backup coverage and recency for the device and its data."],
  ["A.8.14", "Technological", "Redundancy of information processing facilities", "Availability requirements are met by redundancy."],
  ["A.8.15", "Technological", "Logging", "Audit trail of agent actions and administrative changes."],
  ["A.8.16", "Technological", "Monitoring activities", "Networks, systems and digital-employee heartbeats are monitored."],
  ["A.8.17", "Technological", "Clock synchronization", "Clocks agree so logs can be correlated."],
  ["A.8.18", "Technological", "Use of privileged utility programs", "Utilities that can override controls are restricted."],
  ["A.8.19", "Technological", "Installation of software on operational systems", "Software installed on devices running digital employees."],
  ["A.8.20", "Technological", "Networks security", "Host firewall posture on devices running digital employees."],
  ["A.8.21", "Technological", "Security of network services", "Listening services exposed beyond loopback."],
  ["A.8.22", "Technological", "Segregation of networks", "Networks are separated by trust level."],
  ["A.8.23", "Technological", "Web filtering", "Access to external sites is filtered where required."],
  ["A.8.24", "Technological", "Use of cryptography", "Disk encryption and at-rest protection of secrets."],
  ["A.8.25", "Technological", "Secure development life cycle", "Security is built into how software and agents are developed."],
  ["A.8.26", "Technological", "Application security requirements", "Security requirements are specified for applications."],
  ["A.8.27", "Technological", "Secure system architecture and engineering principles", "Systems are engineered to secure principles."],
  ["A.8.28", "Technological", "Secure coding", "Code is written to secure coding standards."],
  ["A.8.29", "Technological", "Security testing in development and acceptance", "Security is tested before release."],
  ["A.8.30", "Technological", "Outsourced development", "Outsourced work meets the same security bar."],
  ["A.8.31", "Technological", "Separation of development, test and production environments", "Environments are separated."],
  ["A.8.32", "Technological", "Change management", "Changes to facilities and to agent SOUL baselines are controlled."],
  ["A.8.33", "Technological", "Test information", "Test data is selected and protected."],
  ["A.8.34", "Technological", "Protection of information systems during audit testing", "Audit activity does not disrupt production."],
];

/**
 * ISO/IEC 42001:2023 Annex A — all 38 controls, plus the three management-system
 * clause references the device and agent scanners already cite.
 *
 * The clause-level ids (AIMS-x.y) are kept because existing device checks and
 * audit observations reference them; removing them would orphan evidence that
 * has already been collected.
 */
const AIMS_CONTROLS: Array<[string, string, string]> = [
  ["AIMS-4.5", "AI computing and data resources", "Compute and platform resources the AI systems run on are documented."],
  ["AIMS-6.1", "Actions to address risks and opportunities", "Risks and opportunities for the AI management system are addressed."],
  ["AIMS-6.3", "AI system change management", "Changes to AI system instructions require documented approval."],
  ["AIMS-8.4", "AI system monitoring", "Performance and behaviour of AI systems are monitored continuously."],

  // Annex A controls (38)
  ["A.2.2", "AI policy", "A documented AI policy exists and is approved by management."],
  ["A.2.3", "Alignment with other organizational policies", "The AI policy is consistent with security, privacy and quality policies."],
  ["A.2.4", "Review of the AI policy", "The AI policy is reviewed at planned intervals."],
  ["A.3.2", "AI roles and responsibilities", "Roles for the AI management system are assigned and communicated."],
  ["A.3.3", "Reporting of concerns", "A route exists to raise concerns about an AI system."],
  ["A.4.2", "Resource documentation", "Resources needed for AI systems are identified and documented."],
  ["A.4.3", "Data resources", "Data resources used by AI systems are documented."],
  ["A.4.4", "Tooling resources", "Tools, connectors and MCP servers used by AI systems are documented."],
  ["A.4.5", "System and computing resources", "Compute and platform resources the AI systems run on are documented."],
  ["A.4.6", "Human resources", "Human competence required to operate the AI systems is defined."],
  ["A.5.2", "AI system impact assessment process", "A defined process for assessing AI system impact."],
  ["A.5.3", "Documentation of AI system impact assessments", "Impact assessments are recorded and retained."],
  ["A.5.4", "Assessing AI system impact on individuals or groups", "Impact on individuals and groups is assessed."],
  ["A.5.5", "Assessing societal impacts of AI systems", "Broader societal impact is assessed."],
  ["A.6.1.2", "AI system life cycle objectives", "Objectives for responsible development across the life cycle."],
  ["A.6.1.3", "Processes for responsible AI system design and development", "Defined processes for responsible design and development."],
  ["A.6.2.2", "AI system requirements and specification", "Requirements are specified before build."],
  ["A.6.2.3", "Documentation of AI system design and development", "Design and development decisions are documented."],
  ["A.6.2.4", "AI system verification and validation", "The system is verified and validated against its requirements."],
  ["A.6.2.5", "AI system deployment", "Deployment follows a controlled process."],
  ["A.6.2.6", "AI system operation and monitoring", "Operation and behaviour are monitored continuously."],
  ["A.6.2.7", "AI system technical documentation", "Technical documentation is produced and kept current."],
  ["A.6.2.8", "AI system recording of event logs", "Event logs are recorded and retained."],
  ["A.7.2", "Data for development and enhancement of AI system", "Data used to build and improve the system is managed."],
  ["A.7.3", "Acquisition of data", "Data acquisition is lawful and documented."],
  ["A.7.4", "Quality of data for AI systems", "Data quality is defined and checked."],
  ["A.7.5", "Data provenance", "Origin and lineage of data are recorded."],
  ["A.7.6", "Data preparation", "Data preparation methods are documented."],
  ["A.8.2", "System documentation and information for users", "Users are given the documentation they need."],
  ["A.8.3", "External reporting", "A channel exists for external parties to report."],
  ["A.8.4", "Communication of incidents", "AI incidents are communicated to those who need to know."],
  ["A.8.5", "Information for interested parties", "Interested parties receive the information they are owed."],
  ["A.9.2", "Processes for responsible use of AI systems", "Defined processes govern how the systems are used."],
  ["A.9.3", "Objectives for responsible use of AI system", "Responsible-use objectives are set."],
  ["A.9.4", "Intended use of the AI system", "Intended use is defined; use outside it is out of scope."],
  ["A.10.2", "Allocating responsibilities", "Responsibility is allocated across the AI value chain."],
  ["A.10.3", "Suppliers", "Supplier obligations for AI systems are set and checked."],
  ["A.10.4", "Customers", "Customer-facing obligations for AI systems are met."],
];

/**
 * Controls the product can evidence by itself, from the device scan and the
 * continuous audit engine.
 *
 * Everything else needs a person. Marking the difference is the point: a
 * compliance module that renders 131 controls with no indication of which ones
 * a machine is watching invites the operator to assume all of them are covered.
 */
export const AUTO_EVIDENCED_CONTROLS: ReadonlySet<string> = new Set([
  // Device scan
  "A.5.15", "A.5.17", "A.5.23", "A.7.7", "A.8.1", "A.8.2", "A.8.3",
  "A.8.7", "A.8.8", "A.8.10", "A.8.13", "A.8.15", "A.8.19", "A.8.20",
  "A.8.21", "A.8.24", "A.8.32",
  // Continuous audit engine
  "A.5.2", "A.5.25", "A.5.26", "A.8.16",
  "AIMS-4.5", "AIMS-6.1", "AIMS-6.3", "AIMS-8.4",
]);

/** MGF 2020 — Model AI Governance Framework (PDPC), as adopted by AI Verify. */
const MGF_PRINCIPLES: Array<[string, string, string]> = [
  ["MGF-01", "Transparency", "Provide appropriate information to individuals about when and how AI is used in decisions that affect them."],
  ["MGF-02", "Explainability", "Decisions and behaviour of the AI system can be explained to the people who operate it and are affected by it."],
  ["MGF-03", "Repeatability / Reproducibility", "The AI system produces consistent results under the same conditions and its results can be reproduced for review."],
  ["MGF-04", "Safety", "The AI system does not cause harm; impact and risk assessments are performed and known limitations are documented."],
  ["MGF-05", "Security", "The AI system and its data are protected against unauthorised access, poisoning and adversarial attack."],
  ["MGF-06", "Robustness", "The AI system remains reliable under unexpected inputs and stressful or changing conditions."],
  ["MGF-07", "Fairness", "The AI system does not systematically disadvantage individuals or groups; bias in data and model is assessed and mitigated."],
  ["MGF-08", "Data Governance", "The provenance, quality, lineage and lawful basis of data used to train and operate the AI system are managed."],
  ["MGF-09", "Accountability", "Clear internal ownership: a named function is responsible for the AI system across its lifecycle."],
  ["MGF-10", "Human Agency and Oversight", "The appropriate level of human involvement (human-in/over/out-of-the-loop) is determined and enforced."],
  ["MGF-11", "Inclusive Growth, Societal and Environmental Well-being", "Deployment considers broader societal impact, inclusion, and environmental cost."],
];

/** GenAI 2024 — Model AI Governance Framework for Generative AI. */
const GENAI_DIMENSIONS: Array<[string, string, string]> = [
  ["GEN-01", "Accountability", "Allocate responsibility across the AI supply chain (model developer, deployer, application provider) with proper incentives."],
  ["GEN-02", "Data", "Ensure data quality and address contentious training data (personal data, copyright) in a transparent way."],
  ["GEN-03", "Trusted Development and Deployment", "Apply development best practices, disclosure of capabilities/limitations, and baseline safety evaluation."],
  ["GEN-04", "Incident Reporting", "Establish channels and processes to report, triage and remediate AI incidents and vulnerabilities."],
  ["GEN-05", "Testing and Assurance", "Third-party testing and assurance against common standards to build trust."],
  ["GEN-06", "Security", "Address generative-AI-specific threat vectors: prompt injection, model theft, data leakage through prompts."],
  ["GEN-07", "Content Provenance", "Make AI-generated content identifiable (watermarking, provenance metadata) to counter misinformation."],
  ["GEN-08", "Safety and Alignment R&D", "Invest in and track alignment research so model behaviour matches human intention."],
  ["GEN-09", "AI for Public Good", "Steer AI use toward public benefit: access, upskilling, and sustainable deployment."],
];

/** Agentic AI — governance pillars for autonomous agents acting on the business. */
const AGENTIC_PILLARS: Array<[string, string, string]> = [
  ["AGT-01", "Goal Alignment and Task Boundaries", "Each agent has a documented mandate (SOUL baseline); tasks outside the mandate are refused or escalated."],
  ["AGT-02", "Human Oversight and HITL Gates", "High-impact actions (instruction changes, payments, external commitments) require human approval before execution."],
  ["AGT-03", "Agent Identity and Access Management", "Every agent has its own credentials with least privilege, expiry and rotation; no shared or non-expiring keys."],
  ["AGT-04", "Action Logging and Auditability", "Every agent action is recorded in an immutable trail attributable to the agent and reviewable by an auditor."],
  ["AGT-05", "Tool and API Permission Scoping", "Tools, connectors and MCP servers available to an agent are explicitly allow-listed per role."],
  ["AGT-06", "Memory and Data Governance", "Agent memory respects retention policy; personal data in memory is minimised and erasable (PDPA)."],
  ["AGT-07", "Failure Containment and Rollback", "Agent malfunction can be contained: pause/kill switch, bounded blast radius, and rollback of applied changes."],
  ["AGT-08", "Multi-Agent Interaction Governance", "Delegation between agents preserves authorisation context; no privilege escalation through chained agents."],
];

/**
 * Ensure every framework catalogue exists. Idempotent, and it never overwrites
 * an operator's own edits: rows are inserted only when the id is absent, so
 * implementation status, evidence and ownership survive every boot.
 */
export async function ensureComplianceCatalogues(db: SupabaseClient): Promise<void> {
  await upsertMissing(db, "isms_controls", "control_id", ISMS_CONTROLS.map(
    ([control_id, category, title, description]) => ({
      control_id, category, title, description, implementation_status: "not_started",
    }),
  ));

  await upsertMissing(db, "aims_controls", "control_id", AIMS_CONTROLS.map(
    ([control_id, title, description]) => ({
      control_id, title, description, implementation_status: "not_started",
    }),
  ));

  // The AI Verify catalogues share a shape: a catalogue row is the one with a
  // null assessment_id; per-assessment copies hang off an assessment.
  await upsertCriteria(db, "ai_verify_mgf_principles", "principle_id", "principle_name", MGF_PRINCIPLES);
  await upsertCriteria(db, "ai_verify_genai_dimensions", "dimension_id", "dimension_name", GENAI_DIMENSIONS);
  await upsertCriteria(db, "ai_verify_agentic_pillars", "pillar_id", "pillar_name", AGENTIC_PILLARS);
}

async function upsertMissing(
  db: SupabaseClient,
  table: string,
  idColumn: string,
  rows: Array<Record<string, unknown>>,
): Promise<void> {
  const { data: existing, error } = await db.from(table).select(idColumn);
  if (error) throw new Error(`${table}: ${error.message}`);
  const have = new Set(
    (existing ?? []).map((r) => (r as unknown as Record<string, string>)[idColumn]),
  );
  const missing = rows.filter((r) => !have.has(r[idColumn] as string));
  if (!missing.length) return;
  const { error: insertError } = await db.from(table).insert(missing);
  if (insertError) throw new Error(`${table}: ${insertError.message}`);
}

async function upsertCriteria(
  db: SupabaseClient,
  table: string,
  idColumn: string,
  nameColumn: string,
  rows: Array<[string, string, string]>,
): Promise<void> {
  const { data: existing, error } = await db
    .from(table)
    .select(idColumn)
    .is("assessment_id", null);
  if (error) throw new Error(`${table}: ${error.message}`);
  const have = new Set(
    (existing ?? []).map((r) => (r as unknown as Record<string, string>)[idColumn]),
  );
  const missing = rows
    .filter(([id]) => !have.has(id))
    .map(([id, name, description]) => ({
      [idColumn]: id,
      [nameColumn]: name,
      title: name,
      description,
    }));
  if (!missing.length) return;
  const { error: insertError } = await db.from(table).insert(missing);
  if (insertError) throw new Error(`${table}: ${insertError.message}`);
}

/**
 * Operating parameters the Settings page edits.
 *
 * These lived only in `seed.sql`, which is documented as optional and is never
 * applied automatically — so on every install, cloud or embedded, the Company /
 * Security / Compliance / PDPA tabs rendered "No settings for this category
 * yet." and there was no way to reach the values from the product at all.
 *
 * Stored as one JSON blob per domain, matching the shape the retention monitor
 * and the compliance pages already read. Inserted only when absent, so an
 * operator's values are never reset.
 */
const DEFAULT_SETTINGS: Array<[string, Record<string, unknown>, string]> =
  Object.entries(DEFAULT_SETTING_BLOBS).map(([key, { value, description }]) => [
    key,
    value,
    description,
  ]);

/**
 * Ensure the editable operating parameters exist. Safe on every boot: a key
 * that is already present is left exactly as the operator left it.
 */
export async function ensureDefaultSettings(db: SupabaseClient): Promise<void> {
  const { data: existing, error } = await db
    .from("settings")
    .select("key, value")
    .eq("category", "general");
  if (error) throw new Error(`settings: ${error.message}`);

  const rows = (existing ?? []) as Array<{ key: string; value: unknown }>;
  const have = new Map(rows.map((r) => [r.key, r.value]));

  const row = (key: string, value: Record<string, unknown>, description: string) => ({
    category: "general",
    key,
    // `settings.value` is a text column since migration 023. Handing the
    // driver a live object leaves the encoding to whatever sits underneath —
    // PostgREST in cloud mode, a bound PGlite parameter in embedded mode —
    // and a JS object coerced to text lands as "[object Object]", which no
    // reader can parse back. Serialise it here so both modes store the same
    // bytes.
    value: JSON.stringify(value),
    description,
  });

  const missing = DEFAULT_SETTINGS.filter(([key]) => !have.has(key)).map(([k, v, d]) =>
    row(k, v, d),
  );

  // Installs provisioned before the serialisation fix already hold the literal
  // "[object Object]". That text carries none of the operator's values — there
  // is nothing to preserve and nothing any reader can do with it — so repair it
  // here rather than making every operator find and re-save four tabs. A value
  // that still parses is left strictly alone, so real settings are never reset.
  const corrupt = DEFAULT_SETTINGS.filter(
    ([key]) => have.has(key) && parseSettingsJson(have.get(key)) === null,
  ).map(([k, v, d]) => row(k, v, d));

  if (!missing.length && !corrupt.length) return;

  if (missing.length) {
    const { error: insertError } = await db.from("settings").insert(missing);
    if (insertError) throw new Error(`settings: ${insertError.message}`);
  }
  if (corrupt.length) {
    const { error: repairError } = await db
      .from("settings")
      .upsert(corrupt, { onConflict: "category,key" });
    if (repairError) throw new Error(`settings repair: ${repairError.message}`);
  }
}

/**
 * Record the deployment mode so the runtime pages can name it.
 *
 * The setup wizard writes this row, but embedded installs never run the wizard
 * — there is nothing to configure — so the Local runtime page reported
 * "Database mode: Unknown" on a perfectly healthy install.
 */
export async function ensureDeploymentRecord(
  db: SupabaseClient,
  mode: string,
  note: string,
): Promise<void> {
  const { error } = await db.from("settings").upsert(
    [
      { category: "deployment", key: "supabase_deployment", value: mode, description: "Supabase deployment mode" },
      { category: "deployment", key: "data_residency_note", value: note, description: "Data residency note" },
    ],
    { onConflict: "category,key" },
  );
  if (error) throw new Error(`deployment record: ${error.message}`);
}