Party Ledger Reconciliation

module: party-ledger-reconciliation
intent: null            # setup | advice | review | fix | build | convert | export
scale: null             # Starter | Growth | Scale, only if the answer changes it
areas:
  "Parties": null
  "Balances": null
  "Confirmations": null
  "Adjustments": null
  "Outcome": null
requested_outputs: []   # csv | sql | json | notion | xlsx - requested formats only
confirmed_facts: []     # only what the user actually said
open_questions: []      # the unanswered ones, in the order worth asking

Reconciliation Number,Period Start,Period End,Party Type,Party Name,Party PAN/VAT,Ledger Balance,Statement Balance,Difference,Missing Invoices,Missing Receipts/Payments,Duplicate Entries,Credit/Debit Notes,Unadjusted Advances,Confirmation Sent,Confirmation Date,Confirmation Status,Difference Reason,Adjustment Required,Adjustment Entry,Adjustment Date,Adjusted By,Reconciliation Frequency,Reviewed By,Status,Notes,Reconciliation ID
REC-2026-0044,2026-08-01,2026-08-31,Customer/Debtor,Greyson Foods,33CDEFG9012H1Z9,486200.00,471200.00,15000.00,1,2,3,4500.00,15000.00,Yes,2026-09-03,Partially Confirmed,Advance held in our books but not credited by the party,Yes,ADJ-2026-0118,2026-09-05,Sneha Iyer,Monthly,Vikram Singh,In progress,"2 missing receipts, 3 duplicate entries and a 4500.00 credit note cleared on both sides; difference matches the unadjusted advance, adjustment raised and pending review.",

CREATE TABLE party_ledger_reconciliation (
  reconciliation_number VARCHAR(255),
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  party_type VARCHAR(100) NOT NULL,
  party_name VARCHAR(255),
  party_pan_vat VARCHAR(255),
  ledger_balance NUMERIC(14,2) NOT NULL,
  statement_balance NUMERIC(14,2) NOT NULL,
  difference NUMERIC(14,2) NOT NULL,
  missing_invoices NUMERIC,
  missing_receipts_payments NUMERIC,
  duplicate_entries NUMERIC,
  credit_debit_notes NUMERIC(14,2),
  unadjusted_advances NUMERIC(14,2),
  confirmation_sent VARCHAR(100),
  confirmation_date DATE,
  confirmation_status VARCHAR(100) NOT NULL,
  difference_reason VARCHAR(255),
  adjustment_required VARCHAR(100) NOT NULL,
  adjustment_entry VARCHAR(255),
  adjustment_date DATE,
  adjusted_by VARCHAR(255),
  reconciliation_frequency VARCHAR(100),
  reviewed_by VARCHAR(255),
  status VARCHAR(100) NOT NULL,
  notes TEXT,
  reconciliation_id SERIAL PRIMARY KEY,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_party_ledger_reconciliation_status ON party_ledger_reconciliation (status);

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "Party / Ledger Reconciliation",
  "type": "object",
  "additionalProperties": false,
  "properties": {
      "Reconciliation Number": { "type": "string" },
      "Period Start": { "type": "string", "format": "date" },
      "Period End": { "type": "string", "format": "date" },
      "Party Type": { "type": "string" },
      "Party Name": { "type": "string" },
      "Party PAN/VAT": { "type": "string" },
      "Ledger Balance": { "type": "number" },
      "Statement Balance": { "type": "number" },
      "Difference": { "type": "number" },
      "Missing Invoices": { "type": "number" },
      "Missing Receipts/Payments": { "type": "number" },
      "Duplicate Entries": { "type": "number" },
      "Credit/Debit Notes": { "type": "number" },
      "Unadjusted Advances": { "type": "number" },
      "Confirmation Sent": { "type": "string" },
      "Confirmation Date": { "type": "string", "format": "date" },
      "Confirmation Status": { "type": "string" },
      "Difference Reason": { "type": "string" },
      "Adjustment Required": { "type": "string" },
      "Adjustment Entry": { "type": "string" },
      "Adjustment Date": { "type": "string", "format": "date" },
      "Adjusted By": { "type": "string" },
      "Reconciliation Frequency": { "type": "string" },
      "Reviewed By": { "type": "string" },
      "Status": { "type": "string" },
      "Notes": { "type": "string" },
      "Reconciliation ID": { "type": "integer" }
  },
  "required": [
      "Period Start",
      "Period End",
      "Party Type",
      "Ledger Balance",
      "Statement Balance",
      "Difference",
      "Confirmation Status",
      "Adjustment Required",
      "Status"
  ]
}

| CSV column | Notion property | Set after import |
|---|---|---|
| Reconciliation Number | Title | Use as the database title |
| Period Start | Date | Convert to Date |
| Period End | Date | Convert to Date |
| Party Type | Select (add options after import) | Convert to Select, add options: "Customer/Debtor", "Supplier/Creditor", "Employee", "Bank", "Other" |
| Party Name | Text | Leave as Text |
| Party PAN/VAT | Text | Leave as Text |
| Ledger Balance | Number (format: currency) | Convert to Number, set format to Currency |
| Statement Balance | Number (format: currency) | Convert to Number, set format to Currency |
| Difference | Number (format: currency) | Convert to Number, set format to Currency |
| Missing Invoices | Number | Convert to Number |
| Missing Receipts/Payments | Number | Convert to Number |
| Duplicate Entries | Number | Convert to Number |
| Credit/Debit Notes | Number (format: currency) | Convert to Number, set format to Currency |
| Unadjusted Advances | Number (format: currency) | Convert to Number, set format to Currency |
| Confirmation Sent | Select (add options after import) | Convert to Select, add options: "Yes", "No", "Not Required" |
| Confirmation Date | Date | Convert to Date |
| Confirmation Status | Select (add options after import) | Convert to Select, add options: "Not Sent", "Sent", "Confirmed", "Partially Confirmed", "Disputed", "No Response" |
| Difference Reason | Text | Leave as Text |
| Adjustment Required | Select (add options after import) | Convert to Select, add options: "Yes", "No" |
| Adjustment Entry | Text | Leave as Text |
| Adjustment Date | Date | Convert to Date |
| Adjusted By | Text | Leave as Text |
| Reconciliation Frequency | Select (add options after import) | Convert to Select, add options: "Monthly", "Quarterly", "Half-Yearly", "Annual" |
| Reviewed By | Text | Leave as Text |
| Status | Select (add options after import) | Convert to Select, add options: "Not started", "In progress", "Blocked", "Done", "Cancelled" |
| Notes | Text | Leave as Text |
| Reconciliation ID | Text (preserve source ID) | Keep imported IDs as Text; optionally add a separate Unique ID property |