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
3	CREATE TABLE "invoice_items" (
4	"id" text PRIMARY KEY
5	"invoice_id" text NOT NULL
6	"description" text NOT NULL
7	"quantity" numeric(10
8	"unit_price" numeric(16
9	"amount" numeric(16
10	"sort_order" integer DEFAULT
11	);
12	--> statement-breakpoint
13	CREATE TABLE "invoice_recipients" (
14	"id" text PRIMARY KEY
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
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
27	"project_id" text NOT NULL
28	"invoice_number" text NOT NULL
29	"status" "invoice_status" DEFAULT
30	"issue_date" timestamp DEFAULT
31	"due_date" timestamp,
32	"notes" text,
33	"total_amount" numeric(16
34	"currency" text DEFAULT
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
47	"created_at" timestamp DEFAULT
48	"updated_at" timestamp DEFAULT
49	CONSTRAINT "invoices_invoice_number_unique" UNIQUE
50	);
51	--> statement-breakpoint
52	CREATE TABLE "member_rates" (
53	"id" text PRIMARY KEY
54	"member_id" text NOT NULL
55	"project_id" text,
56	"hourly_rate" integer NOT NULL
57	"currency" text DEFAULT
58	"effective_from" timestamp NOT NULL
59	"created_at" timestamp DEFAULT
60	);
61	--> statement-breakpoint
62	CREATE TABLE "project_budgets" (
63	"id" text PRIMARY KEY
64	"project_id" text NOT NULL
65	"budget_minutes" integer NOT NULL
66	"alert_threshold" integer DEFAULT
67	"created_at" timestamp DEFAULT
68	"updated_at" timestamp DEFAULT
69	CONSTRAINT "project_budgets_project_id_unique" UNIQUE
70	);
71	--> statement-breakpoint
72	CREATE TABLE "time_entries" (
73	"id" text PRIMARY KEY
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
81	"status" "status" DEFAULT
82	"reject_reason" text,
83	"invoice_id" text,
84	"created_at" timestamp DEFAULT
85	"updated_at" timestamp DEFAULT
86	);
87	--> statement-breakpoint
88	CREATE TABLE "timesheet_report_entries" (
89	"id" text PRIMARY KEY
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
96	"project_id" text NOT NULL
97	"title" text NOT NULL
98	"status" timesheet_report_status DEFAULT '
99	"total_minutes" integer NOT NULL
100	"total_amount_cents" integer DEFAULT
101	"currency" text DEFAULT
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
108	"updated_at" timestamp DEFAULT
109	);
110	--> statement-breakpoint
111	ALTER TABLE "invoice_items" ADD
112	ALTER TABLE "invoice_recipients" ADD
113	ALTER TABLE "invoice_recipients" ADD
114	ALTER TABLE "invoice_requirements" ADD
115	ALTER TABLE "invoice_requirements" ADD
116	ALTER TABLE "invoices" ADD
117	ALTER TABLE "invoices" ADD
118	ALTER TABLE "invoices" ADD
119	ALTER TABLE "member_rates" ADD
120	ALTER TABLE "member_rates" ADD
121	ALTER TABLE "project_budgets" ADD
122	ALTER TABLE "time_entries" ADD
123	ALTER TABLE "time_entries" ADD
124	ALTER TABLE "time_entries" ADD
125	ALTER TABLE "time_entries" ADD
126	ALTER TABLE "timesheet_report_entries" ADD
127	ALTER TABLE "timesheet_report_entries" ADD
128	ALTER TABLE "timesheet_reports" ADD
129	ALTER TABLE "timesheet_reports" ADD
130	ALTER TABLE "timesheet_reports" ADD
131	CREATE INDEX "invoices_project_id_status_idx" ON
132	CREATE UNIQUE INDEX "member_rate_effective_unique" ON
133	CREATE INDEX "time_entries_project_id_status_idx" ON
134	CREATE INDEX "time_entries_member_id_idx" ON
135	CREATE INDEX "timesheet_report_entries_report_id_idx" ON