2026-02-11 23:45:15 -08:00

4.7 KiB

Raw Permalink Blame History

Spendings → Entries Schema Migration Record

Purpose

This document preserves the original spendings schema and maps it to the new entries schema for future debugging and data migration. It is a historical reference and should be kept alongside the migration that introduces entries.

Source Migrations Reviewed

Baseline Spendings Schema (Pre‑Migration)

From packages/db/migrations/001_init.sql:

Table: `spendings`

id bigserial PK
group_id bigint FK → groups(id) ON DELETE CASCADE
created_by bigint FK → users(id)
amount_dollars numeric(12,2) NOT NULL CHECK (amount_dollars >= 0)
occurred_at date NOT NULL
necessity enum spending_necessity NOT NULL
purchase_type text NOT NULL
notes text NULL
receipt_id bigint FK → receipts(id) ON DELETE SET NULL
created_at timestamptz NOT NULL DEFAULT now()

Indexes

idx_spendings_group_occurred on (group_id, occurred_at DESC)
idx_spendings_receipt on (receipt_id)

receipts stores image bytes: bytes bytea NOT NULL and content_type.
tags and spending_tags define many‑to‑many tagging.

Amount Conversion (Historical)

From packages/db/migrations/002_amount_dollars.sql:

spendings.amount_cents renamed to amount_dollars
Type changed to numeric(12,2) using amount_dollars / 100.0

Note: This implies older environments may have amount_cents. Fresh installs already use amount_dollars from 001_init.sql.

Tags & Group Settings (Baseline)

From packages/db/migrations/002_tags.sql:

Table: `group_settings`

group_id PK FK → groups(id)
allow_member_tag_manage boolean NOT NULL DEFAULT false
updated_at timestamptz NOT NULL DEFAULT now()

Table: `tags`

id bigserial PK
group_id bigint FK → groups(id) ON DELETE CASCADE
name text NOT NULL
created_by bigint FK → users(id)
created_at timestamptz NOT NULL DEFAULT now()

Constraints & Indexes

Unique: tags_group_name_unique on (group_id, lower(name))
Index: tags_group_id_idx on (group_id)

Join Table: `spending_tags`

spending_id bigint FK → spendings(id) ON DELETE CASCADE
tag_id bigint FK → tags(id) ON DELETE CASCADE
created_at timestamptz NOT NULL DEFAULT now()
PK (spending_id, tag_id)
Indexes: spending_tags_spending_idx, spending_tags_tag_idx

Spendings → Entries Mapping (Planned)

Rename Summary

spending → entry
spendings → entries
“spending” labels → “entry” (or “transaction”) in UI and API

Column Mapping

Spendings Column	Entries Column	Notes
`id`	`id`	same PK strategy
`group_id`	`group_id`	same FK
`created_by`	`created_by`	same FK
`amount_dollars`	`amount_dollars`	same type/check
`occurred_at`	`occurred_at`	same type
`necessity`	`necessity`	still applicable for spending entries
`purchase_type`	`purchase_type`	same
`notes`	`notes`	same
`receipt_id`	`receipt_id`	same FK
`created_at`	`created_at`	same

New Entries Fields

entry_type enum (SPENDING, INCOME) or equivalent flag
Recurring schedule fields (see below)

Recurring Entries (Planned Fields)

Add to entries (or a dedicated recurring_entries) table:

is_recurring boolean NOT NULL DEFAULT false
frequency enum (daily, weekly, biweekly, monthly, quarterly, yearly)
interval_count integer NOT NULL DEFAULT 1
end_condition enum (never, after_count, by_date)
end_count integer NULL
end_date date NULL
next_run_at date NULL (set on creation; if start date is today, set to today)
last_executed_at date NULL DEFAULT NULL

Reminder: Recurrence execution must be implemented via cron/scheduler later.

Non‑Regression Notes

Spendings list endpoints must not return receipt image bytes (receipts.bytes).
Tag uniqueness is case‑insensitive per group.

Known Caveats

Two migrations share the 002_ prefix; migration ordering must be deterministic.
002_amount_dollars.sql assumes legacy amount_cents exists; skip/guard on fresh installs.

Verification Checklist

Entries schema mirrors spendings constraints and indexes.
Data migration preserves values and tag associations.
Tests updated for new names and income/spending support.

Last updated: 2026-02-09

4.7 KiB Raw Permalink Blame History Unescape Escape