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 RawBuilder, sql } from "kysely";
import type { Kysely, KyselyDatabase } from "./client.ts";
import type { Database } from "./types.ts";
type Relations = Database["public"]["Tables"] & Database["public"]["Views"];
type RelationName = keyof Relations & string;
type RowOf<T extends RelationName> = Relations[T]["Row"];
/**
* Matches rows where the column IS NULL, as `.is(column, null)` does. A plain
* `null` (or `undefined`) value is `.eq(column, null)`, which matches nothing.
*/
export const isNull = Symbol("IS NULL");
type Operator =
| "<>"
| "<"
| "<="
| ">"
| ">="
| "@>"
| "IS NOT NULL"
| "NOT IN";
/** A comparison other than equality. A class, so a jsonb VALUE shaped like
* `{ op, value }` stays a value and never becomes SQL. */
class Comparison {
constructor(
readonly op: Operator,
readonly value?: unknown
) {}
}
export const neq = (value: unknown) => new Comparison("<>", value);
export const lt = (value: unknown) => new Comparison("<", value);
export const lte = (value: unknown) => new Comparison("<=", value);
export const gt = (value: unknown) => new Comparison(">", value);
export const gte = (value: unknown) => new Comparison(">=", value);
/** `.contains(column, value)` on a jsonb column: the form its GIN index serves. */
export const contains = (value: unknown) => new Comparison("@>", value);
/**
* `.not(column, "in", values)`. A null column matches no non-empty list; an
* empty list matches every row, as PostgREST's does.
*/
export const notIn = (values: readonly unknown[]) =>
new Comparison("NOT IN", values);
/** `.not(column, "is", null)`. */
export const notNull = new Comparison("IS NOT NULL");
type Where<T extends RelationName> = {
[K in keyof RowOf<T> & string]?:
| RowOf<T>[K]
| NonNullable<RowOf<T>[K]>[]
| typeof isNull
| Comparison
| null;
};
/** A PostgREST-style embed: `on` is the related table's column holding this
* row's id (array), `via` is this row's column holding the related id (row). */
export type Embed = {
[property: string]: {
table: RelationName;
columns?: readonly string[];
embed?: Embed;
} & ({ on: string; via?: never } | { via: string; on?: never });
};
type ReadOptions<T extends RelationName> = {
columns?: readonly (keyof RowOf<T> & string)[];
embed?: Embed;
};
type Column<T extends RelationName> = keyof RowOf<T> & string;
type OrderBy<T extends RelationName> = {
/** Ascending, or `{ desc: column }`. */
orderBy?: (Column<T> | { desc: Column<T> })[];
limit?: number;
};
/**
* Rows read over the direct connection, shaped as PostgREST returns them
* (`to_jsonb`: timestamps are full-precision strings, embeds are arrays), so
* values copied into other rows keep their shape. Throws on failure.
*/
export function selectRows<T extends RelationName, const C extends Column<T>>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>,
options: { columns: readonly C[] } & OrderBy<T>
): Promise<Pick<RowOf<T>, C>[]>;
export function selectRows<T extends RelationName, R = RowOf<T>>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>,
options?: ReadOptions<T> & OrderBy<T>
): Promise<R[]>;
export async function selectRows<T extends RelationName>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>,
options: ReadOptions<T> & OrderBy<T> = {}
): Promise<unknown[]> {
const alias = "t0";
const conditions = conditionsFor(alias, where);
const order = (options.orderBy ?? []).map((column) =>
typeof column === "string"
? sql.ref(`${alias}.${column}`)
: sql`${sql.ref(`${alias}.${column.desc}`)} DESC`
);
const { rows } = await sql<{ row: unknown }>`
SELECT ${rowExpression(alias, options.columns, options.embed, 1)} AS row
FROM ${sql.table(table)} AS ${sql.ref(alias)}
${conditions.length > 0 ? sql`WHERE ${sql.join(conditions, sql` AND `)}` : sql``}
${order.length > 0 ? sql`ORDER BY ${sql.join(order)}` : sql``}
${options.limit === undefined ? sql`` : sql`LIMIT ${options.limit}`}
`.execute(db);
return rows.map((r) => r.row);
}
export async function selectRow<T extends RelationName, R = RowOf<T>>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>,
options: ReadOptions<T> = {}
): Promise<R | undefined> {
const rows = await selectRows<T, R>(db, table, where, {
limit: 1,
...options
});
return rows[0];
}
function rowExpression(
alias: string,
columns: readonly string[] | undefined,
embed: Embed | undefined,
depth: number
): RawBuilder<unknown> {
// A column list is projected in a subquery: jsonb_build_object would cap it
// at fifty columns.
const row = columns
? sql`(SELECT to_jsonb(c) FROM (SELECT ${sql.join(
columns.map((column) => sql.ref(`${alias}.${column}`))
)}) AS c)`
: sql`to_jsonb(${sql.ref(alias)})`;
const children = Object.entries(embed ?? {});
if (children.length === 0) return row;
const child = `t${depth}`;
const properties = children.map(([property, spec]) => {
const related = rowExpression(child, spec.columns, spec.embed, depth + 1);
const from = sql`FROM ${sql.table(spec.table)} AS ${sql.ref(child)}`;
return spec.via === undefined
? sql`${sql.lit(property)}, (
SELECT coalesce(jsonb_agg(${related}), '[]'::jsonb) ${from}
WHERE ${sql.ref(`${child}.${spec.on}`)} = ${sql.ref(`${alias}.id`)}
)`
: sql`${sql.lit(property)}, (
SELECT ${related} ${from}
WHERE ${sql.ref(`${child}.id`)} = ${sql.ref(`${alias}.${spec.via}`)}
)`;
});
return sql`${row} || jsonb_build_object(${sql.join(properties)})`;
}
function conditionsFor(alias: string, where: Record<string, unknown>) {
return Object.entries(where).map(([column, value]) => {
const ref = sql.ref(`${alias}.${column}`);
if (value === isNull) return sql`${ref} IS NULL`;
// PostgREST's eq never matches a null: keep a missing id from selecting
// every row whose column happens to be empty.
if (value === null || value === undefined) return sql`FALSE`;
if (Array.isArray(value)) return sql`${ref} = ANY(${value})`;
if (value instanceof Comparison) {
switch (value.op) {
case "IS NOT NULL":
return sql`${ref} IS NOT NULL`;
case "NOT IN":
return sql`NOT (${ref} = ANY(${value.value}))`;
case "@>":
return sql`${ref} @> ${JSON.stringify(value.value)}::jsonb`;
case "<>":
return sql`${ref} <> ${value.value}`;
case "<":
return sql`${ref} < ${value.value}`;
case "<=":
return sql`${ref} <= ${value.value}`;
case ">":
return sql`${ref} > ${value.value}`;
case ">=":
return sql`${ref} >= ${value.value}`;
}
}
return sql`${ref} = ${value}`;
});
}
type Functions = Database["public"]["Functions"];
export async function rpcRows<F extends keyof Functions & string>(
db: Kysely<KyselyDatabase>,
fn: F,
args: Functions[F]["Args"]
): Promise<{
data: Extract<Functions[F]["Returns"], unknown[]>;
error: Error | null;
}> {
const named = Object.entries(args as Record<string, unknown>).map(
([name, value]) => sql`${sql.ref(name)} => ${value}`
);
const { rows } = await sql<{ row: unknown }>`
SELECT to_jsonb(r) AS row FROM ${sql.ref(fn)}(${sql.join(named)}) AS r
`.execute(db);
return {
data: rows.map((r) => r.row) as Extract<Functions[F]["Returns"], unknown[]>,
error: null
};
}
export async function rpcValue<F extends keyof Functions & string>(
db: Kysely<KyselyDatabase>,
fn: F,
args: Functions[F]["Args"]
): Promise<{ data: Functions[F]["Returns"] | null; error: Error | null }> {
const named = Object.entries(args as Record<string, unknown>).map(
([name, value]) => sql`${sql.ref(name)} => ${value}`
);
const { rows } = await sql<{ row: Functions[F]["Returns"] }>`
SELECT to_jsonb(r) AS row FROM ${sql.ref(fn)}(${sql.join(named)}) AS r
`.execute(db);
return { data: rows[0]?.row ?? null, error: null };
}
type TableName = keyof Database["public"]["Tables"] & string;
type Written = { error: Error | null };
/** The keys a JSON body would carry: `undefined` is dropped, `null` is kept. */
function definedKeys(row: Record<string, unknown>): string[] {
return Object.keys(row).filter((key) => row[key] !== undefined);
}
/** Rows go in as one JSON document cast by Postgres, as PostgREST does: a
* jsonb array stays an array, and a key one row lacks is NULL for it. */
export async function insertRows<T extends TableName>(
db: Kysely<KyselyDatabase>,
table: T,
rows:
| Database["public"]["Tables"][T]["Insert"]
| Database["public"]["Tables"][T]["Insert"][]
): Promise<Written & { data: Database["public"]["Tables"][T]["Row"][] }> {
const list = (Array.isArray(rows) ? rows : [rows]) as Record<
string,
unknown
>[];
const columns = [...new Set(list.flatMap(definedKeys))];
if (list.length === 0 || columns.length === 0) {
return { data: [], error: null };
}
const refs = sql.join(columns.map((column) => sql.ref(column)));
const result = await sql<{ row: Database["public"]["Tables"][T]["Row"] }>`
INSERT INTO ${sql.table(table)} AS t0 (${refs})
SELECT ${refs}
FROM jsonb_populate_recordset(null::${sql.table(table)}, ${JSON.stringify(list)}::jsonb)
RETURNING to_jsonb(t0) AS row
`.execute(db);
return { data: result.rows.map((r) => r.row), error: null };
}
export async function updateRows<T extends TableName>(
db: Kysely<KyselyDatabase>,
table: T,
set: Database["public"]["Tables"][T]["Update"],
where: Where<T>
): Promise<Written> {
const columns = definedKeys(set as Record<string, unknown>);
const conditions = conditionsFor("t0", where);
if (columns.length === 0) return { error: null };
if (conditions.length === 0) {
throw new Error(`updateRows on ${table} needs a filter`);
}
await sql`
UPDATE ${sql.table(table)} AS t0
SET ${sql.join(columns.map((column) => sql`${sql.ref(column)} = r.${sql.ref(column)}`))}
FROM jsonb_populate_record(null::${sql.table(table)}, ${JSON.stringify(set)}::jsonb) AS r
WHERE ${sql.join(conditions, sql` AND `)}
`.execute(db);
return { error: null };
}
export async function deleteRows<T extends TableName>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>
): Promise<Written> {
const conditions = conditionsFor("t0", where);
if (conditions.length === 0) {
throw new Error(`deleteRows on ${table} needs a filter`);
}
await sql`
DELETE FROM ${sql.table(table)} AS t0
WHERE ${sql.join(conditions, sql` AND `)}
`.execute(db);
return { error: null };
}
type Result<R> = { data: R; error: null } | { data: null; error: Error };
/** PostgREST's `.single()`: exactly one row, otherwise an error and no data. */
export function single<T extends RelationName, const C extends Column<T>>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>,
options: { columns: readonly C[] } & OrderBy<T>
): Promise<Result<Pick<RowOf<T>, C>>>;
export function single<T extends RelationName, R = RowOf<T>>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>,
options?: ReadOptions<T> & OrderBy<T>
): Promise<Result<R>>;
export async function single<T extends RelationName>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>,
options: ReadOptions<T> & OrderBy<T> = {}
): Promise<Result<unknown>> {
const rows = await selectRows<T, unknown>(db, table, where, options);
return rows.length === 1
? { data: rows[0], error: null }
: {
data: null,
error: new Error(`Expected one ${table} row, found ${rows.length}`)
};
}
/** `.maybeSingle()`: no row is `data: null` without an error; two is an error. */
export function maybeSingle<T extends RelationName, const C extends Column<T>>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>,
options: { columns: readonly C[] } & OrderBy<T>
): Promise<{ data: Pick<RowOf<T>, C> | null; error: Error | null }>;
export function maybeSingle<T extends RelationName, R = RowOf<T>>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>,
options?: ReadOptions<T> & OrderBy<T>
): Promise<{ data: R | null; error: Error | null }>;
export async function maybeSingle<T extends RelationName>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>,
options: ReadOptions<T> & OrderBy<T> = {}
): Promise<{ data: unknown; error: Error | null }> {
const rows = await selectRows<T, unknown>(db, table, where, options);
return rows.length > 1
? {
data: null,
error: new Error(
`Expected at most one ${table} row, found ${rows.length}`
)
}
: { data: rows[0] ?? null, error: null };
}
/** `error` is always null (a failure throws); typed so existing checks compile. */
export function many<T extends RelationName, const C extends Column<T>>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>,
options: { columns: readonly C[] } & OrderBy<T>
): Promise<{ data: Pick<RowOf<T>, C>[]; error: Error | null }>;
export function many<T extends RelationName, R = RowOf<T>>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>,
options?: ReadOptions<T> & OrderBy<T>
): Promise<{ data: R[]; error: Error | null }>;
export async function many<T extends RelationName>(
db: Kysely<KyselyDatabase>,
table: T,
where: Where<T>,
options: ReadOptions<T> & OrderBy<T> = {}
): Promise<{ data: unknown[]; error: Error | null }> {
return {
data: await selectRows<T, unknown>(db, table, where, options),
error: null
};
}
export type Tables = Database["public"]["Tables"];
export type Views = Database["public"]["Views"];
/** `Promise.all` for reads, one after another: each concurrent read would
* take its own connection from the pool and save almost nothing. */
export async function inOrder<const T extends readonly (() => unknown)[]>(
reads: T
): Promise<{ -readonly [K in keyof T]: Awaited<ReturnType<T[K]>> }> {
const results: unknown[] = [];
for (const read of reads) results.push(await read());
return results as { -readonly [K in keyof T]: Awaited<ReturnType<T[K]>> };
}