sailfork / t3code-github / .repos/ alchemy-effect/ packages/ gcp/ src/ BigQuery/ Routine.ts

import * as bigquery from "@distilled.cloud/gcp/bigquery_v2";
import { Unowned } from "alchemy/AdoptPolicy";
import { isResolved } from "alchemy/Diff";
import { createPhysicalName } from "alchemy/PhysicalName";
import * as Provider from "alchemy/Provider";
import { Resource } from "alchemy/Resource";
import * as Data from "effect/Data";
import * as Effect from "effect/Effect";
import * as Stream from "effect/Stream";
import { GcpEnvironment } from "../Environment.ts";
import {
  ALCHEMY_LABEL_PREFIX,
  alchemyLabelKeys,
  createInternalLabels,
  hasAlchemyLabels,
} from "../Labels.ts";
import type { Providers } from "../Providers.ts";

const MAX_ROUTINE_ID_LENGTH = 256;
const DEFAULT_LANGUAGE = "SQL";
const DEFAULT_ARGUMENT_KIND = "FIXED_TYPE";

export type RoutineType = bigquery.RoutineRoutineTypeEnum | (string & {});
export type RoutineLanguage = bigquery.RoutineLanguageEnum | (string & {});
export type RoutineArgument = bigquery.Argument;
export type RoutineDataType = bigquery.StandardSqlDataType;
export type RoutineReturnTableType = bigquery.StandardSqlTableType;
export type RoutineSparkOptions = bigquery.SparkOptions;
export type RoutineRemoteFunctionOptions = bigquery.RemoteFunctionOptions;
export type RoutinePythonOptions = bigquery.PythonOptions;
export type RoutineExternalRuntimeOptions = bigquery.ExternalRuntimeOptions;
export type RoutineDeterminismLevel = bigquery.RoutineDeterminismLevelEnum | (string & {});
export type RoutineSecurityMode = bigquery.RoutineSecurityModeEnum | (string & {});
export type RoutineDataGovernanceType = bigquery.RoutineDataGovernanceTypeEnum | (string & {});

export type RoutineProps = {
  /**
   * Dataset id (the `{dataset}` segment of
   * `projects/{project}/datasets/{dataset}`) or a full dataset resource
   * name. Immutable — changing it replaces the routine.
   */
  datasetId: string;
  /**
   * Routine id (the `{routine}` segment of
   * `projects/{project}/datasets/{dataset}/routines/{routine}`). If omitted,
   * a unique name is generated from the stack, stage, and logical id. Must
   * contain only letters, numbers, and underscores; max 256 characters.
   * Immutable — changing it replaces the routine.
   */
  routineId?: string;
  /**
   * Routine kind (`SCALAR_FUNCTION`, `PROCEDURE`, `TABLE_VALUED_FUNCTION`,
   * `AGGREGATE_FUNCTION`). Immutable — changing it replaces the routine.
   */
  routineType: RoutineType;
  /**
   * Body of the routine. For SQL functions this is the expression inside
   * the `AS (…)` clause, not a full `CREATE FUNCTION` statement. For
   * JavaScript, the evaluated string in the `AS` clause.
   */
  definitionBody: string;
  /**
   * Implementation language (`SQL`, `JAVASCRIPT`, `PYTHON`, `JAVA`,
   * `SCALA`). Defaults to `SQL` unless `remoteFunctionOptions` is set.
   */
  language?: RoutineLanguage;
  /**
   * Input/output arguments. `mode` is valid on procedures only.
   */
  arguments?: RoutineArgument[];
  /**
   * Return type for scalar and aggregate functions. Optional for SQL
   * (inferred at query time); required for other languages.
   */
  returnType?: RoutineDataType;
  /**
   * Return table schema when `routineType` is `TABLE_VALUED_FUNCTION`.
   * Optional for SQL (inferred at query time).
   */
  returnTableType?: RoutineReturnTableType;
  /**
   * User-friendly description of this routine. Alchemy ownership is stored
   * in a `[alchemy …]` prefix for `list` / nuke.
   */
  description?: string;
  /**
   * GCS paths of imported JavaScript libraries when `language` is
   * `JAVASCRIPT`.
   */
  importedLibraries?: string[];
  /**
   * Determinism of a JavaScript UDF (`DETERMINISTIC`,
   * `NOT_DETERMINISTIC`).
   */
  determinismLevel?: RoutineDeterminismLevel;
  /**
   * Security mode (`DEFINER`, `INVOKER`). If omitted, BigQuery infers it
   * from the routine configuration.
   */
  securityMode?: RoutineSecurityMode;
  /**
   * Set to `DATA_MASKING` to register a custom masking function.
   */
  dataGovernanceType?: RoutineDataGovernanceType;
  /**
   * Extra validation for procedure bodies. Default `true`. Recursive
   * procedures should set this to `false`.
   * @default true
   */
  strictMode?: boolean;
  /**
   * Spark procedure options (`JAVA` / `SCALA` / PySpark).
   */
  sparkOptions?: RoutineSparkOptions;
  /**
   * Remote function endpoint and connection.
   */
  remoteFunctionOptions?: RoutineRemoteFunctionOptions;
  /**
   * Python UDF entry point and packages.
   */
  pythonOptions?: RoutinePythonOptions;
  /**
   * Container limits for Python UDFs.
   */
  externalRuntimeOptions?: RoutineExternalRuntimeOptions;
};

export type Routine = Resource<
  "GCP.BigQuery.Routine",
  RoutineProps,
  {
    /**
     * Resource path
     * `projects/{project}/datasets/{dataset}/routines/{routine}`.
     */
    name: string;
    /** Opaque id `project:dataset.routine`. */
    id: string;
    /** Routine id (last path segment). */
    routineId: string;
    /** Dataset id. */
    datasetId: string;
    /** Project id. */
    project: string;
    /** Routine kind. */
    routineType: string;
    /** Implementation language, if set. */
    language: string | undefined;
    /** Routine body. */
    definitionBody: string;
    /** Arguments, if set. */
    arguments: RoutineArgument[] | undefined;
    /** Scalar/aggregate return type, if set. */
    returnType: RoutineDataType | undefined;
    /** TVF return table type, if set. */
    returnTableType: RoutineReturnTableType | undefined;
    /** Description, if set. */
    description: string | undefined;
    /** Imported JavaScript libraries. */
    importedLibraries: string[] | undefined;
    /** JavaScript determinism, if set. */
    determinismLevel: string | undefined;
    /** Security mode, if set. */
    securityMode: string | undefined;
    /** Data-governance classification, if set. */
    dataGovernanceType: string | undefined;
    /** Procedure strict-mode flag. */
    strictMode: boolean | undefined;
    /** Spark options, if set. */
    sparkOptions: RoutineSparkOptions | undefined;
    /** Remote function options, if set. */
    remoteFunctionOptions: RoutineRemoteFunctionOptions | undefined;
    /** Python UDF options, if set. */
    pythonOptions: RoutinePythonOptions | undefined;
    /** Python runtime options, if set. */
    externalRuntimeOptions: RoutineExternalRuntimeOptions | undefined;
    /** Creation time in milliseconds since epoch. */
    creationTime: string | undefined;
    /** Last-modified time in milliseconds since epoch. */
    lastModifiedTime: string | undefined;
    /** Server etag. */
    etag: string | undefined;
  },
  never,
  Providers
>;

/**
 * A Google BigQuery routine — a user-defined function, table-valued
 * function, aggregate function, or stored procedure.
 *
 * Routines have no labels; Alchemy stamps ownership into the description
 * (`[alchemy alchemy-stack=… alchemy-stage=… alchemy-id=…]`) so `list` /
 * `pnpm nuke:gcp` can find them inside datasets that carry `alchemy-*`
 * labels. `datasetId`, `routineId`, and `routineType` are immutable
 * (changing them replaces the routine). Body, arguments, description,
 * language, and language-specific options update in place via a
 * full-resource replace.
 *
 * ### Creating a Routine
 * **Example:** SQL scalar function
 * ```typescript
 * const dataset = yield* GCP.BigQuery.Dataset("Analytics", {
 *   forceDestroy: true,
 * });
 * const add = yield* GCP.BigQuery.Routine("Add", {
 *   datasetId: dataset.datasetId,
 *   routineType: "SCALAR_FUNCTION",
 *   language: "SQL",
 *   arguments: [
 *     { name: "x", dataType: { typeKind: "INT64" } },
 *     { name: "y", dataType: { typeKind: "INT64" } },
 *   ],
 *   returnType: { typeKind: "INT64" },
 *   definitionBody: "x + y",
 * });
 * ```
 *
 * **Example:** SQL stored procedure
 * ```typescript
 * const proc = yield* GCP.BigQuery.Routine("Seed", {
 *   datasetId: dataset.datasetId,
 *   routineType: "PROCEDURE",
 *   language: "SQL",
 *   definitionBody: "SELECT 1",
 * });
 * ```
 *
 * **Example:** JavaScript UDF
 * ```typescript
 * const multiply = yield* GCP.BigQuery.Routine("Multiply", {
 *   datasetId: dataset.datasetId,
 *   routineType: "SCALAR_FUNCTION",
 *   language: "JAVASCRIPT",
 *   determinismLevel: "DETERMINISTIC",
 *   arguments: [
 *     { name: "x", dataType: { typeKind: "FLOAT64" } },
 *     { name: "y", dataType: { typeKind: "FLOAT64" } },
 *   ],
 *   returnType: { typeKind: "FLOAT64" },
 *   definitionBody: "return x * y;",
 * });
 * ```
 *
 * ### Calling a Routine
 * **Example:** Invoke a SQL function with Query
 * ```typescript
 * const query = yield* GCP.BigQuery.Query(dataset);
 * const result = yield* query({ query: "SELECT Add(1, 2) AS n" });
 * ```
 *
 * @resource
 * @category BigQuery
 */
export const Routine = Resource<Routine>("GCP.BigQuery.Routine");

export class RoutineNotResolved extends Data.TaggedError("GCP.BigQuery.RoutineNotResolved")<{
  resourceName: string;
}> {}

const lastSegment = (value: string) => {
  const trimmed = value.replace(/\/+$/, "");
  const parts = trimmed.split("/");
  return parts[parts.length - 1] || trimmed;
};

const datasetIdOf = (datasetId: string) =>
  datasetId.includes("/") ? lastSegment(datasetId) : datasetId;

const resourceName = (project: string, datasetId: string, routineId: string) =>
  `projects/${project}/datasets/${datasetId}/routines/${routineId}`;

const toId = (id: string, routineId: string | undefined, existing?: string) =>
  Effect.gen(function* () {
    if (routineId !== undefined) return routineId;
    if (existing !== undefined) return existing;
    const generated = yield* createPhysicalName({
      id,
      maxLength: MAX_ROUTINE_ID_LENGTH,
      lowercase: true,
      delimiter: "_",
    });
    return generated.replaceAll("-", "_");
  });

const stable = (value: unknown): unknown => {
  if (Array.isArray(value)) return value.map(stable);
  if (value !== null && typeof value === "object") {
    return Object.fromEntries(
      Object.entries(value as Record<string, unknown>)
        .filter(([, nested]) => nested !== undefined)
        .sort(([left], [right]) => left.localeCompare(right))
        .map(([key, nested]) => [key, stable(nested)]),
    );
  }
  return value;
};

const jsonEqual = (left: unknown, right: unknown) =>
  JSON.stringify(stable(left ?? null)) === JSON.stringify(stable(right ?? null));

const canonType = (type: RoutineDataType | undefined): RoutineDataType | undefined => {
  if (type === undefined) return undefined;
  return {
    typeKind: type.typeKind,
    arrayElementType: canonType(type.arrayElementType),
    rangeElementType: canonType(type.rangeElementType),
    structType: type.structType
      ? {
          fields: (type.structType.fields ?? []).map((field) => ({
            name: field.name,
            type: canonType(field.type),
          })),
        }
      : undefined,
  };
};

const canonArguments = (args: RoutineArgument[] | undefined) =>
  (args ?? []).map((argument) => ({
    name: argument.name ?? "",
    argumentKind: argument.argumentKind ?? DEFAULT_ARGUMENT_KIND,
    mode: argument.mode,
    isAggregate: argument.isAggregate,
    dataType: canonType(argument.dataType),
  }));

const encodeDescription = (
  labels: Record<string, string>,
  description: string | undefined,
): string => {
  const marker = `[alchemy ${alchemyLabelKeys.stack}=${labels[alchemyLabelKeys.stack]} ${alchemyLabelKeys.stage}=${labels[alchemyLabelKeys.stage]} ${alchemyLabelKeys.id}=${labels[alchemyLabelKeys.id]}]`;
  return description ? `${marker}\n${description}` : marker;
};

const parseDescription = (
  description: string | undefined,
): {
  labels: Record<string, string>;
  description: string | undefined;
} => {
  if (!description?.startsWith("[alchemy ")) {
    return { labels: {}, description };
  }
  const end = description.indexOf("]");
  if (end < 0) return { labels: {}, description };
  const labels: Record<string, string> = {};
  for (const part of description.slice("[alchemy ".length, end).split(/\s+/)) {
    const eq = part.indexOf("=");
    if (eq > 0) {
      labels[part.slice(0, eq)] = part.slice(eq + 1);
    }
  }
  const rest = description.slice(end + 1).replace(/^\n/, "");
  return { labels, description: rest.length > 0 ? rest : undefined };
};

const hasOwnershipMarker = (description: string | undefined) =>
  Object.keys(parseDescription(description).labels).some((key) =>
    key.startsWith(ALCHEMY_LABEL_PREFIX),
  );

const toAttrs = (routine: bigquery.Routine, project: string) => {
  const ref = routine.routineReference;
  const routineId = ref?.routineId ?? "";
  const datasetId = ref?.datasetId ?? "";
  const projectId = ref?.projectId ?? project;
  return {
    name: resourceName(projectId, datasetId, routineId),
    id: `${projectId}:${datasetId}.${routineId}`,
    routineId,
    datasetId,
    project: projectId,
    routineType: routine.routineType ?? "",
    language: routine.language,
    definitionBody: routine.definitionBody ?? "",
    arguments: routine.arguments,
    returnType: routine.returnType,
    returnTableType: routine.returnTableType,
    description: parseDescription(routine.description).description,
    importedLibraries: routine.importedLibraries,
    determinismLevel: routine.determinismLevel,
    securityMode: routine.securityMode,
    dataGovernanceType: routine.dataGovernanceType,
    strictMode: routine.strictMode,
    sparkOptions: routine.sparkOptions,
    remoteFunctionOptions: routine.remoteFunctionOptions,
    pythonOptions: routine.pythonOptions,
    externalRuntimeOptions: routine.externalRuntimeOptions,
    creationTime: routine.creationTime,
    lastModifiedTime: routine.lastModifiedTime,
    etag: routine.etag,
  };
};

const getByRef = (projectId: string, datasetId: string, routineId: string) =>
  bigquery
    .getRoutines({ projectId, datasetId, routineId })
    .pipe(Effect.catchTag("NotFound", () => Effect.undefined));

const defaultLanguage = (news: RoutineProps, observed?: bigquery.Routine) => {
  if (news.language !== undefined) return news.language;
  if (observed?.language !== undefined) return observed.language;
  if (news.remoteFunctionOptions !== undefined || observed?.remoteFunctionOptions !== undefined) {
    return undefined;
  }
  return DEFAULT_LANGUAGE;
};

const pick = <T>(news: T | undefined, observed: T | undefined) =>
  news !== undefined ? news : observed;

const toRoutineBody = (
  projectId: string,
  datasetId: string,
  routineId: string,
  news: RoutineProps,
  encodedDescription: string,
  observed?: bigquery.Routine,
): bigquery.Routine => {
  const body: bigquery.Routine = {
    routineReference: { projectId, datasetId, routineId },
    routineType: news.routineType,
    definitionBody: news.definitionBody,
    description: encodedDescription,
  };
  const language = defaultLanguage(news, observed);
  if (language !== undefined) body.language = language;
  const args = pick(news.arguments, observed?.arguments);
  if (args !== undefined) body.arguments = args;
  const returnType = pick(news.returnType, observed?.returnType);
  if (returnType !== undefined) body.returnType = returnType;
  const returnTableType = pick(news.returnTableType, observed?.returnTableType);
  if (returnTableType !== undefined) body.returnTableType = returnTableType;
  const importedLibraries = pick(news.importedLibraries, observed?.importedLibraries);
  if (importedLibraries !== undefined) {
    body.importedLibraries = importedLibraries;
  }
  const determinismLevel = pick(news.determinismLevel, observed?.determinismLevel);
  if (determinismLevel !== undefined) body.determinismLevel = determinismLevel;
  const securityMode = pick(news.securityMode, observed?.securityMode);
  if (securityMode !== undefined) body.securityMode = securityMode;
  const dataGovernanceType = pick(news.dataGovernanceType, observed?.dataGovernanceType);
  if (dataGovernanceType !== undefined) {
    body.dataGovernanceType = dataGovernanceType;
  }
  const strictMode = pick(news.strictMode, observed?.strictMode);
  if (strictMode !== undefined) body.strictMode = strictMode;
  const sparkOptions = pick(news.sparkOptions, observed?.sparkOptions);
  if (sparkOptions !== undefined) body.sparkOptions = sparkOptions;
  const remoteFunctionOptions = pick(news.remoteFunctionOptions, observed?.remoteFunctionOptions);
  if (remoteFunctionOptions !== undefined) {
    body.remoteFunctionOptions = remoteFunctionOptions;
  }
  const pythonOptions = pick(news.pythonOptions, observed?.pythonOptions);
  if (pythonOptions !== undefined) body.pythonOptions = pythonOptions;
  const externalRuntimeOptions = pick(
    news.externalRuntimeOptions,
    observed?.externalRuntimeOptions,
  );
  if (externalRuntimeOptions !== undefined) {
    body.externalRuntimeOptions = externalRuntimeOptions;
  }
  return body;
};

const inputSnapshot = (routine: bigquery.Routine) => ({
  routineType: (routine.routineType ?? "").toUpperCase(),
  language: routine.language,
  definitionBody: routine.definitionBody ?? "",
  arguments: canonArguments(routine.arguments),
  returnType: canonType(routine.returnType),
  returnTableType: routine.returnTableType
    ? {
        columns: (routine.returnTableType.columns ?? []).map((column) => ({
          name: column.name,
          type: canonType(column.type),
        })),
      }
    : undefined,
  description: routine.description ?? "",
  importedLibraries: routine.importedLibraries ?? [],
  determinismLevel: routine.determinismLevel,
  securityMode: routine.securityMode,
  dataGovernanceType: routine.dataGovernanceType,
  strictMode: routine.strictMode,
  sparkOptions: routine.sparkOptions,
  remoteFunctionOptions: routine.remoteFunctionOptions,
  pythonOptions: routine.pythonOptions,
  externalRuntimeOptions: routine.externalRuntimeOptions,
});

const hasAlchemyDatasetLabels = (labels: Record<string, string | undefined> | null | undefined) =>
  Object.keys(labels ?? {}).some((key) => key.startsWith(ALCHEMY_LABEL_PREFIX));

export const RoutineProvider = () =>
  Provider.succeed(Routine, {
    stables: ["name", "id", "routineId", "datasetId", "project", "routineType", "creationTime"],

    diff: Effect.fn(function* ({ news, olds, output }) {
      if (!isResolved(news)) return undefined;
      const previousRoutineId = olds?.routineId ?? output?.routineId;
      const nextRoutineId = news.routineId ?? previousRoutineId;
      const routineIdChanged =
        news.routineId !== undefined &&
        previousRoutineId !== undefined &&
        news.routineId !== previousRoutineId;

      const previousDataset = olds?.datasetId ?? output?.datasetId;
      const nextDataset = datasetIdOf(news.datasetId);
      const datasetChanged =
        previousDataset !== undefined && datasetIdOf(previousDataset) !== nextDataset;

      const previousType = (olds?.routineType ?? output?.routineType ?? "").toUpperCase();
      const typeChanged =
        previousType.length > 0 && news.routineType.toUpperCase() !== previousType;

      if (!routineIdChanged && !datasetChanged && !typeChanged) {
        return undefined;
      }
      return {
        action: "replace" as const,
        deleteFirst:
          !routineIdChanged &&
          !datasetChanged &&
          nextRoutineId !== undefined &&
          previousRoutineId !== undefined &&
          nextRoutineId === previousRoutineId,
      };
    }),

    read: Effect.fn(function* ({ id, olds, output }) {
      const env = yield* GcpEnvironment.current;
      const routineId = yield* toId(id, olds?.routineId, output?.routineId);
      const datasetId = datasetIdOf(olds?.datasetId ?? output?.datasetId ?? "");
      if (datasetId.length === 0) return undefined;
      const existing = yield* getByRef(env.project, datasetId, routineId);
      if (existing === undefined) return undefined;
      const attrs = toAttrs(existing, env.project);
      return (yield* hasAlchemyLabels(id, parseDescription(existing.description).labels))
        ? attrs
        : Unowned(attrs);
    }),

    list: () =>
      Effect.gen(function* () {
        const env = yield* GcpEnvironment.current;
        const datasets = yield* bigquery.listDatasets
          .pages({
            projectId: env.project,
            maxResults: 1000,
            all: true,
          })
          .pipe(
            Stream.flatMap((page) => Stream.fromIterable(page.datasets ?? [])),
            Stream.filter((dataset) => hasAlchemyDatasetLabels(dataset.labels)),
            Stream.runCollect,
            Effect.map((chunk) => Array.from(chunk)),
            Effect.catchTag("NotFound", () =>
              Effect.succeed([] as bigquery.DatasetListDatasetsItem[]),
            ),
          );
        const pages = yield* Effect.forEach(
          datasets,
          (dataset) => {
            const datasetId = dataset.datasetReference?.datasetId;
            if (datasetId === undefined) {
              return Effect.succeed([] as ReturnType<typeof toAttrs>[]);
            }
            return bigquery.listRoutines
              .pages({
                projectId: env.project,
                datasetId,
                maxResults: 1000,
                readMask: "description,routineType,language",
              })
              .pipe(
                Stream.flatMap((page) => Stream.fromIterable(page.routines ?? [])),
                Stream.filter((routine) => hasOwnershipMarker(routine.description)),
                Stream.map((routine) => toAttrs(routine, env.project)),
                Stream.runCollect,
                Effect.map((chunk) => Array.from(chunk)),
                Effect.catchTag("NotFound", () =>
                  Effect.succeed([] as ReturnType<typeof toAttrs>[]),
                ),
              );
          },
          { concurrency: 4 },
        );
        return pages.flat();
      }),

    reconcile: Effect.fn(function* ({ id, news, output }) {
      const env = yield* GcpEnvironment.current;
      const routineId = yield* toId(id, news.routineId, output?.routineId);
      const datasetId = datasetIdOf(news.datasetId);
      const name = resourceName(env.project, datasetId, routineId);
      const internalLabels = yield* createInternalLabels(id);

      let current = yield* getByRef(env.project, datasetId, routineId);
      const userDescription =
        news.description !== undefined
          ? news.description
          : parseDescription(current?.description).description;
      const encodedDescription = encodeDescription(internalLabels, userDescription);
      const bodyOf = (observed?: bigquery.Routine) =>
        toRoutineBody(env.project, datasetId, routineId, news, encodedDescription, observed);

      if (current === undefined) {
        const created = yield* bigquery
          .insertRoutines({
            projectId: env.project,
            datasetId,
            body: bodyOf(),
          })
          .pipe(Effect.catchTag("Conflict", () => getByRef(env.project, datasetId, routineId)));
        current = created ?? undefined;
      }

      if (current === undefined) {
        return yield* new RoutineNotResolved({ resourceName: name });
      }

      const desired = bodyOf(current);
      const metadataChanged = !jsonEqual(inputSnapshot(desired), inputSnapshot(current));

      if (metadataChanged) {
        current = yield* bigquery
          .updateRoutines({
            projectId: env.project,
            datasetId,
            routineId,
            body: desired,
          })
          .pipe(
            Effect.catchTag("NotFound", () =>
              bigquery
                .insertRoutines({
                  projectId: env.project,
                  datasetId,
                  body: bodyOf(),
                })
                .pipe(
                  Effect.catchTag("Conflict", () => getByRef(env.project, datasetId, routineId)),
                ),
            ),
          );
        if (current === undefined) {
          return yield* new RoutineNotResolved({ resourceName: name });
        }
      }

      return toAttrs(current, env.project);
    }),

    delete: Effect.fn(function* ({ output }) {
      yield* bigquery
        .deleteRoutines({
          projectId: output.project,
          datasetId: output.datasetId,
          routineId: output.routineId,
        })
        .pipe(Effect.catchTag("NotFound", () => Effect.void));
    }),
  });