Skip to content

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.

22.1 KB
Raw Blame
-- Generated by `pnpm --filter @carbon/database authz migration` from packages/database/src/authz. Do not edit.

-- Ships these rules to databases where `authz sync` does not run after migrations yet.

CREATE OR REPLACE FUNCTION public.broadcast_reference_changes()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
DECLARE
  ignored_columns CONSTANT TEXT[] := ARRAY['updatedAt', 'updatedBy', 'embedding', 'loadedJobOperationId', 'loadedAt'];
  changed_rows TEXT;
  rec RECORD;
BEGIN
  -- A database without Supabase Realtime (a self-hosted install that does not
  -- run it) has nothing to send to: the write must still succeed.
  IF to_regprocedure('realtime.send(jsonb,text,text,boolean)') IS NULL THEN
    RETURN NULL;
  END IF;

  -- Tells the company's clients that a reference list changed (one shared topic, company:<companyId>:reference).
  -- Statement-level (attach_statement_handler): one message per company per
  -- statement, whatever the row count. No row data leaves the database: a client
  -- re-reads what it needs through PostgREST, so table RLS still decides what it sees.
  IF TG_OP = 'UPDATE' THEN
    -- A row whose only changes are in ignored_columns drops out (the rule
    -- dispatch_event_batch applies; event-dispatch.test.ts keeps the lists equal).
    changed_rows := 'SELECT to_jsonb(n) - $1 AS c FROM batched_new n
                     EXCEPT
                     SELECT to_jsonb(o) - $1 FROM batched_old o';
  ELSIF TG_OP = 'DELETE' THEN
    changed_rows := 'SELECT to_jsonb(o) AS c FROM batched_old o';
  ELSE
    changed_rows := 'SELECT to_jsonb(n) AS c FROM batched_new n';
  END IF;

  FOR rec IN EXECUTE format(
    'SELECT DISTINCT c->>''companyId'' AS company_id FROM (%s) changed',
    changed_rows
  ) USING ignored_columns
  LOOP
    CONTINUE WHEN rec.company_id IS NULL;
    PERFORM realtime.send(
      jsonb_build_object('table', TG_TABLE_NAME, 'op', TG_OP),
      TG_OP,
      'company:' || rec.company_id || ':reference',
      true
    );
  END LOOP;
  RETURN NULL;
END;
$function$;

CREATE OR REPLACE FUNCTION public.broadcast_table_changes()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
DECLARE
  ignored_columns CONSTANT TEXT[] := ARRAY['updatedAt', 'updatedBy', 'embedding', 'loadedJobOperationId', 'loadedAt'];
  -- Tables whose rows hang off a record through another table. Each hop is
  -- [column of the row, table it points at, column to read there]: its value is
  -- added to the message under that column's name, so a job page can follow
  -- "jobId" on a table that only knows its operation.
  -- A hop with a fourth element looks the other way: the rows of that table
  -- whose fourth column holds this row's value ("id" is the row's own id). A
  -- payment has no invoice column; its settlements name the invoices it pays.
  -- packages/database/src/realtime-tables.ts lists the same columns
  -- (REALTIME_ANCESTOR_COLUMNS; realtime-tables.test.ts keeps them equal).
  ancestors CONSTANT JSONB := '{
    "productionEvent": [["jobOperationId", "jobOperation", "jobId"]],
    "jobOperationStep": [["operationId", "jobOperation", "jobId"]],
    "jobOperationStepRecord": [["jobOperationStepId", "jobOperationStep", "operationId"], ["operationId", "jobOperation", "jobId"]],
    "trackedActivity": [["sourceDocumentId", "productionEvent", "jobOperationId"]],
    "payment": [["id", "invoiceSettlement", "targetSalesInvoiceId", "paymentId"], ["id", "invoiceSettlement", "targetPurchaseInvoiceId", "paymentId"]]
  }';
  changed_rows TEXT;
  parent_rows TEXT;
  parents JSONB;
  sent_columns TEXT[];
  found JSONB;
  hop JSONB;
  rec RECORD;
BEGIN
  -- A database without Supabase Realtime (a self-hosted install that does not
  -- run it) has nothing to send to: the write must still succeed.
  IF to_regprocedure('realtime.send(jsonb,text,text,boolean)') IS NULL THEN
    RETURN NULL;
  END IF;

  -- Tells the company's clients which rows of this table changed (topic company:<companyId>:<table>).
  -- Statement-level (attach_statement_handler): one message per company per
  -- statement, whatever the row count. No row data leaves the database: a client
  -- re-reads what it needs through PostgREST, so table RLS still decides what it sees.
  -- The columns a message may name: the table's foreign keys, and the columns
  -- its ancestors add. A column that merely ends in "Id" is the row's own data
  -- (a part number in "readableId", an order number, a tax id) and stays here.
  SELECT coalesce(array_agg(DISTINCT a.attname::text), ARRAY[]::text[])
    INTO sent_columns
    FROM pg_catalog.pg_constraint k
    JOIN pg_catalog.pg_attribute a
      ON a.attrelid = k.conrelid AND a.attnum = ANY (k.conkey)
   WHERE k.conrelid = TG_RELID AND k.contype = 'f';
  IF ancestors ? TG_TABLE_NAME THEN
    sent_columns := sent_columns || ARRAY(
      SELECT value->>2 FROM jsonb_array_elements(ancestors->TG_TABLE_NAME)
    );
  END IF;

  IF TG_OP = 'UPDATE' THEN
    -- A row whose only changes are in ignored_columns drops out (the rule
    -- dispatch_event_batch applies; event-dispatch.test.ts keeps the lists equal).
    changed_rows := 'SELECT to_jsonb(n) - $1 AS c FROM batched_new n
                     EXCEPT
                     SELECT to_jsonb(o) - $1 FROM batched_old o';
    -- The rows as they were, too: a row moved to another parent concerns the
    -- parent it left as much as the one it joined.
    parent_rows := '(' || changed_rows || ')
                    UNION ALL
                    (SELECT to_jsonb(o) - $1 FROM batched_old o
                     EXCEPT
                     SELECT to_jsonb(n) - $1 FROM batched_new n)';
  ELSIF TG_OP = 'DELETE' THEN
    changed_rows := 'SELECT to_jsonb(o) AS c FROM batched_old o';
    parent_rows := changed_rows;
  ELSE
    changed_rows := 'SELECT to_jsonb(n) AS c FROM batched_new n';
    parent_rows := changed_rows;
  END IF;

  FOR rec IN EXECUTE format(
    -- implementationHub has no companyId: its id is the company id.
    'WITH keyed AS (
       SELECT coalesce(c->>''companyId'',
                       CASE WHEN $2::text = ''implementationHub'' THEN c->>''id'' END) AS company_id,
              c
         FROM (%1$s) changed
     ),
     related AS (
       SELECT coalesce(c->>''companyId'',
                       CASE WHEN $2::text = ''implementationHub'' THEN c->>''id'' END) AS company_id,
              c
         FROM (%2$s) changed
     )
     SELECT k.company_id,
            count(*) AS n,
            -- itemSupersession has no id: its row belongs to the item it describes.
            jsonb_agg(coalesce(k.c->''id'', k.c->''itemId''))
              FILTER (WHERE k.c ? ''id'' OR k.c ? ''itemId'') AS ids,
            -- The records these rows belong to: each "<name>Id" column and its
            -- values (narrowed to the foreign keys before sending), so a page showing one job (or quote, or order) can ignore
            -- changes to another''s rows. A column with more than 20 values is
            -- left out, and a client treats a missing column as "may concern me".
            -- Only computed for a statement small enough to list its ids.
            CASE WHEN count(*) <= 100 THEN (
              SELECT jsonb_object_agg(p.key, p.vals)
                FROM (
                  SELECT e.key, jsonb_agg(DISTINCT e.value) AS vals
                    FROM related k2, jsonb_each(k2.c) e
                   WHERE k2.company_id = k.company_id
                     AND e.key LIKE ''%%Id'' AND e.key <> ''companyId''
                     AND jsonb_typeof(e.value) = ''string''
                   GROUP BY e.key
                  HAVING count(DISTINCT e.value) <= 20
                ) p
            ) END AS parents
       FROM keyed k
      GROUP BY k.company_id',
    changed_rows, parent_rows
  ) USING ignored_columns, TG_TABLE_NAME
  LOOP
    CONTINUE WHEN rec.company_id IS NULL;

    parents := rec.parents;
    IF parents IS NOT NULL AND ancestors ? TG_TABLE_NAME THEN
      FOR hop IN SELECT value FROM jsonb_array_elements(ancestors->TG_TABLE_NAME) LOOP
        found := CASE WHEN hop->>0 = 'id' THEN rec.ids ELSE parents->(hop->>0) END;
        -- A column that was left out (no value, or too many) leaves its
        -- ancestor out as well: the client then takes the change as its own.
        CONTINUE WHEN found IS NULL;
        EXECUTE format(
          'SELECT jsonb_agg(DISTINCT %1$I) FROM public.%2$I
            WHERE "companyId" = $1 AND %1$I IS NOT NULL
              AND %3$I IN (SELECT jsonb_array_elements_text($2))',
          hop->>2, hop->>1, coalesce(hop->>3, 'id')
        ) INTO found USING rec.company_id, found;
        IF found IS NOT NULL THEN
          parents := parents || jsonb_build_object(hop->>2, found);
        ELSIF jsonb_array_length(hop) = 4 THEN
          -- Looked for and not there: no such record has this row.
          parents := parents || jsonb_build_object(hop->>2, '[]'::jsonb);
        END IF;
      END LOOP;
    END IF;
    -- An ancestor hop may read a column that is not sent (a polymorphic
    -- "sourceDocumentId"), so the narrowing comes last.
    IF parents IS NOT NULL THEN
      parents := coalesce(
        (SELECT jsonb_object_agg(e.key, e.value)
           FROM jsonb_each(parents) e
          WHERE e.key = ANY (sent_columns)),
        '{}'::jsonb
      );
    END IF;
    PERFORM realtime.send(
      jsonb_build_object(
        'table', TG_TABLE_NAME,
        'op', TG_OP,
        -- null = "many rows changed, or the table has no id: resync"
        'ids', CASE WHEN rec.n <= 100 THEN rec.ids END,
        'parents', parents
      ),
      TG_OP,
      'company:' || rec.company_id || ':' || TG_TABLE_NAME,
      true
    );
  END LOOP;
  RETURN NULL;
END;
$function$;

CREATE OR REPLACE FUNCTION public.broadcast_user_changes()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
DECLARE
  ignored_columns CONSTANT TEXT[] := ARRAY['updatedAt', 'updatedBy', 'embedding', 'loadedJobOperationId', 'loadedAt'];
  changed_rows TEXT;
  rec RECORD;
BEGIN
  -- A database without Supabase Realtime (a self-hosted install that does not
  -- run it) has nothing to send to: the write must still succeed.
  IF to_regprocedure('realtime.send(jsonb,text,text,boolean)') IS NULL THEN
    RETURN NULL;
  END IF;

  -- Tells one user which of their rows changed (topic user:<userId>:<table>).
  -- Statement-level (attach_statement_handler): one message per user per
  -- statement, whatever the row count. No row data leaves the database: a client
  -- re-reads what it needs through PostgREST, so table RLS still decides what it sees.
  IF TG_OP = 'UPDATE' THEN
    -- A row whose only changes are in ignored_columns drops out (the rule
    -- dispatch_event_batch applies; event-dispatch.test.ts keeps the lists equal).
    changed_rows := 'SELECT to_jsonb(n) - $1 AS c FROM batched_new n
                     EXCEPT
                     SELECT to_jsonb(o) - $1 FROM batched_old o';
  ELSIF TG_OP = 'DELETE' THEN
    changed_rows := 'SELECT to_jsonb(o) AS c FROM batched_old o';
  ELSE
    changed_rows := 'SELECT to_jsonb(n) AS c FROM batched_new n';
  END IF;

  FOR rec IN EXECUTE format(
    'SELECT c->>''userId'' AS user_id,
            count(*) AS n,
            jsonb_agg(c->''id'') FILTER (WHERE c ? ''id'') AS ids
       FROM (%s) changed
      GROUP BY 1',
    changed_rows
  ) USING ignored_columns
  LOOP
    CONTINUE WHEN rec.user_id IS NULL;
    PERFORM realtime.send(
      jsonb_build_object(
        'table', TG_TABLE_NAME,
        'op', TG_OP,
        'ids', CASE WHEN rec.n <= 100 THEN rec.ids END
      ),
      TG_OP,
      'user:' || rec.user_id || ':' || TG_TABLE_NAME,
      true
    );
  END LOOP;
  RETURN NULL;
END;
$function$;

CREATE OR REPLACE FUNCTION public.dispatch_event_batch()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public', 'pgmq', 'extensions'
AS $function$
DECLARE
  sub RECORD;
  msg_batch JSONB[];
  rec_company_id TEXT;
  has_subs BOOLEAN;
  current_actor_id TEXT;
  current_workflow_run_id TEXT;
  pk_columns TEXT[];
  pk_join TEXT;
  record_id_expr TEXT;
  query_text TEXT;
  did_enqueue BOOLEAN := FALSE;
  ignored_columns CONSTANT TEXT[] := ARRAY['updatedAt', 'updatedBy', 'embedding', 'loadedJobOperationId', 'loadedAt'];
BEGIN
  current_actor_id := auth.uid()::TEXT;
  current_workflow_run_id :=
    (nullif(current_setting('request.jwt.claims', true), '')::jsonb)->>'workflow_run_id';
  IF TG_OP = 'DELETE' THEN
    SELECT t."companyId" INTO rec_company_id FROM batched_old t LIMIT 1;
  ELSIF TG_OP = 'INSERT' THEN
    SELECT t."companyId" INTO rec_company_id FROM batched_new t LIMIT 1;
  ELSE
    SELECT t."companyId" INTO rec_company_id FROM batched_new t LIMIT 1;
  END IF;

  IF rec_company_id IS NULL THEN RETURN NULL; END IF;

  SELECT EXISTS (
    SELECT 1 FROM "eventSystemSubscription"
    WHERE "table" = TG_TABLE_NAME
      AND "companyId" = rec_company_id
      AND "active" = TRUE
      AND TG_OP = ANY("operations")
      AND ("handlerType" NOT IN ('SYNC','WEBHOOK','WORKFLOW')
           OR current_setting('app.sync_in_progress', true) IS DISTINCT FROM 'true')
  ) INTO has_subs;

  IF NOT has_subs THEN RETURN NULL; END IF;

  -- Looked up only once a subscription exists: almost no company subscribes
  -- to most tables, and this catalog read ran on every write statement.
  pk_columns := public.get_primary_key_columns(TG_TABLE_NAME);

  -- Pair UPDATE rows on the full key: single-column pairing cross-joins rows
  -- on tables with composite identity.
  SELECT string_agg(format('n.%I = o.%I', col, col), ' AND ')
    INTO pk_join
  FROM unnest(pk_columns) AS col;

  -- recordId names one row. "id" does that wherever it is part of the key
  -- ((id, companyId) tables); a primary key without one is every column joined
  -- by ':', since a single column of it is shared by many rows. A table with no
  -- primary key keeps the one column it always sent: the audit log files
  -- itemPlanning (unique on itemId + locationId) under its itemId.
  -- %1$s is the row alias.
  IF 'id' = ANY(pk_columns) THEN
    record_id_expr := '%1$s."id"::TEXT';
  ELSIF EXISTS (
    SELECT 1 FROM pg_index WHERE indrelid = TG_RELID AND indisprimary
  ) THEN
    SELECT 'concat_ws('':'', ' || string_agg(format('%%1$s.%I::TEXT', col), ', ') || ')'
      INTO record_id_expr
    FROM unnest(pk_columns) AS col;
  ELSE
    record_id_expr := format('%%1$s.%I::TEXT', public.get_primary_key_column(TG_TABLE_NAME));
  END IF;

  FOR sub IN
    SELECT * FROM "eventSystemSubscription"
    WHERE "table" = TG_TABLE_NAME
      AND "companyId" = rec_company_id
      AND "active" = TRUE
      AND TG_OP = ANY("operations")
      AND ("handlerType" NOT IN ('SYNC','WEBHOOK','WORKFLOW')
           OR current_setting('app.sync_in_progress', true) IS DISTINCT FROM 'true')
  LOOP

    IF TG_OP = 'INSERT' THEN
        query_text := format('
            SELECT array_agg(
                jsonb_build_object(
                    ''subscriptionId'', $1,
                    ''triggerType'', $2,
                    ''handlerType'', $3,
                    ''handlerConfig'', $4,
                    ''companyId'', $5,
                    ''actorId'', $6,
                    ''workflowRunId'', $10,
                    ''event'', jsonb_build_object(
                        ''table'', $7,
                        ''operation'', $8,
                        ''recordId'', %s,
                        ''new'', row_to_json(t)::jsonb,
                        ''old'', null,
                        ''timestamp'', clock_timestamp()
                    )
                )
            )
            FROM batched_new t
            WHERE t."companyId" = $5
              AND ($9 = ''{}''::jsonb OR row_to_json(t)::jsonb @> $9)
        ', format(record_id_expr, 't'));

        EXECUTE query_text INTO msg_batch
        USING sub.id, TG_LEVEL, sub."handlerType", sub."config", rec_company_id,
              current_actor_id, TG_TABLE_NAME, TG_OP, sub.filter,
              current_workflow_run_id;

    ELSIF TG_OP = 'DELETE' THEN
        query_text := format('
            SELECT array_agg(
                jsonb_build_object(
                    ''subscriptionId'', $1,
                    ''triggerType'', $2,
                    ''handlerType'', $3,
                    ''handlerConfig'', $4,
                    ''companyId'', $5,
                    ''actorId'', $6,
                    ''workflowRunId'', $10,
                    ''event'', jsonb_build_object(
                        ''table'', $7,
                        ''operation'', $8,
                        ''recordId'', %s,
                        ''new'', null,
                        ''old'', row_to_json(t)::jsonb,
                        ''timestamp'', clock_timestamp()
                    )
                )
            )
            FROM batched_old t
            WHERE t."companyId" = $5
              AND ($9 = ''{}''::jsonb OR row_to_json(t)::jsonb @> $9)
        ', format(record_id_expr, 't'));

        EXECUTE query_text INTO msg_batch
        USING sub.id, TG_LEVEL, sub."handlerType", sub."config", rec_company_id,
              current_actor_id, TG_TABLE_NAME, TG_OP, sub.filter,
              current_workflow_run_id;

    ELSIF TG_OP = 'UPDATE' THEN
        query_text := format('
            SELECT array_agg(
                jsonb_build_object(
                    ''subscriptionId'', $1,
                    ''triggerType'', $2,
                    ''handlerType'', $3,
                    ''handlerConfig'', $4,
                    ''companyId'', $5,
                    ''actorId'', $6,
                    ''workflowRunId'', $10,
                    ''event'', jsonb_build_object(
                        ''table'', $7,
                        ''operation'', $8,
                        ''recordId'', %s,
                        ''new'', row_to_json(n)::jsonb,
                        ''old'', row_to_json(o)::jsonb,
                        ''timestamp'', clock_timestamp()
                    )
                )
            )
            FROM batched_new n
            JOIN batched_old o ON %s
            WHERE n."companyId" = $5
              AND ($9 = ''{}''::jsonb OR row_to_json(n)::jsonb @> $9)
              AND (NOT $11 OR (to_jsonb(n) - $12) IS DISTINCT FROM (to_jsonb(o) - $12))
        ', format(record_id_expr, 'n'), pk_join);

        EXECUTE query_text INTO msg_batch
        USING sub.id, TG_LEVEL, sub."handlerType", sub."config", rec_company_id,
              current_actor_id, TG_TABLE_NAME, TG_OP, sub.filter,
              current_workflow_run_id,
              sub."handlerType" IN ('AUDIT', 'SEARCH', 'EMBEDDING'),
              ignored_columns;
    END IF;

    IF msg_batch IS NOT NULL AND array_length(msg_batch, 1) > 0 THEN
      PERFORM pgmq.send_batch('event_system', msg_batch);
      did_enqueue := TRUE;
    END IF;

  END LOOP;

  -- Wake the Inngest drainer once per transaction: the GUC is txn-local, so a
  -- bulk import posts a single doorbell instead of one per statement.
  IF did_enqueue
     AND current_setting('carbon.event_wake_sent', true) IS DISTINCT FROM 'true' THEN
    PERFORM util.wake_event_queue();
    PERFORM set_config('carbon.event_wake_sent', 'true', true);
  END IF;

  RETURN NULL;
END;
$function$;

CREATE OR REPLACE FUNCTION public.log_table_changes()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
DECLARE
  ignored_columns CONSTANT TEXT[] := ARRAY['updatedAt', 'updatedBy', 'embedding', 'loadedJobOperationId', 'loadedAt'];
  changed_rows TEXT;
BEGIN
  -- Records which rows of this table changed, in public."tableChange", so a
  -- client that kept a copy of a list can ask "what changed since I last
  -- looked?" (table_changes_since) and re-read only those rows instead of the
  -- whole table. A broadcast tells an open tab the same thing; this is for the
  -- tab that was closed, or disconnected, when it happened.
  IF TG_OP = 'UPDATE' THEN
    -- A row whose only changes are in ignored_columns drops out (the rule
    -- dispatch_event_batch applies; event-dispatch.test.ts keeps the lists equal).
    changed_rows := 'SELECT to_jsonb(n) - $1 AS c FROM batched_new n
                     EXCEPT
                     SELECT to_jsonb(o) - $1 FROM batched_old o';
  ELSIF TG_OP = 'DELETE' THEN
    changed_rows := 'SELECT to_jsonb(o) AS c FROM batched_old o';
  ELSE
    changed_rows := 'SELECT to_jsonb(n) AS c FROM batched_new n';
  END IF;

  EXECUTE format(
    -- One log row per changed row. Past 100 rows in one statement, a single row
    -- with a null "rowId" says "this table changed a lot: read it again".
    -- itemSupersession has no id: its row belongs to the item it describes.
    'INSERT INTO public."tableChange" ("companyId", "table", "rowId")
     SELECT DISTINCT company_id, $2::text, CASE WHEN n <= 100 THEN row_id END
       FROM (
         SELECT c->>''companyId'' AS company_id,
                coalesce(c->>''id'', c->>''itemId'') AS row_id,
                count(*) OVER (PARTITION BY c->>''companyId'') AS n
           FROM (%s) changed
       ) rows
      WHERE company_id IS NOT NULL',
    changed_rows
  ) USING ignored_columns, TG_TABLE_NAME;
  RETURN NULL;
END;
$function$;

CREATE OR REPLACE FUNCTION public.log_user_changes()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
DECLARE
  ignored_columns CONSTANT TEXT[] := ARRAY['updatedAt', 'updatedBy', 'embedding', 'loadedJobOperationId', 'loadedAt'];
  changed_rows TEXT;
BEGIN
  -- A person's name and avatar live on the global "user" row, which has no
  -- company. Each company that person belongs to shows them in its people list
  -- (the employees view), so a change is logged once per company, as a change
  -- to that company's "employee" row.
  IF TG_OP = 'UPDATE' THEN
    changed_rows := 'SELECT to_jsonb(n) - $1 AS c FROM batched_new n
                     EXCEPT
                     SELECT to_jsonb(o) - $1 FROM batched_old o';
  ELSIF TG_OP = 'DELETE' THEN
    changed_rows := 'SELECT to_jsonb(o) AS c FROM batched_old o';
  ELSE
    changed_rows := 'SELECT to_jsonb(n) AS c FROM batched_new n';
  END IF;

  EXECUTE format(
    'INSERT INTO public."tableChange" ("companyId", "table", "rowId")
     SELECT DISTINCT m."companyId", ''employee'', changed.c->>''id''
       FROM (%s) changed
       JOIN public."userToCompany" m ON m."userId" = changed.c->>''id''',
    changed_rows
  ) USING ignored_columns;
  RETURN NULL;
END;
$function$;