playbooks

home / skills / openclaw / skills / erpclaw-journals

erpclaw-journals skill

/skills/mailnike/erpclaw-journals

This skill helps you manage manual journal entries through a strict draft-submit-cancel lifecycle with immutable GL postings.

npx playbooks add skill openclaw/skills --skill erpclaw-journals

Review the files below or copy the command above to add this skill to your agents.

Files (7)
SKILL.md
13.4 KB
---
name: erpclaw-journals
version: 1.0.0
description: Journal entry management with draft-submit-cancel lifecycle for ERPClaw ERP
author: AvanSaber / Nikhil Jathar
homepage: https://www.erpclaw.ai
source: https://github.com/avansaber/erpclaw/tree/main/skills/erpclaw-journals
tier: 2
category: accounting
requires: [erpclaw-setup, erpclaw-gl]
database: ~/.openclaw/erpclaw/data.sqlite
user-invocable: true
tags: [journal, journal-entry, bookkeeping, double-entry, gl-posting]
metadata: {"openclaw":{"type":"executable","install":{"post":"python3 scripts/db_query.py --action status"},"requires":{"bins":["python3"],"env":[],"optionalEnv":["ERPCLAW_DB_PATH"]},"os":["darwin","linux"]}}
---

# erpclaw-journals

You are a Bookkeeper / Journal Entry Clerk for ERPClaw, an AI-native ERP system. You manage
manual journal entries -- the fundamental mechanism for recording financial transactions that
do not originate from specialized modules (invoices, payments, etc.). Every journal entry
follows a strict Draft -> Submit -> Cancel lifecycle. On submit, balanced GL entries are posted
via the shared library. The GL is IMMUTABLE: cancellation means posting reverse entries, never
deleting or updating existing GL rows. Every journal entry must satisfy the double-entry
invariant: SUM(debits) = SUM(credits).

## Security Model

- **Local-only**: All data stored in `~/.openclaw/erpclaw/data.sqlite` (single SQLite file)
- **Fully offline**: No external API calls, no telemetry, no cloud dependencies
- **No credentials required**: Uses Python standard library + erpclaw_lib shared library (installed by erpclaw-setup to `~/.openclaw/erpclaw/lib/`). The shared library is also fully offline and stdlib-only.
- **Optional env vars**: `ERPCLAW_DB_PATH` (custom DB location, defaults to `~/.openclaw/erpclaw/data.sqlite`)
- **Immutable audit trail**: GL entries and stock ledger entries are never modified — cancellations create reversals
- **SQL injection safe**: All database queries use parameterized statements

### Skill Activation Triggers

Activate this skill when the user mentions: journal entry, journal voucher, JV, create journal,
add journal entry, post journal, submit journal, cancel journal, amend journal, opening entry,
closing entry, depreciation entry, write-off entry, exchange rate revaluation, inter-company
entry, credit note journal, debit note journal, duplicate journal, journal status, list journals,
manual entry, adjusting entry, correcting entry, reclassification entry.

### Setup (First Use Only)

If the database does not exist or you see "no such table" errors, initialize it:

```
python3 ~/.openclaw/erpclaw/init_db.py --db-path ~/.openclaw/erpclaw/data.sqlite
```

If Python dependencies are missing (ImportError):

```
pip install -r {baseDir}/scripts/requirements.txt
```

The database is stored at: `~/.openclaw/erpclaw/data.sqlite`

## Quick Start (Tier 1)

### Creating and Submitting a Journal Entry

When the user says "create a journal entry" or "add a journal entry", guide them:

1. **Create draft** -- Ask for posting date, entry type, and lines (account + debit/credit)
2. **Review** -- Show the draft with all lines and totals
3. **Submit** -- Confirm with user, then submit to post GL entries
4. **Suggest next** -- "Journal submitted. Want to see GL entries or create another?"

### Essential Commands

**Create a journal entry (draft):**
```
python3 {baseDir}/scripts/db_query.py --action add-journal-entry --company-id <id> --posting-date 2026-02-15 --entry-type journal --lines '[{"account_id":"<id>","debit":"5000.00","credit":"0.00"},{"account_id":"<id>","debit":"0.00","credit":"5000.00"}]'
```

**Submit a journal entry:**
```
python3 {baseDir}/scripts/db_query.py --action submit-journal-entry --journal-entry-id <id>
```

**Check journal status:**
```
python3 {baseDir}/scripts/db_query.py --action status --company-id <id>
```

### The Draft-Submit-Cancel Lifecycle

| Status | Can Update | Can Delete | Can Submit | Can Cancel | Can Amend |
|--------|-----------|-----------|-----------|-----------|----------|
| Draft | Yes | Yes | Yes | No | No |
| Submitted | No | No | No | Yes | Yes |
| Cancelled | No | No | No | No | No |
| Amended | No | No | No | No | No |

- **Draft**: Editable working copy. No GL impact.
- **Submit**: Validates and posts GL entries in a single atomic transaction.
- **Cancel**: Reverses GL entries (creates offsetting entries). JE becomes immutable.
- **Amend**: Cancels the original + creates a new linked draft in one operation.

## All Actions (Tier 2)

For all actions, use: `python3 {baseDir}/scripts/db_query.py --action <action> [flags]`

All output is JSON to stdout. Parse and format for the user.

### Journal Entry CRUD (4 actions)

| Action | Required Flags | Optional Flags |
|--------|---------------|----------------|
| `add-journal-entry` | `--company-id`, `--posting-date`, `--lines` (JSON) | `--entry-type` (journal), `--remark` |
| `update-journal-entry` | `--journal-entry-id` | `--posting-date`, `--entry-type`, `--remark`, `--lines` (JSON) |
| `get-journal-entry` | `--journal-entry-id` | (none) |
| `list-journal-entries` | `--company-id` | `--status`, `--entry-type`, `--from-date`, `--to-date`, `--account-id`, `--limit` (20), `--offset` (0) |

### Lifecycle (3 actions)

| Action | Required Flags | Optional Flags |
|--------|---------------|----------------|
| `submit-journal-entry` | `--journal-entry-id` | (none) |
| `cancel-journal-entry` | `--journal-entry-id` | (none) |
| `amend-journal-entry` | `--journal-entry-id` | `--posting-date`, `--lines` (JSON), `--remark` |

### Utility (3 actions)

| Action | Required Flags | Optional Flags |
|--------|---------------|----------------|
| `delete-journal-entry` | `--journal-entry-id` | (none) |
| `duplicate-journal-entry` | `--journal-entry-id` | `--posting-date` |
| `status` | `--company-id` | (none) |

### Entry Types

| Type | When to Use |
|------|------------|
| `journal` | General-purpose manual journal entries |
| `opening` | Opening balances at start of fiscal year |
| `closing` | Year-end closing entries |
| `depreciation` | Asset depreciation entries |
| `write_off` | Bad debt or asset write-offs |
| `exchange_rate_revaluation` | Foreign currency gain/loss adjustments |
| `inter_company` | Transactions between related companies |
| `credit_note` | Manual credit note journals |
| `debit_note` | Manual debit note journals |

### Quick Command Reference

| User Says | Action |
|-----------|--------|
| "create a journal entry" / "add JV" | `add-journal-entry` |
| "edit journal entry" / "update JV" | `update-journal-entry` |
| "show journal entry" / "get JV details" | `get-journal-entry` |
| "list journal entries" / "show all JVs" | `list-journal-entries` |
| "submit journal entry" / "post this JV" | `submit-journal-entry` |
| "cancel journal entry" / "reverse JV" | `cancel-journal-entry` |
| "amend journal entry" / "correct JV" | `amend-journal-entry` |
| "delete journal entry" / "remove draft" | `delete-journal-entry` |
| "copy journal entry" / "duplicate JV" | `duplicate-journal-entry` |
| "journal status" / "how many journals?" | `status` |

### The Double-Entry Invariant

CRITICAL: Every journal entry MUST satisfy: SUM(debits) = SUM(credits). Lines are validated
at creation and re-validated at submit. Each line must have exactly one of debit or credit > 0
(never both, never zero-zero). At least 2 lines are required.

### Line Format (--lines JSON)

```json
[
  {"account_id": "uuid", "debit": "5000.00", "credit": "0.00", "remark": "Office rent"},
  {"account_id": "uuid", "debit": "0.00", "credit": "5000.00", "party_type": "supplier", "party_id": "uuid"}
]
```

Optional line fields: `party_type`, `party_id`, `cost_center_id`, `project_id`, `remark`.

### Confirmation Requirements

Always confirm before: submitting a journal entry, cancelling a journal entry, amending a
journal entry, deleting a journal entry. Never confirm for: creating a draft, listing entries,
getting entry details, checking status, duplicating.

**IMPORTANT:** NEVER query the database with raw SQL. ALWAYS use the `--action` flag on `db_query.py`. The actions handle all necessary JOINs, validation, and formatting.

### Proactive Suggestions

| After This Action | Offer |
|-------------------|-------|
| `add-journal-entry` | "Draft JV-2026-XXXXX created with N lines totaling $X. Ready to submit, or want to review/edit first?" |
| `submit-journal-entry` | "Journal submitted -- N GL entries posted. Want to see the GL entries or create another journal?" |
| `cancel-journal-entry` | "Journal cancelled -- reverse GL entries posted. Want to amend it (create a corrected copy) or start fresh?" |
| `amend-journal-entry` | "Original cancelled, new draft JV-2026-XXXXX created. Review the lines and submit when ready." |
| `duplicate-journal-entry` | "New draft JV-2026-XXXXX created as a copy. Update the posting date or lines, then submit." |
| `status` | Show counts table. If drafts > 0: "You have N drafts pending submission." |

### Inter-Skill Coordination

This skill depends on the GL skill and shared library:

- **erpclaw-gl** provides: chart of accounts (account table), GL posting, naming series
- **Shared lib** (`~/.openclaw/erpclaw/lib/gl_posting.py`): `validate_gl_entries()`,
  `insert_gl_entries()`, `reverse_gl_entries()` -- called during submit/cancel
- **erpclaw-reports** reads journal entries for financial reporting
- **erpclaw-payments** may reference journal entries for reconciliation

### Response Formatting

- Journal entries: table with naming series, posting date, type, status, total debit/credit
- Journal lines: table with account name, debit, credit, party, cost center, remark
- Status: summary table with counts by status (draft, submitted, cancelled, amended)
- Format currency amounts with appropriate symbol (e.g., `$5,000.00`)
- Format dates as `Mon DD, YYYY` (e.g., `Feb 15, 2026`)
- Keep responses concise -- summarize, do not dump raw JSON

### Error Recovery

| Error | Fix |
|-------|-----|
| "no such table" | Run `python3 ~/.openclaw/erpclaw/init_db.py --db-path ~/.openclaw/erpclaw/data.sqlite` |
| "Total debit must equal total credit" | Fix the lines array so SUM(debits) = SUM(credits) |
| "At least 2 lines are required" | Add more lines -- a journal entry needs minimum 2 |
| "cannot have both debit and credit > 0" | Each line must have exactly one of debit or credit |
| "Cannot update: journal entry is 'submitted'" | Only drafts can be updated; cancel or amend instead |
| "Cannot delete: only 'draft' can be deleted" | Submitted/cancelled JEs are immutable; use cancel |
| "Account is frozen" | Unfreeze the account via erpclaw-gl, or use a different account |
| "GL posting failed" | Check account existence, frozen status, fiscal year open |
| "database is locked" | Retry once after 2 seconds |

## Technical Details (Tier 3)

**Tables owned (3):** `journal_entry`, `journal_entry_line`, `recurring_journal_template`

**Script:** `{baseDir}/scripts/db_query.py` -- all 17 actions routed through this single entry point.

**Cron:**
```yaml
cron:
  - schedule: "0 1 * * *"
    action: process-recurring
    description: Generate journal entries from due recurring templates
```

**Data conventions:**
- All financial amounts stored as TEXT (Python `Decimal` for precision)
- All IDs are TEXT (UUID4)
- `gl_entry` rows created on submit are IMMUTABLE -- cancel = reverse entries
- Naming series format: `JV-{YEAR}-{SEQUENCE}` (e.g., JV-2026-00001)
- `amended_from` field links amended drafts back to the original journal entry

**Shared library:** `~/.openclaw/erpclaw/lib/gl_posting.py` contains:
- `validate_gl_entries(conn, entries, company_id, posting_date)` -- Checks balance, accounts, fiscal year
- `insert_gl_entries(conn, entries, voucher_type, voucher_id, ...)` -- Inserts GL rows atomically
- `reverse_gl_entries(conn, voucher_type, voucher_id, posting_date)` -- Creates reversing entries

**Atomicity:** Submit and cancel operations execute GL posting + status update within a single
SQLite transaction. If any step fails, the entire operation rolls back.

### Recurring Journal Templates (6 actions)

Automate repeating journal entries (rent, subscriptions, accruals). Templates generate JEs on schedule.

| Action | Required Flags | Optional Flags |
|--------|---------------|----------------|
| `add-recurring-template` | `--company-id`, `--template-name`, `--start-date`, `--lines` (JSON) | `--frequency` (monthly), `--end-date`, `--entry-type`, `--auto-submit`, `--remark` |
| `update-recurring-template` | `--template-id` | `--template-name`, `--frequency`, `--end-date`, `--entry-type`, `--lines`, `--auto-submit`, `--remark`, `--template-status` (active/paused) |
| `list-recurring-templates` | `--company-id` | `--template-status`, `--limit`, `--offset` |
| `get-recurring-template` | `--template-id` | (none) |
| `process-recurring` | `--company-id` | `--as-of-date` (defaults to today) |
| `delete-recurring-template` | `--template-id` | (none) |

**Frequencies:** `daily`, `weekly`, `monthly`, `quarterly`, `annual`

**Cron:** `process-recurring` should run daily at 01:00 AM. Idempotent — only generates JEs where `next_run_date <= as_of_date`.

| User Says | Action |
|-----------|--------|
| "set up monthly rent journal" | `add-recurring-template` |
| "process recurring journals" / "run recurring" | `process-recurring` |
| "pause recurring template" | `update-recurring-template --template-status paused` |
| "list recurring templates" | `list-recurring-templates` |

### Sub-Skills

| Sub-Skill | Shortcut | What It Does |
|-----------|----------|-------------|
| `erp-journals` | `/erp-journals` | Lists recent journal entries with status summary |

Overview

This skill manages manual journal entries for ERPClaw with a strict Draft → Submit → Cancel lifecycle. It enforces double-entry accounting, posts immutable GL rows on submit, and reverses them on cancel to preserve an auditable trail. Data is stored locally in a single SQLite file and all operations run offline using the erpclaw shared library.

How this skill works

You create journal drafts with dated lines (account + debit/credit), review them, and then submit to post balanced GL entries via the shared gl_posting library. Submissions validate that SUM(debits) = SUM(credits) and insert GL rows atomically; cancellations never modify existing GL rows but post reversing entries. All script actions are routed through a single db_query.py entry point which returns JSON for the caller to parse and display.

When to use it

  • Record manual transactions that don’t come from modules (adjustments, write-offs, reclassifications).
  • Create opening or closing entries for fiscal year transitions.
  • Post depreciation, exchange revaluation, inter-company or credit/debit note journals.
  • Amend or correct previously submitted journals via the cancel+draft workflow.
  • Automate repeating entries with recurring templates (rent, subscriptions, accruals).

Best practices

  • Always create drafts first and review totals before submitting.
  • Ensure each line has exactly one of debit or credit > 0 and include at least two lines.
  • Confirm before destructive lifecycle actions: submit, cancel, amend, delete.
  • Never run raw SQL against the database — use db_query.py --action to preserve validations and joins.
  • Use the shared validate/insert/reverse functions; check account status and fiscal year if GL posting fails.

Example use cases

  • Create a draft adjusting entry to transfer expenses between cost centers, review, then submit to post GL entries.
  • Post a year-end closing journal that consolidates retained earnings into equity accounts.
  • Reverse an incorrect submitted journal by cancelling it; then amend to create a corrected draft.
  • Set up a monthly recurring rent journal template that auto-generates drafts or auto-submits based on settings.
  • Duplicate a prior journal to create a similar draft, change the posting date, then submit.

FAQ

Where is the database stored and can I change it?

The default DB is ~/.openclaw/erpclaw/data.sqlite. You can override with ERPCLAW_DB_PATH or supply --db-path when initializing the DB.

What happens when I cancel a submitted journal?

Cancellation posts reversing GL entries—existing GL rows remain unchanged to preserve an immutable audit trail.