Open-source manufacturing ERP, MES and QMS. Quoting, MRP, inventory, shop floor, quality and lot/serial traceability on one Postgres schema, with a REST API and MCP server. Self-host or use Carbon Cloud.
TypeScript ai-agentsbill-of-materialserpinventory-managementleanmanufacturingmanufacturing-execution-systemmcp-servermesmrpopen-source-erppostgresqlproduction-planningqmsquality-management-systemreact-routerself-hostedsupabasetraceabilitytypescript
// SPDX-License-Identifier: AGPL-3.0-only
// Carbon (github.com/crbnos/carbon). Modified or adapted versions of this file,
// including ports, remain AGPLv3; serving them over a network requires releasing their source.
import type {
PostgrestClientOptions,
PostgrestError,
PostgrestFilterBuilder
} from "@supabase/postgrest-js";
import type { SupabaseClient } from "@supabase/supabase-js";
import type { Kysely } from "kysely";
import type { KyselyDatabase } from "./client.ts";
import type { Database } from "./types.ts";
/**
* Job statuses that participate in scheduling/planning ("open" work).
* The DB enum can't express this business subset itself; `satisfies` binds
* these values to the enum so a typo or an enum rename is a compile error,
* and every consumer imports THIS constant instead of re-hardcoding strings.
*/
export const activeJobStatuses = [
"Ready",
"In Progress",
"Paused"
] as const satisfies readonly Database["public"]["Enums"]["jobStatus"][];
/**
* `journalLine.documentLineReference` values: which source line a posted
* journal line came from (`purchase-invoice:<id>`, `receipt:<id>`, …).
*/
export const journalReference = {
to: {
purchaseInvoice: (id: string) => `purchase-invoice:${id}`,
receipt: (id: string) => `receipt:${id}`,
salesInvoice: (id: string) => `sales-invoice:${id}`,
shipment: (id: string) => `shipment:${id}`,
job: (id: string) => `job:${id}`,
materialIssue: (id: string) => `material-issue:${id}`,
productionEvent: (id: string) => `production-event:${id}`,
maintenanceEvent: (id: string) => `maintenance-event:${id}`
}
};
/**
* Either data-access handle a helper might receive: a Supabase client or a
* Kysely handle (a `Kysely` instance or an active `Transaction`). Use it to
* overload a helper that some callers reach with a client and others with
* Kysely, instead of maintaining two near-identical `*Db` twins.
*/
export type AnyPostgresClient =
| SupabaseClient<Database>
| Kysely<KyselyDatabase>;
/**
* Runtime guard narrowing {@link AnyPostgresClient} to the Kysely handle. Kysely
* exposes `.selectFrom`; the Supabase client does not.
*/
export const isKysely = (db: AnyPostgresClient): db is Kysely<KyselyDatabase> =>
typeof (db as Kysely<KyselyDatabase>).selectFrom === "function";
const BATCH_SIZE = 1000;
// How many pages to request at once past the first. Serial paging made a 150k-row
// table 150 sequential round trips.
const PAGE_CONCURRENCY = 4;
// Backstop only: a server that ignored `Range` would otherwise loop forever.
const MAX_PAGES = 1000;
export type PaginatedResult<T> =
| {
data: T[];
count: number;
error: null;
}
| {
data: null;
count: null;
error: PostgrestError;
};
/**
* What paging needs from a query: `.range()` resolving to a PostgREST result.
* Structural on purpose — naming `PostgrestFilterBuilder` here made the
* compiler compare an app's whole schema against it for every call, which no
* embed-heavy select or RPC survived ("excessively deep").
*/
type PageQuery<T extends object> = {
range(
from: number,
to: number
): PromiseLike<{ data: T[] | null; error: PostgrestError | null }>;
};
/**
* Fetches all records from a table by automatically handling pagination
* to work around Supabase's 1000 row limit per request.
*
* Takes a FACTORY, not a query: supabase-js builders are mutable — `.range()`
* sets `this.url.searchParams` and returns `this` — so concurrent awaits on one
* builder would all fetch whichever range was set last. `fetchAll`
* (`./fetch-all.ts`) is the serial, loosely typed variant.
*/
export async function fetchAllRecords<T extends object>(
buildQuery: () => PageQuery<T>
): Promise<PaginatedResult<T>> {
const fetchPage = async (page: number) =>
await buildQuery().range(page * BATCH_SIZE, (page + 1) * BATCH_SIZE - 1);
// The first page alone, so a table that fits in one page stays at one request.
const first = await fetchPage(0);
if (first.error) {
return { data: null, count: null, error: first.error };
}
const allData: T[] = first.data ?? [];
if (allData.length < BATCH_SIZE) {
return { data: allData, count: allData.length, error: null };
}
for (let page = 1; page < MAX_PAGES; page += PAGE_CONCURRENCY) {
const results = await Promise.all(
Array.from({ length: PAGE_CONCURRENCY }, (_, i) => fetchPage(page + i))
);
// In page order, so the first SHORT page ends the read. Pages after it are
// speculative — they were issued before we knew where the data stopped, and
// a read past the end can legitimately fail (PostgREST answers an
// out-of-range `Range` with 416). Scanning every result for an error first
// would turn that into a failed fetch after the rows were already in hand.
for (const result of results) {
if (result.error) {
return { data: null, count: null, error: result.error };
}
const rows = (result.data ?? []) as T[];
allData.push(...rows);
if (rows.length < BATCH_SIZE) {
return { data: allData, count: allData.length, error: null };
}
}
}
return {
data: null,
count: null,
error: {
message: `fetchAllRecords exceeded ${MAX_PAGES} pages — refusing to return a partial read`,
details: "",
hint: "",
code: "PGRST_PAGINATION_LIMIT",
name: "PostgrestError"
} as PostgrestError
};
}
// Ids per request. `.in()` writes every id into the URL, and the gateway
// rejects a request line it cannot buffer (HTTP 431); 100 ids is about 3 kB.
const IN_FILTER_BATCH_SIZE = 100;
/**
* Reads every row for an id list. Walks the ids in groups of 100, so each
* `.in()` fits in the request URL, and pages each group past the row cap with
* `fetchAllRecords`. The factory gets one group and must order its query, so
* the pages of a group are stable. A failed group fails the whole read: a
* partial list would read as "no rows" for the ids it dropped.
*/
export async function fetchAllByIds<T extends object>(
ids: readonly string[],
buildQuery: (batch: string[]) => PageQuery<T>
): Promise<PaginatedResult<T>> {
const rows: T[] = [];
for (let start = 0; start < ids.length; start += IN_FILTER_BATCH_SIZE) {
const batch = ids.slice(start, start + IN_FILTER_BATCH_SIZE);
const result = await fetchAllRecords(() => buildQuery(batch));
if (result.error) return { data: null, count: null, error: result.error };
rows.push(...result.data);
}
return { data: rows, count: rows.length, error: null };
}
/**
* Helper function for simple table queries that need all records
*/
export async function fetchAllFromTable<T extends object>(
client: SupabaseClient<Database>,
tableName:
| keyof Database["public"]["Tables"]
| keyof Database["public"]["Views"],
selectColumns: string = "*",
filterFn?: (query: any) => any
): Promise<PaginatedResult<T>> {
// No `count: "exact"` — that is a COUNT(*) OVER () across the whole filtered
// set on EVERY page, and no caller reads the returned count.
const buildQuery = () => {
const query = client
// @ts-expect-error
.from(tableName)
.select(selectColumns);
return (filterFn ? filterFn(query) : query) as PageQuery<T>;
};
return fetchAllRecords<T>(buildQuery);
}
/**
* Fetches records with automatic batching for queries that might exceed 1000 rows
* Used when you need all records but want to process them in batches
*/
export async function* fetchRecordsInBatches<T extends object>(
baseQuery: PostgrestFilterBuilder<
PostgrestClientOptions,
Database["public"],
Record<string, unknown>,
T[]
>,
batchSize: number = BATCH_SIZE
): AsyncGenerator<{ data: T[]; batch: number; hasMore: boolean }> {
let offset = 0;
let batch = 0;
let hasMore = true;
while (hasMore) {
const query = baseQuery.range(offset, offset + batchSize - 1);
const result = await query;
if (result.error) {
throw new Error(`Batch query failed: ${result.error.message}`);
}
hasMore = result.data && result.data.length === batchSize;
batch++;
yield {
data: result.data || [],
batch,
hasMore
};
offset += batchSize;
}
}