aiolabs/satmachineadmin

Fork 0

Future: dedicated audit table for settlement adjustments (queryable history) #10

New issue

Closed

opened 2026-05-14 13:46:58 +00:00 by padreug · 1 comment

padreug commented

2026-05-14 13:46:58 +00:00

Owner

Context

v2 (see #9) stores audit context for in-place settlement adjustments in the dca_settlements.notes column (added in m006_add_settlement_notes). This is fine for "operator opens a settlement and reads what happened", but it's a free-text blob and doesn't support queryable history.

Currently two kinds of entries land there:

System memos (distribution.apply_partial_dispense_and_redistribute) when a partial-dispense recompute overwrites the settlement's monetary fields. Captures the original values + reason + new values, prefixed with a UTC timestamp.
Operator-authored notes (POST /api/v1/dca/settlements/{id}/notes) for free-form context — cash-drawer reconciliation, off-LN refunds, etc. Timestamped and tagged with the author's user id.

Both append to a single TEXT column with newest-first ordering.

What this is missing

Cannot filter "show me every partial-dispense in March" without text-grepping.
Cannot ask "which operator made the most adjustments?"
Cannot revert an entry (notes are append-only by design, but errors get baked in).
Tagged authorship is just a stringified user_id concat — joining to LNbits user records for a display name requires regex parsing.
No structured action type — partial-dispense vs balance-settle-from-operator vs free-form note all live in the same blob.

Proposed v2.x — `dca_settlement_audit` table

CREATE TABLE satoshimachine.dca_settlement_audit (
    id TEXT PRIMARY KEY,
    settlement_id TEXT NOT NULL,           -- FK to dca_settlements
    action_type TEXT NOT NULL,             -- 'partial_dispense' | 'note' |
                                           --  'balance_settle' | 'status_override' | ...
    author_user_id TEXT NOT NULL,          -- LNbits user id (joinable)
    notes TEXT,                            -- free-form operator-supplied
    payload_json TEXT,                     -- structured action payload
                                           -- (e.g., {original_gross, new_gross, ...} for partial_dispense)
    created_at TIMESTAMP NOT NULL
);
CREATE INDEX dca_settlement_audit_settlement_idx
  ON satoshimachine.dca_settlement_audit (settlement_id, created_at DESC);
CREATE INDEX dca_settlement_audit_author_idx
  ON satoshimachine.dca_settlement_audit (author_user_id, created_at DESC);
CREATE INDEX dca_settlement_audit_action_idx
  ON satoshimachine.dca_settlement_audit (action_type, created_at DESC);

Migration path: keep the notes column as a convenience render of the latest few entries (or deprecate it once UI rendering catches up). New writes go to both during a transition window; readers cut over once the dedicated table proves out.

Reports this enables

Per-operator activity audit ("how active is each operator in adjustments?")
Compliance / dispute response ("show every adjustment touching settlement X with structured fields")
Per-machine reliability heuristics ("which machine has the most partial-dispenses?" — fed into a future fleet-health score)
Time-window queries ("every off-LN refund logged in Q1")

Status

Not blocking v1. v1 ships with the notes-column approach; this issue tracks the upgrade once we have UI need or compliance pressure that the notes blob can't satisfy.

aiolabs/satmachineadmin#9 — v2 epic
aiolabs/satmachineadmin#3 — partial transaction processing (the original feature that motivated the audit story)
m006_add_settlement_notes migration — the current notes-column landing

## Context v2 (see `#9`) stores audit context for in-place settlement adjustments in the `dca_settlements.notes` column (added in `m006_add_settlement_notes`). This is fine for "operator opens a settlement and reads what happened", but it's a free-text blob and doesn't support queryable history. Currently two kinds of entries land there: 1. **System memos** (`distribution.apply_partial_dispense_and_redistribute`) when a partial-dispense recompute overwrites the settlement's monetary fields. Captures the original values + reason + new values, prefixed with a UTC timestamp. 2. **Operator-authored notes** (`POST /api/v1/dca/settlements/{id}/notes`) for free-form context — cash-drawer reconciliation, off-LN refunds, etc. Timestamped and tagged with the author's user id. Both append to a single TEXT column with newest-first ordering. ## What this is missing - Cannot filter "show me every partial-dispense in March" without text-grepping. - Cannot ask "which operator made the most adjustments?" - Cannot revert an entry (notes are append-only by design, but errors get baked in). - Tagged authorship is just a stringified user_id concat — joining to LNbits user records for a display name requires regex parsing. - No structured action type — partial-dispense vs balance-settle-from-operator vs free-form note all live in the same blob. ## Proposed v2.x — `dca_settlement_audit` table ```sql CREATE TABLE satoshimachine.dca_settlement_audit ( id TEXT PRIMARY KEY, settlement_id TEXT NOT NULL, -- FK to dca_settlements action_type TEXT NOT NULL, -- 'partial_dispense' | 'note' | -- 'balance_settle' | 'status_override' | ... author_user_id TEXT NOT NULL, -- LNbits user id (joinable) notes TEXT, -- free-form operator-supplied payload_json TEXT, -- structured action payload -- (e.g., {original_gross, new_gross, ...} for partial_dispense) created_at TIMESTAMP NOT NULL ); CREATE INDEX dca_settlement_audit_settlement_idx ON satoshimachine.dca_settlement_audit (settlement_id, created_at DESC); CREATE INDEX dca_settlement_audit_author_idx ON satoshimachine.dca_settlement_audit (author_user_id, created_at DESC); CREATE INDEX dca_settlement_audit_action_idx ON satoshimachine.dca_settlement_audit (action_type, created_at DESC); ``` Migration path: keep the `notes` column as a convenience render of the latest few entries (or deprecate it once UI rendering catches up). New writes go to both during a transition window; readers cut over once the dedicated table proves out. ## Reports this enables - Per-operator activity audit ("how active is each operator in adjustments?") - Compliance / dispute response ("show every adjustment touching settlement X with structured fields") - Per-machine reliability heuristics ("which machine has the most partial-dispenses?" — fed into a future fleet-health score) - Time-window queries ("every off-LN refund logged in Q1") ## Status **Not blocking v1.** v1 ships with the notes-column approach; this issue tracks the upgrade once we have UI need or compliance pressure that the notes blob can't satisfy. ## Related - `aiolabs/satmachineadmin#9` — v2 epic - `aiolabs/satmachineadmin#3` — partial transaction processing (the original feature that motivated the audit story) - `m006_add_settlement_notes` migration — the current notes-column landing

padreug referenced this issue from aiolabs/spirekeeper

2026-06-14 07:08:41 +00:00

Future: dedicated audit table for settlement adjustments (queryable history) #6

padreug commented

2026-06-14 07:09:35 +00:00

Author

Owner

➡️ Migrated to aiolabs/spirekeeper#6 (aiolabs/spirekeeper#6).

The v2-bitspire line of this extension now lives in its own repo, aiolabs/spirekeeper. Tracking for this issue continues there; closing here. (Issue numbers were reassigned in the new repo.)

➡️ **Migrated to https://git.atitlan.io/aiolabs/spirekeeper/issues/6 (aiolabs/spirekeeper#6).** The v2-bitspire line of this extension now lives in its own repo, `aiolabs/spirekeeper`. Tracking for this issue continues there; closing here. (Issue numbers were reassigned in the new repo.)

padreug closed this issue

2026-06-14 07:09:36 +00:00

No labels

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

aiolabs/satmachineadmin#10

No description provided.

Rows
Columns