scheduled transactions

[mxfactorial]

scheduled transactions

modigliani-miller proved capital structure is irrelevant in perfect markets. but markets arent perfect—theyre full of intermediaries extracting rent from scheduling complexity

dividends, interest payments, debt service, royalties, subscriptions, escrow releases—wall street built empires on the operational complexity of "when does money move"

in systemaccounting: cron + transaction_rule_instance + http_post

the entire scheduled finance industry reduces to rows in postgres and a cron job. no prime brokers. no payment processors. no settlement delays. just time-triggered value transfer with conservation enforced

RIP financial engineering (1958-2026)

architecture additions

1. pg_cron - postgres extension, scheduler
2. pg_http - postgres extension, http calls to services
3. execute_transaction_rule_instance() - postgres function, wires pg_cron to services
4. services/cron - crud for scheduled transaction templates
5. services/cron-exec - request-create with retry

pg_cron + pg_http

schedules and initiates transactions via postgres pg_cron and pg_http extensions

single source of truth—schedule lives in postgres, scheduler is postgres, no external process to drift

keeps rule service simple (enriches/filters only). scheduling is separate concern

entries

private - user-defined scheduled transactions

• "every wednesday, donate 2% of my sales to charity X"
• only affects that account

public - system-wide scheduled rules

• "1st of month, apply interest to all savings accounts"
• "quarterly, calculate withholdings for all businesses"
• applies to all qualifying accounts

tables

cron.job - pg_cron managed, stores schedule definitions

cron.job_run_details - pg_cron managed, execution history for audit

transaction_rule_instance - stores scheduled transaction templates (cron, debitor, creditor, item_id, price, quantity, etc). see rule_instance_schema.md

example

-- transaction_rule_instance row IS the template
INSERT INTO transaction_rule_instance (
  cron, rule_name, rule_instance_name,
  account_role, account_name,
  debitor, creditor, item_id, price, quantity
) VALUES (
  '0 0 1 * *', 'createInterestPayment', 'MonthlyInterestAtoB',
  'creditor', 'AccountB',
  'AccountA', 'AccountB', 'interest payment', '112.000', '1'
);

-- pg_cron references the transaction_rule_instance id
SELECT cron.schedule(
  'monthly-interest-456',
  '0 0 1 * *',
  $$SELECT execute_transaction_rule_instance(456)$$
);

-- query scheduled jobs
SELECT * FROM cron.job;

-- audit execution history
SELECT * FROM cron.job_run_details WHERE jobname = 'monthly-interest-456';

execute_transaction_rule_instance function

CREATE OR REPLACE FUNCTION execute_transaction_rule_instance(instance_id INT)
RETURNS void AS $$
DECLARE
  tri transaction_rule_instance%ROWTYPE;
  rule_response jsonb;
BEGIN
  SELECT * INTO tri FROM transaction_rule_instance WHERE id = instance_id;

  -- call rule service via pg_http (enrich/transform)
  SELECT content::jsonb INTO rule_response FROM http_post(
    'http://rule:10001/',
    json_build_array(
      json_build_object(
        'item_id', tri.item_id,
        'price', tri.price,
        'quantity', tri.quantity,
        'debitor', tri.debitor,
        'creditor', tri.creditor
      )
    )::text,
    'application/json'
  );

  -- fire to executor service (non-blocking)
  PERFORM http_post(
    'http://cron-exec:10009/',
    json_build_object(
      'transaction_rule_instance_id', instance_id,
      'transaction', rule_response
    )::text,
    'application/json'
  );
END;
$$ LANGUAGE plpgsql;

services/cron-exec

handles request-create call with failure/retry—keeps pg_http fast

• accepts enriched transaction from pg_http (returns immediately)
• calls request-create
• retries with backoff on failure
• logs results

retry logic belongs in application code, not postgres

services/cron

thin crud layer—postgres is the scheduler

POST /cron

• insert transaction_rule_instance row + cron.schedule()

GET /crons?account=X

• query transaction_rule_instance WHERE cron IS NOT NULL joined with cron.job

PUT /cron/:id

• update transaction_rule_instance row + cron.unschedule() + cron.schedule()

DELETE /cron/:id

• delete transaction_rule_instance row + cron.unschedule()

flow

pg_cron trigger → execute_transaction_rule_instance(id):

1. pg_http → rule service (enrich/transform)
2. pg_http → cron-exec (fire and forget)
   • cron-exec → request-create (persist with retry)

scheduled transactions flow through normal pipeline

depends on

#426

---

this is a summary task (epic)

subtasks are pending

[mxfactorial]

hours worked

requirement writing: 1.75