Series links:

What

A minimal PostgreSQL structure with just two tables: one for the transaction entry and one for the attached documents (bills/receipts).

Why

  • Keep it simple so you can start quickly.
  • Easy to query, filter by date/text, and export.

How

Tables at a glance

  • accounting_entry: one row per transaction (expense or income).

    • entry_date: when it happened.
    • amount: the value; CR/DR tells direction.
    • cr_dr: ‘CR’ for money in, ‘DR’ for money out.
    • category: a simple label you choose (e.g., Groceries, Fuel).
    • description: short note to help search later.
    • human_code: short human-friendly code you can cite (shown on the listing).
    • id: internal unique id.

  • accounting_document: zero or more documents for each entry (your bills/receipts).

    • accounting_entry_id: links the document to its entry.
    • file_path: where the file is stored (S3 key or path).
    • doc_code: short code for the document (helps reference a specific file).
    • id: internal unique id.

Why two tables?

  • An entry is the transaction itself.
  • Documents are the evidences you attach; you can upload many files to one entry.
  • This keeps data tidy and makes searching/exporting fast.

Run these SQL statements in your PostgreSQL database.

CREATE TABLE public.accounting_document (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  accounting_entry_id uuid NOT NULL,
  file_path text NOT NULL,
  doc_code character varying(16) NOT NULL,
  CONSTRAINT accounting_document_pkey PRIMARY KEY (id)
);

CREATE TABLE public.accounting_entry (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  entry_date date NOT NULL,
  amount numeric(18,2) NOT NULL,
  cr_dr character varying(2) NOT NULL,
  category character varying(100) NOT NULL,
  description text NULL,
  human_code character varying(16) NOT NULL,
  CONSTRAINT accounting_entry_pkey PRIMARY KEY (id)
);

Notes:

  • cr_dr holds CR or DR.
  • file_path stores the S3 object key (for example: 20251001-01-oct-2025-bill.jpg).
  • You can add a foreign key from accounting_document.accounting_entry_id to accounting_entry.id if you prefer strict linking.
  • Indexes you can add later if needed: date index on entry_date, and simple btree/text indexes for category/description.

Thoughts / Caveats

  • Use categories you actually search for (e.g., Groceries, Fuel, Rent).
  • Don’t over-design; this is enough for searching and CSV export.
  • Backups matter. Take periodic snapshots of the database.