`db/migrations/0013_assets.sql`

34 lines
-- 0013_assets.sql
-- Assets / net-worth tracker: the user enters things they own (home by address, vehicles,
-- accounts) with a current value, a finance category, and free-form details + notes.
-- Feeds a simple net-worth total and (later) links to the neighborhood-watch feature via
-- a property's address. Values are user-entered (or, later, an optional estimate API).
-- Idempotent. Safe to re-run.

BEGIN;

CREATE TABLE IF NOT EXISTS asset (
  id             text PRIMARY KEY,
  user_id        text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  person_id      text REFERENCES person(id) ON DELETE SET NULL,   -- optional owner
  kind           text NOT NULL DEFAULT 'property',   -- property|vehicle|account|valuable|other
  name           text NOT NULL,                       -- e.g. "Primary residence"
  address        text,                                -- for property (also used by neighborhood watch)
  latitude       numeric(9,6),                        -- optional geocode
  longitude      numeric(9,6),
  current_value  numeric(14,2),
  value_source   text NOT NULL DEFAULT 'manual',      -- manual|estimate
  category       text,                                -- finance grouping: real_estate|vehicle|cash|investment|other (free text)
  details        text,                                -- structured-ish details (beds/baths, VIN, acct hint…)
  notes          text,                                -- free-form
  valued_at      date,                                -- as-of date for current_value
  status         text NOT NULL DEFAULT 'active',      -- active|sold|archived
  metadata_jsonb jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS asset_user_idx      ON asset (user_id, status);
CREATE INDEX IF NOT EXISTS asset_category_idx  ON asset (user_id, category);

COMMIT;