import { randomUUID } from "node:crypto";
import { connectSearch } from "./examples/customer-owned-search/store.mjs";

// session, applicationPolicy, provisionedPipelineId and permittedImagePath
// come from your existing backend/auth/permission flow, not untrusted claims.
const search = await connectSearch({
  supabaseUrl: process.env.SUPABASE_URL,
  publishableKey: process.env.SUPABASE_PUBLISHABLE_KEY,
  userAccessToken: session.access_token,
  authorize: applicationPolicy,
});
const subjectId = randomUUID();
await search.createSubject(subjectId);
// Persist this identifier before starting; reuse it to inspect the same job.
const enrollmentId = randomUUID();
const enrollment = await search.enroll({
  subjectId, enrollmentId, pipelineId: provisionedPipelineId,
  imagePath: permittedImagePath,
});
const candidates = await search.search({
  pipelineId: provisionedPipelineId, imagePath: permittedQueryImagePath,
  maxDistance: evaluatedCosineThreshold, limit: 5,
});
// candidates.outcome can be "no_acceptable_match". Never identify the nearest
// subject by default or log images, embeddings, tokens or subject mappings.
// An authorized retention/deletion action removes this subject and its vectors:
await search.deleteSubject(subjectId);

node scripts/test-customer-search-db.mjs

-- Example for a fresh customer-owned Supabase sandbox, never a deepface.dev migration.
begin;
create schema if not exists extensions;
create extension if not exists vector with schema extensions;

-- One authenticated Supabase user is one tenant in this example.
-- Provision pipelines as the database owner after verifying model/settings/revision.
create table public.face_search_pipelines (
  tenant_id uuid not null references auth.users(id) on delete cascade,
  pipeline_id uuid not null,
  model_name text not null check (length(model_name) between 1 and 200),
  provider_revision text not null check (length(provider_revision) between 1 and 200),
  detector_backend text not null,
  detector_policy text not null check (length(detector_policy) between 1 and 500),
  align boolean not null,
  normalization text not null,
  dimensions integer not null check (dimensions between 1 and 2000),
  primary key (tenant_id, pipeline_id)
);
create table public.face_search_subjects (
  tenant_id uuid not null default auth.uid() references auth.users(id) on delete cascade,
  subject_id uuid not null,
  created_at timestamptz not null default now(),
  primary key (tenant_id, subject_id)
);
create table public.face_search_enrollments (
  tenant_id uuid not null default auth.uid(),
  enrollment_id uuid not null,
  subject_id uuid not null,
  pipeline_id uuid not null,
  status text not null default 'pending' check (status in ('pending', 'complete')),
  embedding extensions.vector,
  compute_request_id text check (length(compute_request_id) between 1 and 128),
  created_at timestamptz not null default now(),
  primary key (tenant_id, enrollment_id),
  foreign key (tenant_id, subject_id) references public.face_search_subjects on delete cascade,
  foreign key (tenant_id, pipeline_id) references public.face_search_pipelines on delete cascade,
  check ((status = 'pending' and embedding is null) or
         (status = 'complete' and embedding is not null and compute_request_id is not null))
);
create index face_search_scope on public.face_search_enrollments (tenant_id, pipeline_id, status);

alter table public.face_search_pipelines enable row level security;
alter table public.face_search_pipelines force row level security;
alter table public.face_search_subjects enable row level security;
alter table public.face_search_subjects force row level security;
alter table public.face_search_enrollments enable row level security;
alter table public.face_search_enrollments force row level security;
create policy pipeline_read on public.face_search_pipelines for select to authenticated
  using ((select auth.uid()) is not null and tenant_id = (select auth.uid()));
create policy subject_access on public.face_search_subjects for all to authenticated
  using ((select auth.uid()) is not null and tenant_id = (select auth.uid()))
  with check ((select auth.uid()) is not null and tenant_id = (select auth.uid()));
create policy enrollment_access on public.face_search_enrollments for all to authenticated
  using ((select auth.uid()) is not null and tenant_id = (select auth.uid()))
  with check ((select auth.uid()) is not null and tenant_id = (select auth.uid()));

-- Revoke Supabase defaults explicitly. No service-role client is used by the recipe.
revoke all on public.face_search_pipelines, public.face_search_subjects,
  public.face_search_enrollments from public, anon, authenticated;
grant usage on schema extensions to authenticated;
grant select on public.face_search_pipelines to authenticated;
grant select, insert, delete on public.face_search_subjects to authenticated;
grant select, insert on public.face_search_enrollments to authenticated;
grant update (status, embedding, compute_request_id) on public.face_search_enrollments to authenticated;

create function public.face_search_check_enrollment() returns trigger
language plpgsql security invoker set search_path = '' as $$
declare expected_dimensions integer;
begin
  if auth.uid() is null or new.tenant_id <> auth.uid() then
    raise exception 'tenant_unavailable' using errcode = '42501';
  end if;
  if tg_op = 'UPDATE' then
    if old.status = 'complete' or
       (old.tenant_id, old.enrollment_id, old.subject_id, old.pipeline_id) is distinct from
       (new.tenant_id, new.enrollment_id, new.subject_id, new.pipeline_id) or
       (old.compute_request_id is not null and old.compute_request_id is distinct from new.compute_request_id) then
      raise exception 'immutable_enrollment' using errcode = '22023';
    end if;
  elsif new.status <> 'pending' or new.embedding is not null or new.compute_request_id is not null then
    raise exception 'reserve_before_compute' using errcode = '22023';
  end if;
  select p.dimensions into expected_dimensions from public.face_search_pipelines p
    where p.tenant_id = new.tenant_id and p.pipeline_id = new.pipeline_id;
  if not found then raise exception 'pipeline_unavailable' using errcode = '22023'; end if;
  if new.embedding is not null and
    (extensions.vector_dims(new.embedding) <> expected_dimensions or extensions.vector_norm(new.embedding) = 0) then
    raise exception 'incompatible_embedding' using errcode = '22023';
  end if;
  return new;
end;
$$;
revoke all on function public.face_search_check_enrollment() from public, anon, authenticated;
create trigger validate_face_search_enrollment before insert or update
  on public.face_search_enrollments for each row execute function public.face_search_check_enrollment();

-- Exact search is intentional for this small worked example. No ANN recall claim.
create function public.face_search_match(
  p_pipeline_id uuid, p_query extensions.vector,
  p_max_distance double precision, p_limit integer default 5
) returns table (enrollment_id uuid, subject_id uuid, distance double precision)
language plpgsql stable security invoker set search_path = '' as $$
declare expected_dimensions integer;
begin
  if auth.uid() is null then raise exception 'authentication_required' using errcode = '42501'; end if;
  if p_max_distance is null or p_max_distance < 0 or p_max_distance > 2 then
    raise exception 'invalid_threshold' using errcode = '22023';
  end if;
  if p_limit is null or p_limit < 1 or p_limit > 20 then
    raise exception 'invalid_limit' using errcode = '22023';
  end if;
  select p.dimensions into expected_dimensions from public.face_search_pipelines p
    where p.tenant_id = auth.uid() and p.pipeline_id = p_pipeline_id;
  if not found then raise exception 'pipeline_unavailable' using errcode = '22023'; end if;
  if p_query is null or extensions.vector_dims(p_query) <> expected_dimensions or extensions.vector_norm(p_query) = 0 then
    raise exception 'incompatible_embedding' using errcode = '22023';
  end if;
  return query select e.enrollment_id, e.subject_id,
    e.embedding operator(extensions.<=>) p_query as distance
    from public.face_search_enrollments e
    where e.tenant_id = auth.uid() and e.pipeline_id = p_pipeline_id and e.status = 'complete'
      and e.embedding operator(extensions.<=>) p_query <= p_max_distance
    order by e.embedding operator(extensions.<=>) p_query, e.enrollment_id
    limit p_limit;
end;
$$;
revoke all on function public.face_search_match(uuid, extensions.vector, double precision, integer)
  from public, anon, authenticated;
grant execute on function public.face_search_match(uuid, extensions.vector, double precision, integer) to authenticated;
commit;

import { readFile, writeFile } from "node:fs/promises";
import { randomUUID } from "node:crypto";
import { extname } from "node:path";

class ExampleError extends Error {
  constructor(code, details = {}) { super(code); this.details = details; }
}

export function getConfig(env = process.env) {
  if (!env.DEEPFACE_API_KEY?.trim()) throw new ExampleError("missing_api_key");
  let url;
  try { url = new URL(env.DEEPFACE_API_BASE_URL || "https://api.deepface.dev"); }
  catch { throw new ExampleError("invalid_api_base"); }
  const loopback = ["localhost", "127.0.0.1", "[::1]"].includes(url.hostname);
  if ((url.protocol !== "https:" && !(url.protocol === "http:" && loopback)) || url.username || url.password || url.search || url.hash) {
    throw new ExampleError("invalid_api_base");
  }
  return { base: url.href.replace(/\/$/, ""), key: env.DEEPFACE_API_KEY.trim(), model: env.DEEPFACE_MODEL };
}

export async function getModel(config) {
  try {
    const response = await fetch(`${config.base}/capabilities`, { redirect: "error", signal: AbortSignal.timeout(15_000) });
    if (!response.ok) throw new Error();
    const capabilities = await response.json();
    const model = config.model || capabilities.default_model;
    if (!capabilities.allowed_models?.includes(model)) throw new Error();
    return model;
  } catch { throw new ExampleError("capabilities_or_model_unavailable"); }
}

export async function image(path) {
  const type = { ".jpg": "image/jpeg", ".jpeg": "image/jpeg", ".png": "image/png", ".webp": "image/webp" }[extname(path || "").toLowerCase()];
  if (!type) throw new ExampleError("provide_local_jpeg_png_or_webp");
  return new Blob([await readFile(path)], { type });
}

const finite = (value) => typeof value === "number" && Number.isFinite(value);
const object = (value) => value !== null && typeof value === "object" && !Array.isArray(value);
const named = (value) => typeof value === "string" && value.trim().length > 0;

function validResult(endpoint, data) {
  if (endpoint === "/verify") return object(data) && typeof data.verified === "boolean" && finite(data.distance) && finite(data.threshold);
  if (endpoint === "/represent") return Array.isArray(data) && data.length > 0 && data.every((face) => object(face) && named(face.model_name) && Array.isArray(face.embedding) && face.embedding.length > 0 && face.embedding.every(finite));
  if (endpoint !== "/compare" || !object(data)) return false;
  const threshold = data.threshold;
  const result = (item) => object(item) && Number.isInteger(item.index) && item.index >= 0 && finite(item.distance) && (threshold == null ? item.match === undefined : typeof item.match === "boolean");
  return named(data.model_name) && ["cosine", "euclidean", "euclidean_l2"].includes(data.metric)
    && (threshold == null || finite(threshold)) && object(data.source)
    && ["image", "vector"].includes(data.source.type) && Number.isInteger(data.source.vector_length) && data.source.vector_length > 0
    && Array.isArray(data.results) && data.results.length > 0 && data.results.every(result)
    && (data.best_match === undefined || result(data.best_match));
}

export async function compute(endpoint, options, config, send = fetch) {
  const requestId = randomUUID();
  let response, data;
  try {
    response = await send(`${config.base}${endpoint}`, {
      ...options, method: "POST", redirect: "error", signal: AbortSignal.timeout(120_000),
      headers: { ...options.headers, Authorization: `Bearer ${config.key}`, "x-request-id": requestId },
    });
    data = await response.json();
  } catch { throw new ExampleError("compute_outcome_unknown", { request_id: requestId, retryable: false }); }
  const echoedId = response.headers.get("x-request-id");
  const id = /^[a-zA-Z0-9_-]{1,128}$/.test(echoedId || "") ? echoedId : requestId;
  if (response.status === 202) throw new ExampleError("compute_outcome_unknown", { request_id: id, retryable: false });
  if (!response.ok || data?.outcome === "no_face_detected") {
    const code = typeof data?.error === "string" && /^[a-z0-9_]{1,80}$/.test(data.error) ? data.error : data?.outcome === "no_face_detected" ? "no_face_detected" : `http_${response.status}`;
    throw new ExampleError(code, { status: response.status, request_id: id, retryable: false });
  }
  const valid = validResult(endpoint, data);
  if (!valid) throw new ExampleError("compute_outcome_unknown", { request_id: id, retryable: false });
  return { request_id: id, data };
}

export function summarize({ request_id, data }) {
  if (Array.isArray(data)) return { request_id, outcome: "completed", faces: data.length, dimensions: data.map((face) => face.embedding.length) };
  if (typeof data.verified === "boolean") return { request_id, outcome: "completed", verified: data.verified, distance: typeof data.distance === "number" && Number.isFinite(data.distance) ? data.distance : undefined, threshold: typeof data.threshold === "number" && Number.isFinite(data.threshold) ? data.threshold : undefined };
  const summary = { request_id, outcome: "completed", comparisons: data.results.length, match_evaluation: data.threshold == null ? "not_requested" : "threshold_applied" };
  if (data.threshold != null) Object.assign(summary, { threshold: data.threshold, matches: data.results.filter((result) => result.match === true).length });
  return summary;
}

export async function saveResult(path, data) {
  // Explicit local export only. Refuse to overwrite an existing file.
  await writeFile(path, JSON.stringify(data), { flag: "wx", mode: 0o600 });
}

export async function run(main) {
  try { await main(); }
  catch (error) {
    console.error(JSON.stringify({ error: error instanceof ExampleError ? error.message : "local_input_or_output_error", ...(error instanceof ExampleError ? error.details : {}) }));
    process.exitCode = 1;
  }
}

import { createClient } from "@supabase/supabase-js";
import { getConfig, getModel, image, compute } from "../javascript/client.mjs";

export class SearchError extends Error {
  constructor(code, details = {}) { super(code); this.details = details; }
}
const uuid = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
function identifier(value) { if (typeof value !== "string" || !uuid.test(value)) throw new SearchError("invalid_identifier"); return value; }
function fail(error, code = "database_operation_failed", details = {}) { if (error) throw new SearchError(code, { ...details, retryable: false }); }

// Use a publishable key plus the authenticated owner's session, never service_role.
// Create a separate instance for each authenticated backend request.
export async function connectSearch({ supabaseUrl, publishableKey, userAccessToken, authorize }) {
  let url;
  try { url = new URL(supabaseUrl); } catch { throw new SearchError("invalid_supabase_url"); }
  const loopback = ["127.0.0.1", "localhost", "[::1]"].includes(url.hostname);
  if ((url.protocol !== "https:" && !(url.protocol === "http:" && loopback)) || url.username || url.password || url.search || url.hash) throw new SearchError("invalid_supabase_url");
  if (typeof publishableKey !== "string" || !publishableKey.startsWith("sb_publishable_")) throw new SearchError("publishable_key_required");
  if (typeof userAccessToken !== "string" || !userAccessToken) throw new SearchError("user_session_required");
  const db = createClient(url.href, publishableKey, {
    auth: { persistSession: false, autoRefreshToken: false, detectSessionInUrl: false },
    global: { headers: { Authorization: `Bearer ${userAccessToken}` }, fetch: (input, init) => fetch(input, { ...init, redirect: "error", signal: AbortSignal.timeout(30_000) }) },
  });
  return createSearchStore({ db, userAccessToken, authorize });
}

async function prepareRepresentation(imagePath, profile) {
  const config = getConfig();
  await getModel({ ...config, model: profile.model_name });
  const form = new FormData();
  form.set("img", await image(imagePath), "face");
  form.set("model_name", profile.model_name);
  form.set("detector_backend", profile.detector_backend);
  form.set("align", String(profile.align));
  form.set("normalization", profile.normalization);
  form.set("enforce_detection", "true");
  return () => compute("/represent", { body: form }, config);
}
function representation(result, profile) {
  const faces = result?.data;
  const vector = Array.isArray(faces) && faces.length === 1 ? faces[0]?.embedding : null;
  if (!vector || faces[0].model_name !== profile.model_name || vector.length !== profile.dimensions || !vector.every((n) => typeof n === "number" && Number.isFinite(n)) || !vector.some((n) => n !== 0)) {
    throw new SearchError("incompatible_representation", { request_id: result?.request_id, retryable: false });
  }
  return vector;
}

export async function createSearchStore({ db, userAccessToken, authorize, prepare = prepareRepresentation }) {
  if (typeof authorize !== "function") throw new SearchError("application_authorizer_required");
  const identity = await db.auth.getUser(userAccessToken);
  fail(identity.error, "user_session_required");
  const actorId = identifier(identity.data?.user?.id);
  const authorizeAction = async (action, subjectId) => {
    if (await authorize({ actorId, action, subjectId }) !== true) throw new SearchError("action_not_authorized");
  };
  const scoped = (table) => db.from(table).select("*").eq("tenant_id", actorId);
  async function profileFor(pipelineId) {
    const { data, error } = await scoped("face_search_pipelines").eq("pipeline_id", identifier(pipelineId)).single();
    fail(error, "pipeline_unavailable");
    if (!data || !data.provider_revision || !data.detector_policy || !Number.isInteger(data.dimensions) || data.dimensions < 1 || data.dimensions > 2000) throw new SearchError("pipeline_unavailable");
    return data;
  }
  async function existingEnrollment(enrollmentId, subjectId, pipelineId) {
    const { data, error } = await db.from("face_search_enrollments").select("enrollment_id,subject_id,pipeline_id,status").eq("tenant_id", actorId).eq("enrollment_id", enrollmentId).maybeSingle();
    fail(error);
    if (!data) return null;
    if (data.subject_id !== subjectId || data.pipeline_id !== pipelineId) throw new SearchError("enrollment_conflict");
    if (data.status !== "complete") throw new SearchError("enrollment_pending", { enrollment_id: enrollmentId, retryable: false });
    return { outcome: "already_enrolled", enrollment_id: enrollmentId };
  }
  return {
    async createSubject(subjectId) {
      identifier(subjectId); await authorizeAction("enroll", subjectId);
      const { error } = await db.from("face_search_subjects").insert({ tenant_id: actorId, subject_id: subjectId });
      fail(error); return { outcome: "subject_created", subject_id: subjectId };
    },
    async enroll({ subjectId, pipelineId, enrollmentId, imagePath }) {
      identifier(subjectId); identifier(pipelineId); identifier(enrollmentId);
      await authorizeAction("enroll", subjectId);
      const existing = await existingEnrollment(enrollmentId, subjectId, pipelineId);
      if (existing) return existing;
      const profile = await profileFor(pipelineId);
      const subject = await scoped("face_search_subjects").eq("subject_id", subjectId).maybeSingle();
      fail(subject.error, "subject_unavailable");
      if (!subject.data) throw new SearchError("subject_unavailable");
      const execute = await prepare(imagePath, profile); // Local/capability checks before reserving.
      const reservation = await db.from("face_search_enrollments").insert({ tenant_id: actorId, enrollment_id: enrollmentId, subject_id: subjectId, pipeline_id: pipelineId });
      if (reservation.error?.code === "23505") {
        const winner = await existingEnrollment(enrollmentId, subjectId, pipelineId);
        if (winner) return winner;
        throw new SearchError("reservation_outcome_unknown", { enrollment_id: enrollmentId, retryable: false });
      }
      fail(reservation.error, "reservation_outcome_unknown", { enrollment_id: enrollmentId });
      let result;
      try {
        result = await execute(); // Exactly one compute attempt after winning the reservation.
        const embedding = representation(result, profile);
        const saved = await db.from("face_search_enrollments").update({ embedding, status: "complete", compute_request_id: result.request_id }).eq("tenant_id", actorId).eq("enrollment_id", enrollmentId).eq("status", "pending").select("enrollment_id").single();
        fail(saved.error || !saved.data, "enrollment_persistence_unknown", { enrollment_id: enrollmentId, request_id: result.request_id });
        return { outcome: "enrolled", enrollment_id: enrollmentId, request_id: result.request_id };
      } catch (error) {
        const requestId = result?.request_id || error?.details?.request_id;
        if (typeof requestId === "string" && /^[a-zA-Z0-9_-]{1,128}$/.test(requestId)) {
          // Best-effort metadata only. Never replay inference to repair this write.
          await db.from("face_search_enrollments").update({ compute_request_id: requestId }).eq("tenant_id", actorId).eq("enrollment_id", enrollmentId).eq("status", "pending").then(() => {}, () => {});
        }
        throw error;
      }
    },
    async search({ pipelineId, imagePath, maxDistance, limit = 5 }) {
      await authorizeAction("search");
      if (typeof maxDistance !== "number" || !Number.isFinite(maxDistance) || maxDistance < 0 || maxDistance > 2) throw new SearchError("invalid_threshold");
      if (!Number.isInteger(limit) || limit < 1 || limit > 20) throw new SearchError("invalid_limit");
      const profile = await profileFor(pipelineId);
      const result = await (await prepare(imagePath, profile))();
      const vector = representation(result, profile);
      const { data, error } = await db.rpc("face_search_match", { p_pipeline_id: pipelineId, p_query: vector, p_max_distance: maxDistance, p_limit: limit });
      fail(error, "search_database_failed", { request_id: result.request_id });
      if (!Array.isArray(data) || data.length > limit || data.some((row) => !row || !uuid.test(row.enrollment_id || "") || !uuid.test(row.subject_id || "") || typeof row.distance !== "number" || !Number.isFinite(row.distance) || row.distance < 0 || row.distance > maxDistance)) throw new SearchError("search_database_failed", { request_id: result.request_id, retryable: false });
      const matches = data.map(({ enrollment_id, subject_id, distance }) => ({ enrollment_id, subject_id, distance }));
      return { outcome: matches.length ? "candidates_found" : "no_acceptable_match", request_id: result.request_id, matches };
    },
    async deleteSubject(subjectId) {
      identifier(subjectId); await authorizeAction("delete", subjectId);
      const { data, error } = await db.from("face_search_subjects").delete().eq("tenant_id", actorId).eq("subject_id", subjectId).select("subject_id");
      fail(error, "deletion_outcome_unknown");
      if (!Array.isArray(data)) throw new SearchError("deletion_outcome_unknown", { retryable: false });
      return { outcome: "subject_deleted", deleted: data.length }; // FK cascade removes pending and complete vectors.
    },
  };
}

-- Disposable runner only; never apply this fixture to a Supabase project.
-- Synthetic vectors only; auth.uid() is supplied by the disposable test harness.
insert into auth.users values ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa'), ('bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb');
insert into public.face_search_pipelines values
 ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa','11111111-1111-4111-8111-111111111111','fixture','synthetic-v1','fixture','synthetic-policy-v1',true,'fixture',3),
 ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa','22222222-2222-4222-8222-222222222222','fixture','synthetic-v2','fixture','synthetic-policy-v2',true,'fixture',3),
 ('bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb','11111111-1111-4111-8111-111111111111','fixture','synthetic-v1','fixture','synthetic-policy-v1',true,'fixture',3);
create function public.recipe_assert(ok boolean, label text) returns void language plpgsql as $$
begin if ok is not true then raise exception 'FAIL %', label; end if; raise notice 'PASS %', label; end $$;
grant execute on function public.recipe_assert(boolean,text) to authenticated;
set role authenticated;
select set_config('request.jwt.claim.sub','aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa',false);
insert into public.face_search_subjects(subject_id) values ('33333333-3333-4333-8333-333333333333');
insert into public.face_search_enrollments(enrollment_id,subject_id,pipeline_id) values
 ('44444444-4444-4444-8444-444444444444','33333333-3333-4333-8333-333333333333','11111111-1111-4111-8111-111111111111');
select public.recipe_assert((select count(*)=0 from public.face_search_match('11111111-1111-4111-8111-111111111111','[1,0,0]',0.1)), 'pending excluded');
do $$begin
 begin update public.face_search_enrollments set status='complete',embedding='[1,0]',compute_request_id='synthetic-request-a'; raise exception 'test expected dimension rejection'; exception when sqlstate '22023' then null; end;
 perform public.recipe_assert(true,'incompatible enrollment dimensions rejected');
 begin update public.face_search_enrollments set status='complete',embedding='[0,0,0]',compute_request_id='synthetic-request-a'; raise exception 'test expected zero rejection'; exception when sqlstate '22023' then null; end;
 perform public.recipe_assert(true,'zero enrollment rejected');
 begin update public.face_search_enrollments set status='complete',embedding='[NaN,0,0]',compute_request_id='synthetic-request-a'; raise exception 'test expected nonfinite rejection'; exception when data_exception then null; end;
 perform public.recipe_assert(true,'nonfinite enrollment rejected');
end$$;
update public.face_search_enrollments set status='complete',embedding='[1,0,0]',compute_request_id='synthetic-request-a';
select public.recipe_assert((select count(*)=1 from public.face_search_match('11111111-1111-4111-8111-111111111111','[1,0,0]',0.1)), 'enroll and match');
select public.recipe_assert((select count(*)=0 from public.face_search_match('11111111-1111-4111-8111-111111111111','[0,1,0]',0.1)), 'no acceptable match');
select public.recipe_assert((select count(*)=0 from public.face_search_match('22222222-2222-4222-8222-222222222222','[1,0,0]',0.1)), 'same dimensions different pipeline isolated');
do $$begin
 begin update public.face_search_enrollments set embedding='[0,1,0]'; raise exception 'test expected immutable'; exception when sqlstate '22023' then null; end;
 perform public.recipe_assert(true,'completed enrollment immutable');
 begin insert into public.face_search_enrollments(enrollment_id,subject_id,pipeline_id) values ('44444444-4444-4444-8444-444444444444','33333333-3333-4333-8333-333333333333','11111111-1111-4111-8111-111111111111'); raise exception 'test expected duplicate'; exception when unique_violation then null; end;
 perform public.recipe_assert(true,'reservation unique');
 begin perform * from public.face_search_match('11111111-1111-4111-8111-111111111111','[1,0]',0.1); raise exception 'test expected dimensions'; exception when sqlstate '22023' then null; end;
 perform public.recipe_assert(true,'wrong dimensions rejected');
 begin perform * from public.face_search_match('11111111-1111-4111-8111-111111111111','[0,0,0]',0.1); raise exception 'test expected zero'; exception when sqlstate '22023' then null; end;
 perform public.recipe_assert(true,'zero vector rejected');
 begin perform * from public.face_search_match('11111111-1111-4111-8111-111111111111','[1,0,0]',null); raise exception 'test expected threshold'; exception when sqlstate '22023' then null; end;
 perform public.recipe_assert(true,'missing threshold rejected');
 begin perform * from public.face_search_match('11111111-1111-4111-8111-111111111111','[1,0,0]','NaN'); raise exception 'test expected nan'; exception when sqlstate '22023' then null; end;
 perform public.recipe_assert(true,'nonfinite threshold rejected');
 begin perform * from public.face_search_match('11111111-1111-4111-8111-111111111111','[1,0,0]',0.1,0); raise exception 'test expected limit'; exception when sqlstate '22023' then null; end;
 perform public.recipe_assert(true,'unbounded limit rejected');
 begin insert into public.face_search_pipelines values ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa','55555555-5555-4555-8555-555555555555','fixture','forged','fixture','synthetic-policy-v1',true,'fixture',3); raise exception 'test expected provision denial'; exception when insufficient_privilege then null; end;
 perform public.recipe_assert(true,'pipeline provisioning owner only');
end$$;
select set_config('request.jwt.claim.sub','bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb',false);
select public.recipe_assert((select count(*)=0 from public.face_search_enrollments), 'cross tenant read denied');
select public.recipe_assert((select count(*)=0 from public.face_search_match('11111111-1111-4111-8111-111111111111','[1,0,0]',0.1)), 'cross tenant search denied');
with removed as (delete from public.face_search_subjects where subject_id='33333333-3333-4333-8333-333333333333' returning *) select public.recipe_assert((select count(*)=0 from removed), 'cross tenant delete denied');
do $$begin
 begin insert into public.face_search_subjects values ('aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa','66666666-6666-4666-8666-666666666666',now()); raise exception 'test expected tenant denial'; exception when insufficient_privilege then null; end;
 perform public.recipe_assert(true,'tenant spoof rejected');
 begin insert into public.face_search_enrollments(enrollment_id,subject_id,pipeline_id) values ('77777777-7777-4777-8777-777777777777','33333333-3333-4333-8333-333333333333','11111111-1111-4111-8111-111111111111'); raise exception 'test expected subject denial'; exception when foreign_key_violation then null; end;
 perform public.recipe_assert(true,'cross tenant subject reference rejected');
end$$;
select set_config('request.jwt.claim.sub','',false);
select public.recipe_assert((select count(*)=0 from public.face_search_subjects), 'missing identity has no rows');
reset role;
set role anon;
do $$begin
 begin perform * from public.face_search_subjects; raise exception 'test expected anon denial'; exception when insufficient_privilege then null; end;
 begin perform * from public.face_search_match('11111111-1111-4111-8111-111111111111','[1,0,0]',0.1); raise exception 'test expected anon rpc denial'; exception when insufficient_privilege then null; end;
end$$;
reset role;
select public.recipe_assert(true,'anonymous table and function denied');
set role authenticated;
select set_config('request.jwt.claim.sub','aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa',false);
delete from public.face_search_subjects where subject_id='33333333-3333-4333-8333-333333333333';
select public.recipe_assert((select count(*)=0 from public.face_search_enrollments), 'subject deletion cascades');
select public.recipe_assert((select count(*)=0 from public.face_search_match('11111111-1111-4111-8111-111111111111','[1,0,0]',0.1)), 'deleted subject unsearchable');
reset role;

import { execFile } from "node:child_process";
import { promisify } from "node:util";
import { mkdtemp, mkdir, readFile, writeFile, rm } from "node:fs/promises";
import { tmpdir } from "node:os";
import path from "node:path";
const exec = promisify(execFile);
const root = process.cwd();
const bindir = process.env.PG_BINDIR || (await exec("pg_config", ["--bindir"])).stdout.trim();
const directory = await mkdtemp(path.join(tmpdir(), "face-search-sql-"));
const socket = path.join(directory, "socket");
const data = path.join(directory, "data");
const env = Object.fromEntries(Object.entries(process.env).filter(([key]) => !key.startsWith("PG")));
const pg = (name, args) => exec(path.join(bindir, name), args, { env, maxBuffer: 2 * 1024 * 1024 });
const sql = async (source) => {
  const file = path.join(directory, "run.sql");
  await writeFile(file, source);
  return pg("psql", ["-X", "--no-password", "-h", socket, "-p", "55439", "-U", "recipe_lab", "-d", "postgres", "-v", "ON_ERROR_STOP=1", "-f", file]);
};
let started = false;
try {
  await mkdir(socket, { mode: 0o700 });
  await pg("initdb", ["-D", data, "--username=recipe_lab", "--encoding=UTF8", "--no-locale", "--auth-local=trust", "--auth-host=reject"]);
  await pg("pg_ctl", ["-D", data, "-w", "-l", path.join(directory, "postgres.log"), "-o", `-k ${socket} -p 55439 -c listen_addresses=''`, "start"]);
  started = true;
  await sql(`create role anon nologin; create role authenticated nologin nobypassrls;\ncreate schema auth; create table auth.users(id uuid primary key);\ncreate function auth.uid() returns uuid language sql stable as $$select nullif(current_setting('request.jwt.claim.sub', true), '')::uuid$$;\ngrant usage on schema auth to authenticated; grant execute on function auth.uid() to authenticated;\ncreate schema extensions;`);
  let schema = await readFile(path.join(root, "examples/customer-owned-search/schema.sql"), "utf8");
  let vectorMode = "installed extension";
  if (process.env.PGVECTOR_SOURCE_DIR || process.env.PGVECTOR_LIBRARY) {
    if (!process.env.PGVECTOR_SOURCE_DIR || !process.env.PGVECTOR_LIBRARY) throw new Error("Both PGVECTOR_SOURCE_DIR and PGVECTOR_LIBRARY are required");
    const library = process.env.PGVECTOR_LIBRARY.replaceAll("'", "''");
    const source = (await readFile(path.join(process.env.PGVECTOR_SOURCE_DIR, "sql/vector.sql"), "utf8")).replace(/^\\.*$/gm, "").replaceAll("MODULE_PATHNAME", library);
    await sql(`set search_path = extensions, pg_catalog;\n${source}`);
    const declaration = "create extension if not exists vector with schema extensions;";
    if (!schema.includes(declaration)) throw new Error("Expected extension declaration absent");
    schema = schema.replace(declaration, "-- Real pgvector types preloaded from source in this isolated lab.");
    vectorMode = "source-loaded real pgvector (extension installation not tested)";
  }
  await sql(schema);
  let baseline;
  if (process.env.CUSTOMER_SEARCH_BASELINE_REF) {
    const ref = process.env.CUSTOMER_SEARCH_BASELINE_REF;
    if (!/^[a-f0-9]{40}$/.test(ref)) throw new Error("Baseline must be an exact commit SHA");
    const old = (await exec("git", ["show", `${ref}:docs-site/customer-owned-face-search.mdx`], { cwd: root })).stdout;
    const blocks = [...old.matchAll(/```sql\n([\s\S]*?)```/g)].map((match) => match[1]);
    if (blocks.length !== 2) throw new Error("Expected original guide's two SQL blocks");
    const vector = "('[1,' || repeat('0,',126) || '0]')::extensions.vector";
    await sql(`set search_path = public, extensions, pg_catalog;\n${blocks.join("\n").replace(/create extension if not exists vector;/i, "")}\ninsert into public.face_embeddings(person_id,embedding) values ('synthetic-a',${vector}),('synthetic-b',${vector});\ncreate role recipe_baseline_service nologin bypassrls;\ngrant usage on schema public, extensions to recipe_baseline_service; grant select on public.face_embeddings to recipe_baseline_service;\nset role recipe_baseline_service;\ndo $$begin if (select count(*) from public.match_faces(${vector})) <> 2 then raise exception 'baseline assertion failed'; end if; end$$; reset role;`);
    baseline = { base: ref, original_service_role_visible_synthetic_subjects: 2 };
  }
  const result = await sql(await readFile(path.join(root, "examples/customer-owned-search/test.sql"), "utf8"));
  console.log(JSON.stringify({ postgres: (await pg("postgres", ["--version"])).stdout.trim(), vector_mode: vectorMode, baseline, tests: [...result.stderr.matchAll(/NOTICE:\s+PASS ([^\r\n]+)/g)].map((match) => match[1]) }));
} finally {
  if (started) await pg("pg_ctl", ["-D", data, "-m", "fast", "-w", "stop"]);
  await rm(directory, { recursive: true, force: true });
}