Purchase Accounting

module: purchase-accounting
intent: null            # setup | advice | review | fix | build | convert | export
scale: null             # Starter | Growth | Scale, only if the answer changes it
areas:
  "Volume": null
  "Verification": null
  "Taxes and classification": null
  "Current process": 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

Purchase Number,Supplier,Supplier PAN/VAT,Purchase Order,Delivery Challan,Supplier Invoice Number,Invoice Date,Item/Expense Description,Quantity,UOM,Rate,Gross Amount,VAT Rate %,VAT Amount,TDS Rate %,TDS Amount,Net Payable,Classification,Ledger Account,Source Document,Payment Status,Amount Paid,Balance,VAT Register Updated,Entry Verified,Prepared By,Verified By,Notes,Purchase ID
PUR-2026-0041,Bluepeak Supplies,29ABCDE1234F1Z5,PO-2026-0187,DC-2026-0902,INV-8841,2026-08-14,Corrugated cartons 3 ply,500,Nos,169.00,84500.00,18,15210.00,2.5,2112.50,97597.50,Inventory/Purchase,Purchases - Cartons,DOC-2026-0442,Part Paid,50000.00,47597.50,Done,In progress,Ananya Rao,Vikram Singh,Rate compared against PO-2026-0187; quantity matched with DC-2026-0902.,

CREATE TABLE purchase_accounting (
  purchase_number VARCHAR(255),
  supplier VARCHAR(255),
  supplier_pan_vat VARCHAR(255),
  purchase_order VARCHAR(255),  -- relation -> target record
  delivery_challan VARCHAR(255),  -- relation -> target record
  supplier_invoice_number VARCHAR(255),
  invoice_date DATE NOT NULL,
  item_expense_description VARCHAR(255),
  quantity NUMERIC NOT NULL,
  uom VARCHAR(100) NOT NULL,
  rate NUMERIC(14,2) NOT NULL,
  gross_amount NUMERIC(14,2) NOT NULL,
  vat_rate_pct NUMERIC NOT NULL,
  vat_amount NUMERIC(14,2) NOT NULL,
  tds_rate_pct NUMERIC NOT NULL,
  tds_amount NUMERIC(14,2) NOT NULL,
  net_payable NUMERIC(14,2) NOT NULL,
  classification VARCHAR(100) NOT NULL,
  ledger_account VARCHAR(255),
  source_document VARCHAR(255),  -- relation -> target record
  payment_status VARCHAR(100) NOT NULL,
  amount_paid NUMERIC(14,2) NOT NULL,
  balance NUMERIC(14,2) NOT NULL,
  vat_register_updated VARCHAR(100) NOT NULL,
  entry_verified VARCHAR(100) NOT NULL,
  prepared_by VARCHAR(255),
  verified_by VARCHAR(255),
  notes TEXT,
  purchase_id SERIAL PRIMARY KEY,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "Purchase Accounting",
  "type": "object",
  "additionalProperties": false,
  "properties": {
      "Purchase Number": { "type": "string" },
      "Supplier": { "type": "string" },
      "Supplier PAN/VAT": { "type": "string" },
      "Purchase Order": { "type": "string" },
      "Delivery Challan": { "type": "string" },
      "Supplier Invoice Number": { "type": "string" },
      "Invoice Date": { "type": "string", "format": "date" },
      "Item/Expense Description": { "type": "string" },
      "Quantity": { "type": "number" },
      "UOM": { "type": "string" },
      "Rate": { "type": "number" },
      "Gross Amount": { "type": "number" },
      "VAT Rate %": { "type": "number" },
      "VAT Amount": { "type": "number" },
      "TDS Rate %": { "type": "number" },
      "TDS Amount": { "type": "number" },
      "Net Payable": { "type": "number" },
      "Classification": { "type": "string" },
      "Ledger Account": { "type": "string" },
      "Source Document": { "type": "string" },
      "Payment Status": { "type": "string" },
      "Amount Paid": { "type": "number" },
      "Balance": { "type": "number" },
      "VAT Register Updated": { "type": "string" },
      "Entry Verified": { "type": "string" },
      "Prepared By": { "type": "string" },
      "Verified By": { "type": "string" },
      "Notes": { "type": "string" },
      "Purchase ID": { "type": "integer" }
  },
  "required": [
      "Invoice Date",
      "Quantity",
      "UOM",
      "Rate",
      "Gross Amount",
      "VAT Rate %",
      "VAT Amount",
      "TDS Rate %",
      "TDS Amount",
      "Net Payable",
      "Classification",
      "Payment Status",
      "Amount Paid",
      "Balance",
      "VAT Register Updated",
      "Entry Verified"
  ]
}

| CSV column | Notion property | Set after import |
|---|---|---|
| Purchase Number | Title | Use as the database title |
| Supplier | Text | Leave as Text |
| Supplier PAN/VAT | Text | Leave as Text |
| Purchase Order | Relation (link to the target database) | Convert to Relation, link to the target database |
| Delivery Challan | Relation (link to the target database) | Convert to Relation, link to the target database |
| Supplier Invoice Number | Text | Leave as Text |
| Invoice Date | Date | Convert to Date |
| Item/Expense Description | Text | Leave as Text |
| Quantity | Number | Convert to Number |
| UOM | Select (add options after import) | Convert to Select, add options: "Nos", "Kg", "Litre", "Metre", "Set", "Hour", "Box", "Packet" |
| Rate | Number (format: currency) | Convert to Number, set format to Currency |
| Gross Amount | Number (format: currency) | Convert to Number, set format to Currency |
| VAT Rate % | Number | Convert to Number |
| VAT Amount | Number (format: currency) | Convert to Number, set format to Currency |
| TDS Rate % | Number | Convert to Number |
| TDS Amount | Number (format: currency) | Convert to Number, set format to Currency |
| Net Payable | Number (format: currency) | Convert to Number, set format to Currency |
| Classification | Select (add options after import) | Convert to Select, add options: "Inventory/Purchase", "Expense", "Fixed Asset", "Other" |
| Ledger Account | Text | Leave as Text |
| Source Document | Relation (link to the target database) | Convert to Relation, link to the target database |
| Payment Status | Select (add options after import) | Convert to Select, add options: "Unpaid", "Part Paid", "Paid", "Overdue" |
| Amount Paid | Number (format: currency) | Convert to Number, set format to Currency |
| Balance | Number (format: currency) | Convert to Number, set format to Currency |
| VAT Register Updated | Select (add options after import) | Convert to Select, add options: "Not started", "In progress", "Blocked", "Done", "Cancelled" |
| Entry Verified | Select (add options after import) | Convert to Select, add options: "Not started", "In progress", "Blocked", "Done", "Cancelled" |
| Prepared By | Text | Leave as Text |
| Verified By | Text | Leave as Text |
| Notes | Text | Leave as Text |
| Purchase ID | Text (preserve source ID) | Keep imported IDs as Text; optionally add a separate Unique ID property |