Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

25.5 KB
Raw Blame
import { toViewColumn } from '../../database/queries/column-detail';

export { toViewColumn } from '../../database/queries/column-detail';

import { databaseSqlSchema } from '@core/database-sql/catalog';
import type { DatabaseOp } from '@core/database-sql/generated/types';
import type { DatabaseSqlReadReason } from '@core/database-sql/trace';
import { type ResultError, thrownResultErrorHasCode } from '@core/util/result';
import { Telemetry } from '@macro-inc/observability';
import {
  createDatabaseSqlQuery,
  type DatabaseSqlQuery,
  type DatabaseSqlQueryCapabilities,
  type DatabaseSqlRun,
  type DatabaseSqlStatement,
  readDatabaseSql,
  refreshInBackground,
  sameDatabaseSqlStatement,
} from '@queries/database-sql/create-database-sql-query';
import { databaseDetailQueryOptions } from '@queries/storage/databases';
import { databasesKeys } from '@queries/storage/keys';
import { storageServiceClient } from '@service-storage/client';
import type {
  DatabaseOpsError,
  DatabaseSchemaErrorCode,
} from '@service-storage/databases';
import type { ColumnDetail } from '@service-storage/generated/schemas/columnDetail';
import type { DatabaseDetail } from '@service-storage/generated/schemas/databaseDetail';
import type { DatabaseView } from '@service-storage/generated/schemas/databaseView';
import type { OpResult } from '@service-storage/generated/schemas/opResult';
import type { TableDetail } from '@service-storage/generated/schemas/tableDetail';
import { useQueryClient } from '@tanstack/solid-query';
import {
  err,
  errAsync,
  ok,
  okAsync,
  type Result,
  ResultAsync,
} from 'neverthrow';
import { type Accessor, createMemo, createSignal, untrack } from 'solid-js';
import type {
  DatabaseRowsSource,
  DatabaseWriteResult,
} from '../../database/context/table-source';
import {
  type DatabaseColumnType,
  inferDatabaseNumber,
} from '../../database/core/column-inference';
import type { DatabaseCellValue } from '../../database/core/database-view';
import { type GridRowsRead, gridRows } from '../../database/core/grid-cells';
import { writeDatabaseRow } from '../../database/core/row-write';
import type { DatabaseRowMutation } from '../../database/core/table';
import type {
  DatabaseCellFailure,
  DatabaseReadFailure,
  DatabaseWriteFailure,
} from '../../database/core/write-failure';
import { rowsByIdStatement } from '../sql';
import { patchTableColumn } from './detail-cache';
import {
  refreshChangedRows,
  type TableChangesCapabilities,
} from './table-changes';
import { createDatabaseViewQuery, type DatabaseViewQuery } from './view-rows';

/** A stale table or column name, which a refreshed schema may resolve. */
function isStaleSchema(
  failure: DatabaseReadFailure | DatabaseWriteFailure
): boolean {
  return (
    failure.kind === 'engine' ||
    ('staleSchema' in failure && failure.staleSchema === true) ||
    (failure.kind === 'ops' && failure.error.code === 'INVALID_OP')
  );
}

const TABLE_UNAVAILABLE = { kind: 'table-unavailable' } as const;

/** A missing or deleted database means the table is gone; anything else is a failed read. */
function schemaReadFailure(thrown: unknown): DatabaseReadFailure {
  return thrownResultErrorHasCode(thrown, 'NOT_FOUND') ||
    thrownResultErrorHasCode(thrown, 'GONE')
    ? TABLE_UNAVAILABLE
    : { kind: 'fetch', message: 'The table’s schema could not be read.' };
}

function sameIds(left: readonly string[], right: readonly string[]) {
  return (
    left.length === right.length &&
    left.every((id, index) => id === right[index])
  );
}

export function createDatabaseRowsSource(props: {
  databaseId: string;
  table: Accessor<TableDetail>;
  /** The view whose rows the engine reads, filtered and sorted as it says. */
  view: Accessor<DatabaseView>;
  /** Applies a write's ops to this database; reads run in the browser's SQL engine. */
  applyOps: (ops: DatabaseOp[]) => ResultAsync<OpResult[], DatabaseOpsError>;
  /** Where the engine reads rows from; the app's GraphQL client by default. */
  read?: DatabaseSqlQueryCapabilities;
  /** The live view reader; production pages its global server order. */
  viewQuery?: (
    statement: Accessor<DatabaseSqlStatement | undefined>
  ) => DatabaseViewQuery;
  /** Calls back with the version of each change the gateway reports for this table. */
  onTableChanged: (listener: (version: number) => void) => void;
  /** Calls back with the version each batch this viewer commits, such as an added or renamed column, moves this table to. */
  onCommitted: (listener: (version: number) => void) => void;
  /**
   * The journal and row cache an incremental refresh reads, given how to read rows by id.
   * Left out, a change reads the table whole.
   */
  changes?: (
    readRows: (rowIds: string[]) => ResultAsync<void, DatabaseReadFailure>
  ) => TableChangesCapabilities;
  applyVersions: (versions: Record<string, number>) => void;
  addOption: (
    columnId: string,
    label: string
  ) => ResultAsync<void, DatabaseOpsError>;
}): DatabaseRowsSource {
  const queryClient = useQueryClient();
  const tableId = props.table().table.id;
  const detailKey = databasesKeys.detail(props.databaseId).queryKey;
  let schemaStale = false;
  // Only advance across schema changes this writer has itself acknowledged.
  const inferredVersions = new Map<number, number>();
  // The newest version this viewer's own batches moved the table to. A new
  // column's type settles against it: its add and rename each moved the table.
  let committedVersion: number | undefined;
  props.onCommitted((version) => {
    committedVersion = Math.max(committedVersion ?? version, version);
  });
  const cachedDetail = () =>
    queryClient.getQueryData<DatabaseDetail>(detailKey);
  /**
   * The table as the cached schema has it; undefined once the schema no
   * longer lists it. Before the schema is cached the prop is all there is.
   */
  const currentTable = (): TableDetail | undefined => {
    const detail = cachedDetail();
    if (!detail) return props.table();
    return detail.tables.find((entry) => entry.table.id === tableId);
  };
  // Reads are rebuilt from the cached schema: a refreshed table name must
  // reach the retry even when the table prop has not caught up.
  const [schemaRefreshes, setSchemaRefreshes] = createSignal(0);

  function refreshSchema(): ResultAsync<void, DatabaseReadFailure> {
    const fetched = async () => {
      await queryClient.cancelQueries({ queryKey: detailKey, exact: true });
      return queryClient.fetchQuery({
        ...databaseDetailQueryOptions(props.databaseId),
        staleTime: 0,
      });
    };
    return ResultAsync.fromPromise(fetched(), schemaReadFailure).andThen(
      (detail) => {
        if (!detail.tables.some((entry) => entry.table.id === tableId))
          return err(TABLE_UNAVAILABLE);
        schemaStale = false;
        setSchemaRefreshes((count) => count + 1);
        return ok(undefined);
      }
    );
  }
  // Version-only schema updates must not recreate columns and remount editors.
  const details = createMemo(() => props.table().columns);
  /** A read of this table alone, built from the cached schema. */
  const tableStatement = (
    read: (
      table: TableDetail
    ) => { sql: string } | { view: DatabaseView } | undefined
  ) =>
    createMemo(
      (): DatabaseSqlStatement | undefined => {
        props.table();
        schemaRefreshes();
        const detail = cachedDetail();
        const table = currentTable();
        if (!detail || !table) return undefined;
        const statement = read(table);
        if (statement === undefined) return undefined;
        return {
          schema: databaseSqlSchema([{ ...detail, tables: [table] }]),
          scope: props.databaseId,
          ...statement,
        };
      },
      undefined,
      { equals: sameDatabaseSqlStatement }
    );
  const viewStatement = tableStatement(() => ({ view: props.view() }));
  // Board lanes are arranged from every row, so boards read through the engine.
  const engineView = () => props.view().layout.kind === 'board';
  const pagedQuery = (props.viewQuery ?? createDatabaseViewQuery)(() =>
    engineView() ? undefined : viewStatement()
  );
  const engineQuery = createDatabaseSqlQuery(
    () => (engineView() ? viewStatement() : undefined),
    props.read
  );
  const activeQuery = () => (engineView() ? engineQuery : pagedQuery);
  const currentRead = () => {
    const query = activeQuery();
    const outcome = query.outcome();
    const catalog = query.catalog();
    if (!outcome || !catalog) return undefined;
    const pagination = engineView() ? undefined : pagedQuery.pagination;
    return {
      outcome,
      catalog,
      hasMore: pagination?.hasMore() ?? false,
      version: pagination?.version(),
    };
  };
  // Switching a view between table and board switches readers; keep the last read meanwhile.
  const completed = createMemo<ReturnType<typeof currentRead>>((previous) =>
    viewStatement() ? (currentRead() ?? previous) : undefined
  );
  // Accepted writes may refresh a reader after its reactive owner is disposed.
  const shownRead = () => currentRead() ?? completed();
  const rowsQuery: DatabaseViewQuery = {
    outcome: () => shownRead()?.outcome,
    catalog: () => shownRead()?.catalog,
    loading: () => activeQuery().loading(),
    error: () => activeQuery().error(),
    cached: () => activeQuery().cached(),
    refresh: (reason) => activeQuery().refresh(reason),
    answerFromCache: () => activeQuery().answerFromCache(),
    pagination: pagedQuery.pagination && {
      hasMore: () => shownRead()?.hasMore ?? false,
      loading: () => pagedQuery.pagination!.loading(),
      version: () => shownRead()?.version,
      loadMore: () => pagedQuery.pagination!.loadMore(),
    },
  };
  // A type change gives a column a new definition. Until the read of it
  // lands, the column keeps the definition its shown cells were read with.
  const shownDetails = createMemo<ColumnDetail[]>((held) => {
    const answered = new Map(
      rowsQuery
        .catalog()
        ?.tables.flatMap((table) =>
          table.columns.map((column) => [column.placement, column.id] as const)
        )
    );
    return details().map((column) => {
      const read = answered.get(column.column.id);
      if (read === undefined || read === column.definition.definition.id)
        return column;
      return (
        held.find(
          (previous) =>
            previous.column.id === column.column.id &&
            previous.definition.definition.id === read
        ) ?? column
      );
    });
  }, []);
  const columns = createMemo(() => shownDetails().map(toViewColumn));
  const [retainedIds, setRetainedIds] = createSignal<
    Accessor<readonly string[]>
  >(() => []);
  const retainedRowIds = createMemo(
    () => [...new Set(retainedIds()())].sort(),
    [],
    { equals: sameIds }
  );
  const retainedStatement = tableStatement((table) =>
    retainedRowIds().length
      ? { sql: rowsByIdStatement(table.sql_name, retainedRowIds()) }
      : undefined
  );
  const retainedQuery = createDatabaseSqlQuery(retainedStatement, props.read);
  // The table's version when the last completed read began. Accepted draft
  // writes can outlive this owner, so it advances on any awaited refresh.
  const [readVersion, setReadVersion] = createSignal(
    untrack(() => (currentTable() ?? props.table()).table.version)
  );
  // The newest version this writer's row writes made; it reads them back itself.
  let writtenVersion = 0;

  function convertedRows(query: DatabaseSqlQuery) {
    let previous: GridRowsRead | undefined;
    return () => {
      const outcome = query.outcome();
      const catalog = query.catalog();
      if (!outcome || !catalog) return undefined;
      const columns = shownDetails();
      if (
        previous?.outcome === outcome &&
        previous.catalog === catalog &&
        previous.columns === columns
      )
        return previous.rows;
      previous = {
        outcome,
        catalog,
        columns,
        rows: gridRows(outcome, catalog, columns, previous),
      };
      return previous.rows;
    };
  }
  const viewRows = convertedRows(rowsQuery);
  const heldRows = convertedRows(retainedQuery);
  const retainedRows = () => {
    const ids = retainedRowIds();
    if (!ids.length) return [];
    return (heldRows() ?? []).filter((row) => ids.includes(row.rowId));
  };
  const snapshot = () => {
    const rows = viewRows();
    if (!rows) return undefined;
    return {
      version: rowsQuery.pagination?.version() ?? readVersion(),
      rows,
      retained: retainedRows(),
    };
  };

  /**
   * Read from the network again; what comes back is at least `version`. A
   * read a later one replaced shows nothing, so it leaves the version alone.
   */
  function readAgain(
    version: number,
    reason: DatabaseSqlReadReason
  ): ResultAsync<void, DatabaseReadFailure> {
    return ResultAsync.combine([
      rowsQuery.refresh(reason),
      retainedRowIds().length
        ? retainedQuery.refresh(reason)
        : okAsync<DatabaseSqlRun>({ landed: true }),
    ]).map(([rows, retained]) => {
      if (rows.landed && retained.landed)
        setReadVersion((previous) => Math.max(previous, version));
    });
  }
  function refresh(
    reason: 'after-write' | 'refresh' = 'refresh'
  ): ResultAsync<void, DatabaseReadFailure> {
    const span = Telemetry.span('database.rows.read_refresh');
    span.setAttr('database.id', props.databaseId);
    span.setAttr('database.table_id', tableId);
    span.setAttr('database.read_reason', reason);
    const reading = span.run(() => {
      const failed = rowsQuery.error();
      const staleSchema = failed && isStaleSchema(failed);
      const schema = staleSchema ? refreshSchema() : okAsync(undefined);
      span.setAttr('database.refresh_schema', Boolean(staleSchema));
      return schema.andThen(() => {
        const table = currentTable();
        return table
          ? readAgain(
              table.table.version,
              staleSchema ? 'schema-change' : reason
            )
          : errAsync<void, DatabaseReadFailure>(TABLE_UNAVAILABLE);
      });
    });
    return reading
      .andTee(() => {
        span.setAttr('database.outcome', 'success');
        span.end();
      })
      .orTee((failure) => {
        span.setAttr('database.outcome', 'error');
        span.setAttr('database.error_kind', failure.kind);
        span.error(failure.kind);
        span.run(() =>
          Telemetry.warn('database rows could not be refreshed', {
            databaseId: props.databaseId,
            tableId,
            reason,
            failureKind: failure.kind,
          })
        );
        span.end();
      });
  }

  /** Read these rows again by id, through the engine, into the cache the view reads. */
  function readRows(rowIds: string[]): ResultAsync<void, DatabaseReadFailure> {
    const detail = cachedDetail();
    const table = currentTable();
    if (!detail || !table) return errAsync(TABLE_UNAVAILABLE);
    return readDatabaseSql(
      {
        schema: databaseSqlSchema([{ ...detail, tables: [table] }]),
        scope: props.databaseId,
        sql: rowsByIdStatement(table.sql_name, rowIds),
      },
      props.read,
      'websocket'
    ).map(() => undefined);
  }
  /** Answer the open reads from the cache rows were read into; at least `version` once they land. */
  function answerFromCache(
    version: number
  ): ResultAsync<void, DatabaseReadFailure> {
    return ResultAsync.combine([
      rowsQuery.answerFromCache(),
      retainedRowIds().length
        ? retainedQuery.answerFromCache()
        : okAsync<DatabaseSqlRun>({ landed: true }),
    ]).map(([rows, retained]) => {
      if (rows.landed && retained.landed)
        setReadVersion((previous) => Math.max(previous, version));
    });
  }
  const changes = props.changes?.(readRows);
  // Another viewer's edit. This writer's own edits read their version back.
  props.onTableChanged((version) => {
    if (version <= Math.max(readVersion(), writtenVersion)) return;
    const target = Math.max(version, currentTable()?.table.version ?? version);
    const fullRead = () => readAgain(target, 'websocket');
    refreshInBackground({
      // Rows read by id reach the view only through a local cache; without one, read whole.
      refresh: () =>
        changes && rowsQuery.cached()
          ? refreshChangedRows({
              from: readVersion(),
              version: target,
              changes,
              answerFromCache,
              fullRead,
            })
          : fullRead(),
    });
  });

  function columnForWrite(
    table: TableDetail,
    columnId: string
  ): Result<ColumnDetail, DatabaseCellFailure> {
    const column = table.columns.find(
      (column) => column.column.id === columnId
    );
    return column?.writable ? ok(column) : err({ kind: 'read-only-column' });
  }

  /** The base a new column's type is settled against, past the settlements this writer made from it. */
  function latestInferenceBase(
    inferenceBaseVersion: number | undefined
  ): number | undefined {
    let base =
      inferenceBaseVersion === undefined || committedVersion === undefined
        ? (inferenceBaseVersion ?? committedVersion)
        : Math.max(inferenceBaseVersion, committedVersion);
    while (base !== undefined && inferredVersions.has(base))
      base = inferredVersions.get(base);
    return base;
  }

  /** Ask the service to type a new column, against the table at `base`. */
  async function inferColumnType(
    columnId: string,
    type: DatabaseColumnType,
    base: number
  ): Promise<
    Result<
      { column: ColumnDetail; base: number },
      ResultError<DatabaseSchemaErrorCode>[]
    >
  > {
    return Telemetry.span('database.column.infer_type', async (span) => {
      span.setAttr('database.column_type', type.dataType);
      const inferred = await storageServiceClient.databases.inferColumnType({
        id: props.databaseId,
        tableId,
        columnId,
        request: {
          dataType: type.dataType,
          ...(type.dataType === 'ENTITY'
            ? { specificEntityType: type.entityType }
            : {}),
          baseVersion: base,
        },
      });
      if (inferred.isErr()) {
        span.setAttr('database.outcome', 'error');
        return err(inferred.error);
      }
      span.event('schema_committed');
      const settled = inferred.value;
      inferredVersions.set(base, settled.table_version);
      await patchTableColumn(queryClient, {
        databaseId: props.databaseId,
        tableId,
        columnId,
        tableVersion: settled.table_version,
        change: () => settled.column,
      });
      span.setAttr('database.outcome', 'success');
      return ok({ column: settled.column, base: settled.table_version });
    });
  }

  /**
   * Settle a new column's type from its first value. Another first entry
   * may have typed it meanwhile; then the value is written against that type.
   * A table that moved since `base` is typed again at the version read now.
   * A column still left untyped takes an inferred value as text, so the
   * entry is never dropped; only a type the viewer picked is refused.
   */
  async function settleColumnType(
    columnId: string,
    type: DatabaseColumnType,
    base: number,
    picked: boolean
  ): Promise<
    Result<{ column: ColumnDetail; base: number }, DatabaseWriteFailure>
  > {
    const inferred = await inferColumnType(columnId, type, base);
    if (inferred.isOk()) return ok(inferred.value);
    const competing = inferred.error.some((error) => error.code === 'CONFLICT');
    // A failed refresh leaves the cached schema to decide.
    await refreshSchema();
    const table = currentTable();
    if (!table) return err(TABLE_UNAVAILABLE);
    const refreshed = columnForWrite(table, columnId);
    if (refreshed.isErr()) return err(refreshed.error);
    const current = { column: refreshed.value, base: table.table.version };
    if (!refreshed.value.column.infer_type)
      return competing
        ? ok(current)
        : err({ kind: 'type-refused', errors: inferred.error });
    const retried =
      competing && table.table.version !== base
        ? await inferColumnType(columnId, type, table.table.version)
        : inferred;
    if (retried.isOk()) return ok(retried.value);
    return picked
      ? err({ kind: 'type-refused', errors: retried.error })
      : ok(current);
  }

  /** A first value as its column takes it: a number column's text read as a number. */
  function fittedValue(
    column: ColumnDetail,
    value: string | number,
    numeric: number | undefined,
    requested: DatabaseColumnType | undefined
  ): Result<DatabaseCellValue, DatabaseWriteFailure> {
    const definition = column.definition.definition;
    if (
      requested?.dataType === 'ENTITY' &&
      (definition.data_type !== 'ENTITY' ||
        definition.specific_entity_type !== requested.entityType)
    )
      return err({ kind: 'type-mismatch' });
    if (definition.data_type !== 'NUMBER') return ok(value);
    return numeric === undefined ? err({ kind: 'not-a-number' }) : ok(numeric);
  }

  /** Settle the type of new columns from their first value, then fit values to their columns. */
  async function prepareFirstValues(
    mutation: DatabaseRowMutation,
    inferenceBaseVersion: number | undefined
  ): Promise<Result<DatabaseRowMutation, DatabaseWriteFailure>> {
    if (mutation.kind === 'delete' || mutation.kind === 'clear')
      return ok(mutation);
    let base = latestInferenceBase(inferenceBaseVersion);
    const values =
      mutation.kind === 'cell'
        ? { [mutation.columnId]: mutation.value }
        : { ...mutation.values };
    for (const [columnId, value] of Object.entries(values)) {
      if (value === null || value === '') continue;
      const table = currentTable();
      if (!table) return err(TABLE_UNAVAILABLE);
      const found = columnForWrite(table, columnId);
      if (found.isErr()) return err(found.error);
      let column = found.value;
      if (column.column.config?.kind === 'link') continue;
      const requested = mutation.columnTypes?.[columnId];
      const numeric =
        typeof value === 'number' ? value : inferDatabaseNumber(value);
      if (column.column.infer_type) {
        if (base === undefined) return err({ kind: 'needs-refresh' });
        const settled = await settleColumnType(
          columnId,
          requested ?? {
            dataType: numeric === undefined ? 'STRING' : 'NUMBER',
          },
          base,
          requested !== undefined
        );
        if (settled.isErr()) return err(settled.error);
        column = settled.value.column;
        base = settled.value.base;
      }
      const fitted = fittedValue(column, value, numeric, requested);
      if (fitted.isErr()) return err(fitted.error);
      values[columnId] = fitted.value;
    }
    return ok(
      mutation.kind === 'cell'
        ? { ...mutation, value: values[mutation.columnId] }
        : { ...mutation, values }
    );
  }

  async function writeRows(
    mutation: DatabaseRowMutation,
    inferenceBaseVersion: number | undefined,
    createOptions: boolean
  ): Promise<Result<DatabaseWriteResult, DatabaseWriteFailure>> {
    const prepared = await Telemetry.span('database.rows.prepare', async () =>
      prepareFirstValues(mutation, inferenceBaseVersion)
    );
    if (prepared.isErr()) return err(prepared.error);
    // Read the refreshed cache directly; Solid props may notify after fetchQuery resolves.
    const table = currentTable();
    if (!table) return err(TABLE_UNAVAILABLE);
    const written = await writeDatabaseRow(
      {
        tableId,
        columns: table.columns.map(toViewColumn),
        applyOps: ({ ops }) =>
          props.applyOps(ops).map((results) => ({ results })),
      },
      prepared.value,
      createOptions
    );
    if (written.isErr()) return written;
    writtenVersion = Math.max(writtenVersion, written.value.version);
    props.applyVersions({ [tableId]: written.value.version });
    // New options live in the schema; read it again in the background
    // so they show as options without suspending the grid.
    if (createOptions)
      queryClient.invalidateQueries({
        queryKey: detailKey,
        exact: true,
      });
    return written;
  }

  async function write(
    mutation: DatabaseRowMutation,
    inferenceBaseVersion: number | undefined,
    createOptions: boolean
  ): Promise<Result<DatabaseWriteResult, DatabaseWriteFailure>> {
    if (schemaStale) {
      const refreshed = await refreshSchema();
      if (refreshed.isErr())
        return err(
          refreshed.error.kind === 'table-unavailable'
            ? TABLE_UNAVAILABLE
            : { kind: 'schema-unreachable' }
        );
    }
    const written = await writeRows(
      mutation,
      inferenceBaseVersion,
      createOptions
    );
    if (written.isErr() && isStaleSchema(written.error)) {
      schemaStale = true;
      // The failed edit keeps its own error; the next write refreshes first.
      await refreshSchema();
    }
    return written;
  }

  return {
    pagination: rowsQuery.pagination,
    columns,
    snapshot,
    read: () => {
      const outcome = rowsQuery.outcome();
      const catalog = rowsQuery.catalog();
      const statement = viewStatement();
      return outcome && catalog && statement?.view
        ? { outcome, catalog, view: statement.view }
        : undefined;
    },
    loading: () => !rowsQuery.outcome() && rowsQuery.error() === undefined,
    refreshing: () =>
      rowsQuery.loading() ||
      (retainedRowIds().length > 0 && retainedQuery.loading()),
    error: () =>
      rowsQuery.error() ??
      (retainedRowIds().length > 0 ? retainedQuery.error() : undefined),
    refresh,
    addOption: props.addOption,
    retain: (rowIds) => setRetainedIds(() => rowIds),
    write: (mutation, inferenceBaseVersion, createOptions) =>
      new ResultAsync(write(mutation, inferenceBaseVersion, createOptions)),
  };
}