break up rule_instance table

[mxfactorial]

rule_instance schema

current rule_instance table mixes three output types in one polymorphic table with rule_type column. type error.

requirement

split into three tables by output type:

transaction_rule_instance - produces transactions

• cron (nullable) - if set, time-triggered via pg_cron; if null, condition-triggered by incoming transaction
• condition columns: author, sum_value, rule match, etc
• output columns: debitor, creditor, item_id, price, quantity

transaction_item_rule_instance - produces transaction items

• no cron column
• condition columns: state_name, account_name, item_id, price thresholds, etc
• output columns: variable_values for rule execution

approval_rule_instance - produces approvals

• no cron column
• condition columns: account_name, account_role, item matching
• output columns: approver account, role

benefits

1. cron only where it belongs (transaction_rule_instance)
2. no nullable columns that only apply to one type
3. schema enforces type correctness
4. queries target specific tables, no WHERE rule_type = 'x' filtering
5. each table has exactly the columns it needs

migration

1. create three new tables
2. migrate existing rule_instance rows by rule_type
3. update rule service to query new tables
4. update cron.md: pg_cron references transaction_rule_instance
5. drop rule_instance table

related files

schema:

• migrations/schema/000004_rule.up.sql - current rule and rule_instance tables

seed data:

• migrations/seed/000001_seed.up.sql:52~57 - rule definitions
• migrations/testseed/000002_rules.up.sql - test rule_instance rows

types:

• crates/types/src/rule_instance.rs - rust type (will need split)

queries:

• crates/pg/src/sqls/rule_instance.rs - sql queries by rule_type
• crates/pg/src/model.rs - model methods

service:

• crates/service/src/lib.rs - service methods calling model
• services/rule/src/main.rs:55~86 - hardcoded dimension queries
• services/rule/src/rules/ - rule application logic

docs:

• cron.md - scheduled transactions use transaction_rule_instance with cron
• rule-graph.md - dimension abstraction notes

hourly estimate

6

[mxfactorial]

hours worked

requirement writing: 0.5

[mxfactorial]

hours worked

5.5