phantomknight287/saturn

public

The operating system for your freelance business.

feat: here comes the money442302e7 months ago135 lines · 8.4 KB

1	CREATE TYPE "public"."invoice_status" AS ENUM('draft', 'sent', 'disputed', 'paid', 'cancelled');--> statement-breakpoint
2	CREATE TYPE "public"."timesheet_report_status" AS ENUM('draft', 'sent', 'approved', 'disputed');--> statement-breakpoint
3	CREATE TABLE "invoice_items" (
4	"id" text PRIMARY KEY NOT NULL,
5	"invoice_id" text NOT NULL,
6	"description" text NOT NULL,
7	"quantity" numeric(10, 4) NOT NULL,
8	"unit_price" numeric(16, 4) NOT NULL,
9	"amount" numeric(16, 4) NOT NULL,
10	"sort_order" integer DEFAULT 0 NOT NULL
11	);
12	--> statement-breakpoint
13	CREATE TABLE "invoice_recipients" (
14	"id" text PRIMARY KEY NOT NULL,
15	"invoice_id" text NOT NULL,
16	"client_member_id" text NOT NULL
17	);
18	--> statement-breakpoint
19	CREATE TABLE "invoice_requirements" (
20	"id" text PRIMARY KEY NOT NULL,
21	"invoice_id" text NOT NULL,
22	"requirement_id" text NOT NULL
23	);
24	--> statement-breakpoint
25	CREATE TABLE "invoices" (
26	"id" text PRIMARY KEY NOT NULL,
27	"project_id" text NOT NULL,
28	"invoice_number" text NOT NULL,
29	"status" "invoice_status" DEFAULT 'draft' NOT NULL,
30	"issue_date" timestamp DEFAULT now() NOT NULL,
31	"due_date" timestamp,
32	"notes" text,
33	"total_amount" numeric(16, 4) DEFAULT '0' NOT NULL,
34	"currency" text DEFAULT 'USD' NOT NULL,
35	"sender_logo" text,
36	"sender_signature" text,
37	"sender_name" text,
38	"sender_address" text,
39	"sender_custom_fields" jsonb,
40	"client_name" text,
41	"client_address" text,
42	"client_custom_fields" jsonb,
43	"payment_terms" text,
44	"terms" text,
45	"discount_label" text,
46	"discount_amount" numeric(16, 4),
47	"created_at" timestamp DEFAULT now() NOT NULL,
48	"updated_at" timestamp DEFAULT now() NOT NULL,
49	CONSTRAINT "invoices_invoice_number_unique" UNIQUE("invoice_number")
50	);
51	--> statement-breakpoint
52	CREATE TABLE "member_rates" (
53	"id" text PRIMARY KEY NOT NULL,
54	"member_id" text NOT NULL,
55	"project_id" text,
56	"hourly_rate" integer NOT NULL,
57	"currency" text DEFAULT 'USD' NOT NULL,
58	"effective_from" timestamp NOT NULL,
59	"created_at" timestamp DEFAULT now() NOT NULL
60	);
61	--> statement-breakpoint
62	CREATE TABLE "project_budgets" (
63	"id" text PRIMARY KEY NOT NULL,
64	"project_id" text NOT NULL,
65	"budget_minutes" integer NOT NULL,
66	"alert_threshold" integer DEFAULT 80 NOT NULL,
67	"created_at" timestamp DEFAULT now() NOT NULL,
68	"updated_at" timestamp DEFAULT now() NOT NULL,
69	CONSTRAINT "project_budgets_project_id_unique" UNIQUE("project_id")
70	);
71	--> statement-breakpoint
72	CREATE TABLE "time_entries" (
73	"id" text PRIMARY KEY NOT NULL,
74	"project_id" text NOT NULL,
75	"requirement_id" text,
76	"member_id" text NOT NULL,
77	"description" text NOT NULL,
78	"date" timestamp NOT NULL,
79	"duration_minutes" integer NOT NULL,
80	"billable" boolean DEFAULT true NOT NULL,
81	"status" "status" DEFAULT 'draft' NOT NULL,
82	"reject_reason" text,
83	"invoice_id" text,
84	"created_at" timestamp DEFAULT now() NOT NULL,
85	"updated_at" timestamp DEFAULT now() NOT NULL
86	);
87	--> statement-breakpoint
88	CREATE TABLE "timesheet_report_entries" (
89	"id" text PRIMARY KEY NOT NULL,
90	"report_id" text NOT NULL,
91	"time_entry_id" text NOT NULL
92	);
93	--> statement-breakpoint
94	CREATE TABLE "timesheet_reports" (
95	"id" text PRIMARY KEY NOT NULL,
96	"project_id" text NOT NULL,
97	"title" text NOT NULL,
98	"status" timesheet_report_status DEFAULT 'draft' NOT NULL,
99	"total_minutes" integer NOT NULL,
100	"total_amount_cents" integer DEFAULT 0 NOT NULL,
101	"currency" text DEFAULT 'USD' NOT NULL,
102	"sent_by_member_id" text,
103	"client_member_id" text NOT NULL,
104	"dispute_reason" text,
105	"sent_at" timestamp,
106	"responded_at" timestamp,
107	"created_at" timestamp DEFAULT now() NOT NULL,
108	"updated_at" timestamp DEFAULT now() NOT NULL
109	);
110	--> statement-breakpoint
111	ALTER TABLE "invoice_items" ADD CONSTRAINT "invoice_items_invoice_id_invoices_id_fk" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
112	ALTER TABLE "invoice_recipients" ADD CONSTRAINT "invoice_recipients_invoice_id_invoices_id_fk" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
113	ALTER TABLE "invoice_recipients" ADD CONSTRAINT "invoice_recipients_client_member_id_members_id_fk" FOREIGN KEY ("client_member_id") REFERENCES "public"."members"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
114	ALTER TABLE "invoice_requirements" ADD CONSTRAINT "invoice_requirements_invoice_id_invoices_id_fk" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
115	ALTER TABLE "invoice_requirements" ADD CONSTRAINT "invoice_requirements_requirement_id_requirements_id_fk" FOREIGN KEY ("requirement_id") REFERENCES "public"."requirements"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
116	ALTER TABLE "invoices" ADD CONSTRAINT "invoices_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
117	ALTER TABLE "invoices" ADD CONSTRAINT "invoices_sender_logo_media_id_fk" FOREIGN KEY ("sender_logo") REFERENCES "public"."media"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
118	ALTER TABLE "invoices" ADD CONSTRAINT "invoices_sender_signature_media_id_fk" FOREIGN KEY ("sender_signature") REFERENCES "public"."media"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
119	ALTER TABLE "member_rates" ADD CONSTRAINT "member_rates_member_id_members_id_fk" FOREIGN KEY ("member_id") REFERENCES "public"."members"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
120	ALTER TABLE "member_rates" ADD CONSTRAINT "member_rates_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
121	ALTER TABLE "project_budgets" ADD CONSTRAINT "project_budgets_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
122	ALTER TABLE "time_entries" ADD CONSTRAINT "time_entries_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
123	ALTER TABLE "time_entries" ADD CONSTRAINT "time_entries_requirement_id_requirements_id_fk" FOREIGN KEY ("requirement_id") REFERENCES "public"."requirements"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
124	ALTER TABLE "time_entries" ADD CONSTRAINT "time_entries_member_id_members_id_fk" FOREIGN KEY ("member_id") REFERENCES "public"."members"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
125	ALTER TABLE "time_entries" ADD CONSTRAINT "time_entries_invoice_id_invoices_id_fk" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
126	ALTER TABLE "timesheet_report_entries" ADD CONSTRAINT "timesheet_report_entries_report_id_timesheet_reports_id_fk" FOREIGN KEY ("report_id") REFERENCES "public"."timesheet_reports"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
127	ALTER TABLE "timesheet_report_entries" ADD CONSTRAINT "timesheet_report_entries_time_entry_id_time_entries_id_fk" FOREIGN KEY ("time_entry_id") REFERENCES "public"."time_entries"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
128	ALTER TABLE "timesheet_reports" ADD CONSTRAINT "timesheet_reports_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
129	ALTER TABLE "timesheet_reports" ADD CONSTRAINT "timesheet_reports_sent_by_member_id_members_id_fk" FOREIGN KEY ("sent_by_member_id") REFERENCES "public"."members"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
130	ALTER TABLE "timesheet_reports" ADD CONSTRAINT "timesheet_reports_client_member_id_members_id_fk" FOREIGN KEY ("client_member_id") REFERENCES "public"."members"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
131	CREATE INDEX "invoices_project_id_status_idx" ON "invoices" USING btree ("project_id","status");--> statement-breakpoint
132	CREATE UNIQUE INDEX "member_rate_effective_unique" ON "member_rates" USING btree ("member_id","project_id","effective_from");--> statement-breakpoint
133	CREATE INDEX "time_entries_project_id_status_idx" ON "time_entries" USING btree ("project_id","status");--> statement-breakpoint
134	CREATE INDEX "time_entries_member_id_idx" ON "time_entries" USING btree ("member_id");--> statement-breakpoint
135	CREATE INDEX "timesheet_report_entries_report_id_idx" ON "timesheet_report_entries" USING btree ("report_id");