Class DBA

Database maintenance operations for HotMesh's Postgres backend.

HotMesh uses soft-delete patterns: expired jobs and stream messages retain their rows with expired_at set but are never physically removed during normal operation. Over time, three tables accumulate dead rows:

Table What accumulates
{appId}.jobs Completed/expired jobs with expired_at set
{appId}.jobs_attributes Execution artifacts (adata, hmark, jmark, status, other) that are only needed during workflow execution
{appId}.streams Processed stream messages with expired_at set

The DBA service addresses this with two methods:

  • prune() — Targets any combination of jobs, streams, and attributes independently. Each table can be pruned on its own schedule with its own retention window.
  • deploy() — Pre-deploys the Postgres function (e.g., during CI/CD migrations) without running a prune.

Independent cron schedules (TypeScript)

Each table can be targeted independently, allowing different retention windows and schedules:

Example

import { Client as Postgres } from 'pg';
import { DBA } from '@hotmeshio/hotmesh';

const connection = {
  class: Postgres,
  options: { connectionString: 'postgresql://usr:pwd@localhost:5432/db' },
};

// Cron 1 — Nightly: strip execution artifacts from completed jobs
//          Keeps all jobs and their jdata/udata; keeps all streams.
await DBA.prune({
  appId: 'myapp', connection,
  jobs: false, streams: false, attributes: true,
});

// Cron 2 — Hourly: remove processed stream messages older than 24h
await DBA.prune({
  appId: 'myapp', connection,
  expire: '24 hours',
  jobs: false, streams: true,
});

// Cron 3 — Weekly: remove expired jobs older than 30 days
await DBA.prune({
  appId: 'myapp', connection,
  expire: '30 days',
  jobs: true, streams: false,
});

Direct SQL (schedulable via pg_cron)

The underlying Postgres function can be called directly, without the TypeScript SDK. Schedule it via pg_cron, crontab, or any SQL client:

-- Strip attributes only (keep all jobs and streams)
SELECT * FROM myapp.prune('0 seconds', false, false, true);

-- Prune streams older than 24 hours (keep jobs)
SELECT * FROM myapp.prune('24 hours', false, true, false);

-- Prune expired jobs older than 30 days (keep streams)
SELECT * FROM myapp.prune('30 days', true, false, false);

-- Prune everything older than 7 days and strip attributes
SELECT * FROM myapp.prune('7 days', true, true, true);

Methods

deploy prune

Methods

Staticdeploy

  • deploy(connection, appId): Promise<void>

  • Deploys the prune() Postgres function into the target schema. Idempotent — uses CREATE OR REPLACE and can be called repeatedly.

    The function is automatically deployed when DBA.prune is called, but this method is exposed for explicit control (e.g., CI/CD migration scripts that provision database objects before the application starts).

    Parameters

    • connection: ProviderConfig

      Postgres provider configuration

    • appId: string

      Application identifier (schema name)

    Returns Promise<void>

    Example

    import { Client as Postgres } from 'pg';
    import { DBA } from '@hotmeshio/hotmesh';

    // Pre-deploy during CI/CD migration
    await DBA.deploy(
      {
        class: Postgres,
        options: { connectionString: 'postgresql://usr:pwd@localhost:5432/db' }
      },
      'myapp',
    );

Staticprune

  • prune(options): Promise<PruneResult>

  • Prunes expired data and/or strips execution artifacts from completed jobs. Each operation is independently controlled, so callers can target a single table per cron schedule.

    Operations (each enabled individually):

    1. jobs — Hard-deletes expired jobs older than the retention window (FK CASCADE removes their attributes automatically)
    2. streams — Hard-deletes expired stream messages older than the retention window
    3. attributes — Strips non-essential attributes (adata, hmark, jmark, status, other) from completed jobs, retaining only jdata and udata

    Parameters

    Returns Promise<PruneResult>

    Counts of deleted/stripped rows

    Example

    import { Client as Postgres } from 'pg';
    import { DBA } from '@hotmeshio/hotmesh';

    // Strip attributes only — keep all jobs and streams
    await DBA.prune({
      appId: 'myapp',
      connection: {
        class: Postgres,
        options: { connectionString: 'postgresql://usr:pwd@localhost:5432/db' }
      },
      jobs: false,
      streams: false,
      attributes: true,
    });

Settings

Member Visibility

On This Page

Independent cron schedules (TypeScript)Direct SQL (schedulable via pg_cron)
Methods