Salary Wage Accounting

module: salary-wage-accounting
intent: null            # setup | advice | review | fix | build | convert | export
scale: null             # Starter | Growth | Scale, only if the answer changes it
areas:
  "Workforce": null
  "Pay cycle": null
  "Deductions": null
  "Statutory": 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

Payroll Run,Employee,Employee ID,Department,Period Start,Period End,Attendance Days,Paid Days,Gross Salary/Wages,Allowances,Deductions,TDS,SSF/PF Contribution,Other Deductions,Net Salary/Wages,Payment Date,Payment Mode,Ledger Account,Approval Reference,Payroll Liability Balance,Statutory Reconciliation Status,Source Document,Prepared By,Reviewed By,Status,Notes,Salary Record ID
PAYRUN-2026-08,Rohit Menon,EMP-0114,Operations,2026-08-01,2026-08-31,26,26,96000.00,8400.00,1600.00,3600.00,7680.00,500.00,91020.00,2026-08-28,Bank Transfer,Salaries & Wages,APR-PR-2026-08,7680.00,Pending,DOC-2026-0470,Sneha Iyer,Ananya Rao,In progress,"Deductions of 1600.00 and other deductions of 500.00 total 2100.00; PF contribution carried as a liability until deposited.",

CREATE TABLE salary_wage_accounting (
  payroll_run VARCHAR(255),
  employee VARCHAR(255),
  employee_id VARCHAR(255),
  department VARCHAR(100) NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  attendance_days NUMERIC,
  paid_days NUMERIC,
  gross_salary_wages NUMERIC(14,2) NOT NULL,
  allowances NUMERIC(14,2) NOT NULL,
  deductions NUMERIC(14,2) NOT NULL,
  tds NUMERIC(14,2) NOT NULL,
  ssf_pf_contribution NUMERIC(14,2) NOT NULL,
  other_deductions NUMERIC(14,2) NOT NULL,
  net_salary_wages NUMERIC(14,2) NOT NULL,
  payment_date DATE,
  payment_mode VARCHAR(100) NOT NULL,
  ledger_account VARCHAR(255),
  approval_reference VARCHAR(255),
  payroll_liability_balance NUMERIC(14,2) NOT NULL,
  statutory_reconciliation_status VARCHAR(100) NOT NULL,
  source_document VARCHAR(255),  -- relation -> target record
  prepared_by VARCHAR(255),
  reviewed_by VARCHAR(255),
  status VARCHAR(100) NOT NULL,
  notes TEXT,
  salary_record_id SERIAL PRIMARY KEY,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_salary_wage_accounting_status ON salary_wage_accounting (status);

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "Salary & Wage Accounting",
  "type": "object",
  "additionalProperties": false,
  "properties": {
      "Payroll Run": { "type": "string" },
      "Employee": { "type": "string" },
      "Employee ID": { "type": "string" },
      "Department": { "type": "string" },
      "Period Start": { "type": "string", "format": "date" },
      "Period End": { "type": "string", "format": "date" },
      "Attendance Days": { "type": "number" },
      "Paid Days": { "type": "number" },
      "Gross Salary/Wages": { "type": "number" },
      "Allowances": { "type": "number" },
      "Deductions": { "type": "number" },
      "TDS": { "type": "number" },
      "SSF/PF Contribution": { "type": "number" },
      "Other Deductions": { "type": "number" },
      "Net Salary/Wages": { "type": "number" },
      "Payment Date": { "type": "string", "format": "date" },
      "Payment Mode": { "type": "string" },
      "Ledger Account": { "type": "string" },
      "Approval Reference": { "type": "string" },
      "Payroll Liability Balance": { "type": "number" },
      "Statutory Reconciliation Status": { "type": "string" },
      "Source Document": { "type": "string" },
      "Prepared By": { "type": "string" },
      "Reviewed By": { "type": "string" },
      "Status": { "type": "string" },
      "Notes": { "type": "string" },
      "Salary Record ID": { "type": "integer" }
  },
  "required": [
      "Department",
      "Period Start",
      "Period End",
      "Gross Salary/Wages",
      "Allowances",
      "Deductions",
      "TDS",
      "SSF/PF Contribution",
      "Other Deductions",
      "Net Salary/Wages",
      "Payment Mode",
      "Payroll Liability Balance",
      "Statutory Reconciliation Status",
      "Status"
  ]
}

| CSV column | Notion property | Set after import |
|---|---|---|
| Payroll Run | Title | Use as the database title |
| Employee | Text | Leave as Text |
| Employee ID | Text | Leave as Text |
| Department | Select (add options after import) | Convert to Select, add options: "Sales", "Purchase", "Accounts", "Payroll", "Admin", "Finance", "Operations" |
| Period Start | Date | Convert to Date |
| Period End | Date | Convert to Date |
| Attendance Days | Number | Convert to Number |
| Paid Days | Number | Convert to Number |
| Gross Salary/Wages | Number (format: currency) | Convert to Number, set format to Currency |
| Allowances | Number (format: currency) | Convert to Number, set format to Currency |
| Deductions | Number (format: currency) | Convert to Number, set format to Currency |
| TDS | Number (format: currency) | Convert to Number, set format to Currency |
| SSF/PF Contribution | Number (format: currency) | Convert to Number, set format to Currency |
| Other Deductions | Number (format: currency) | Convert to Number, set format to Currency |
| Net Salary/Wages | Number (format: currency) | Convert to Number, set format to Currency |
| Payment Date | Date | Convert to Date |
| Payment Mode | Select (add options after import) | Convert to Select, add options: "Bank Transfer", "Cash", "Cheque", "Digital Payment" |
| Ledger Account | Text | Leave as Text |
| Approval Reference | Text | Leave as Text |
| Payroll Liability Balance | Number (format: currency) | Convert to Number, set format to Currency |
| Statutory Reconciliation Status | Select (add options after import) | Convert to Select, add options: "Reconciled", "Pending", "Variance" |
| Source Document | Relation (link to the target database) | Convert to Relation, link to the target database |
| Prepared By | Text | Leave as Text |
| 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 |
| Salary Record ID | Text (preserve source ID) | Keep imported IDs as Text; optionally add a separate Unique ID property |