grabowski
3417ed6698
- expense_kind enum (utilities + maintenance/repair/cleaning/insurance/tax/rent/other) - property_expenses table with optional link to a property_accounts row (preserves history via ON DELETE SET NULL) - services/expenses.ts: CRUD + 12-month monthly series aggregation + year-to-date summary by kind - /properties/[id]/expenses tab: inline SVG line chart for electricity + water last 12 months (no chart library), summary card, add/edit/delete inline with account linking when kind matches
25 lines
1.8 KiB
SQL
25 lines
1.8 KiB
SQL
CREATE TYPE "public"."expense_kind" AS ENUM('water', 'electricity', 'gas', 'internet', 'phone', 'cable', 'waste', 'maintenance', 'repair', 'cleaning', 'insurance', 'tax', 'rent', 'other');--> statement-breakpoint
|
|
CREATE TABLE "property_expenses" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"property_id" uuid NOT NULL,
|
|
"account_id" uuid,
|
|
"kind" "expense_kind" NOT NULL,
|
|
"amount" numeric(18, 4) NOT NULL,
|
|
"currency" varchar(3) NOT NULL,
|
|
"period_start" timestamp with time zone,
|
|
"period_end" timestamp with time zone,
|
|
"incurred_at" timestamp with time zone NOT NULL,
|
|
"vendor" varchar(128),
|
|
"reference" varchar(128),
|
|
"notes" text,
|
|
"created_by" uuid,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "property_expenses" ADD CONSTRAINT "property_expenses_property_id_properties_id_fk" FOREIGN KEY ("property_id") REFERENCES "public"."properties"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "property_expenses" ADD CONSTRAINT "property_expenses_account_id_property_accounts_id_fk" FOREIGN KEY ("account_id") REFERENCES "public"."property_accounts"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "property_expenses" ADD CONSTRAINT "property_expenses_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
CREATE INDEX "expenses_by_property_time" ON "property_expenses" USING btree ("property_id","incurred_at");--> statement-breakpoint
|
|
CREATE INDEX "expenses_by_property_kind" ON "property_expenses" USING btree ("property_id","kind","incurred_at");--> statement-breakpoint
|
|
CREATE INDEX "expenses_by_account" ON "property_expenses" USING btree ("account_id"); |