331 lines
14 KiB
SQL
331 lines
14 KiB
SQL
CREATE TABLE "financial_balances" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"user_id" text,
|
|
"balance" double precision,
|
|
"balance_date" bigint,
|
|
"encrypted_data" text,
|
|
"created_on" bigint,
|
|
"updated_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "incomes" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"user_id" text,
|
|
"source" text,
|
|
"amount" double precision,
|
|
"frequency" text,
|
|
"received_date" bigint,
|
|
"encrypted_data" text,
|
|
"created_on" bigint,
|
|
"updated_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "lease_documents" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"lease_id" text,
|
|
"document_type" text,
|
|
"file_url" text,
|
|
"file_name" text,
|
|
"signed_by_landlord" boolean DEFAULT false,
|
|
"signed_by_tenant" boolean DEFAULT false,
|
|
"signature_date" bigint,
|
|
"created_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "leases" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"title" text,
|
|
"property_id" text,
|
|
"property_address" text,
|
|
"tenant_id" text,
|
|
"owner_id" text,
|
|
"start_date" bigint,
|
|
"end_date" bigint,
|
|
"rent" double precision,
|
|
"security_deposit" double precision,
|
|
"default_payment_method_id" text,
|
|
"lease_status" text,
|
|
"lease_document_url" text,
|
|
"payment_schedule_id" text,
|
|
"late_fee_rules" text,
|
|
"auto_renewal" boolean DEFAULT false,
|
|
"approval_status" text DEFAULT 'pending_approval',
|
|
"approval_requested_at" bigint,
|
|
"approved_at" bigint,
|
|
"approved_by" text,
|
|
"rejection_reason" text,
|
|
"encrypted_data" text,
|
|
"created_on" bigint,
|
|
"updated_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "maintenance_requests" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"property_id" text,
|
|
"tenant_id" text,
|
|
"request_type" text,
|
|
"description" text,
|
|
"status" text,
|
|
"priority" text,
|
|
"created_on" bigint,
|
|
"resolved_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "messages" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"lease_id" text,
|
|
"sender_id" text,
|
|
"recipient_id" text,
|
|
"message_type" text,
|
|
"subject" text,
|
|
"parent_message_id" text,
|
|
"attachments_json" json,
|
|
"content" text,
|
|
"content_encrypted" text,
|
|
"is_read" boolean DEFAULT false,
|
|
"created_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "obligations" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"user_id" text,
|
|
"description" text,
|
|
"amount" double precision,
|
|
"frequency" text,
|
|
"due_date" bigint,
|
|
"encrypted_data" text,
|
|
"created_on" bigint,
|
|
"updated_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "payment_methods" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"user_id" text,
|
|
"method_type" text,
|
|
"bank_name" text,
|
|
"account_type" text,
|
|
"routing_number_encrypted" text,
|
|
"account_number_encrypted" text,
|
|
"is_default" boolean DEFAULT false,
|
|
"created_on" bigint,
|
|
"updated_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "payment_transactions" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"lease_id" text,
|
|
"amount" double precision,
|
|
"payment_date" bigint,
|
|
"payment_method" text,
|
|
"transaction_status" text,
|
|
"stripe_payment_id" text,
|
|
"created_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "properties" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"title" text,
|
|
"description" text,
|
|
"address_1" text,
|
|
"address_2" text,
|
|
"city" text,
|
|
"state" text,
|
|
"zip" text,
|
|
"country" text,
|
|
"bedrooms" integer,
|
|
"bathrooms" integer,
|
|
"square_footage" integer,
|
|
"lot_size" integer,
|
|
"year_built" integer,
|
|
"price" double precision,
|
|
"property_type" text,
|
|
"listing_status" text,
|
|
"rental_status" text,
|
|
"monthly_rent" double precision,
|
|
"security_deposit" double precision,
|
|
"default_payment_method_id" text,
|
|
"lease_duration_months" integer,
|
|
"amenities" text,
|
|
"property_photos_count" integer DEFAULT 0,
|
|
"image_url" text,
|
|
"encrypted_data" text,
|
|
"created_on" bigint,
|
|
"updated_on" bigint,
|
|
"owner_id" text
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "property_media" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"property_id" text,
|
|
"media_type" text,
|
|
"file_url" text,
|
|
"file_name" text,
|
|
"display_order" integer DEFAULT 0,
|
|
"created_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "reminders" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"user_id" text NOT NULL,
|
|
"lease_id" text,
|
|
"reminder_type" text NOT NULL,
|
|
"title" text NOT NULL,
|
|
"description" text,
|
|
"due_date" bigint NOT NULL,
|
|
"is_completed" boolean DEFAULT false,
|
|
"created_on" bigint,
|
|
"updated_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "rent_schedules" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"property_id" text,
|
|
"rent_amount" double precision,
|
|
"due_day" integer,
|
|
"late_fee_amount" double precision,
|
|
"grace_period_days" integer DEFAULT 0,
|
|
"payment_methods_allowed" text,
|
|
"created_on" bigint,
|
|
"updated_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "roles" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"description" text
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "session_configs" (
|
|
"device_id" text PRIMARY KEY NOT NULL,
|
|
"user_id" text,
|
|
"other_data" text,
|
|
"active_date" bigint,
|
|
"active_route" text,
|
|
"page_data" text
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "state_keys" (
|
|
"seal_key" varchar(32) PRIMARY KEY NOT NULL,
|
|
"state_id" varchar(36) NOT NULL,
|
|
"cookie_hash" varchar(64) NOT NULL,
|
|
"created_on" bigint NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "tenant_applications" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"property_id" text,
|
|
"applicant_id" text,
|
|
"application_status" text,
|
|
"application_data_encrypted" text,
|
|
"document_urls_json" json,
|
|
"submitted_on" bigint,
|
|
"reviewed_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "tenant_invites" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"landlord_id" text,
|
|
"property_id" text,
|
|
"lease_id" text,
|
|
"available_on" bigint,
|
|
"invited_emails" text,
|
|
"created_on" bigint,
|
|
"updated_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "user_roles" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"user_id" text,
|
|
"role_id" text,
|
|
"created_on" bigint,
|
|
"updated_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "user_tokens" (
|
|
"user_id" text NOT NULL,
|
|
"state_id" text NOT NULL,
|
|
"access_token" text NOT NULL,
|
|
"refresh_token" text NOT NULL,
|
|
"created_on" bigint,
|
|
"updated_on" bigint,
|
|
CONSTRAINT "user_tokens_user_id_state_id_pk" PRIMARY KEY("user_id","state_id")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "user_pins" (
|
|
"user_id" text PRIMARY KEY NOT NULL,
|
|
"pin" text NOT NULL,
|
|
"created_on" bigint NOT NULL,
|
|
"modified_on" bigint NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "users" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"name" text,
|
|
"email" text,
|
|
"phone" text,
|
|
"address_1" text,
|
|
"address_2" text,
|
|
"city" text,
|
|
"state" text,
|
|
"zip" text,
|
|
"country" text,
|
|
"user_type" text,
|
|
"onboarded_landlord" boolean DEFAULT false,
|
|
"onboarded_tenant" boolean DEFAULT false,
|
|
"email_verified" boolean DEFAULT false,
|
|
"banking_setup_complete" boolean DEFAULT false,
|
|
"notification_preferences_json" json,
|
|
"payment_preferences_json" json,
|
|
"onboarding_step" integer DEFAULT 0,
|
|
"encrypted_data" text,
|
|
"started_on" bigint,
|
|
"updated_on" bigint
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "web_tokens" (
|
|
"token_id" varchar(64) PRIMARY KEY NOT NULL,
|
|
"state_id" varchar(36) NOT NULL,
|
|
"user_id" text NOT NULL,
|
|
"token_type" text NOT NULL,
|
|
"token_value" text NOT NULL,
|
|
"expires_on" bigint NOT NULL,
|
|
"created_on" bigint NOT NULL,
|
|
"updated_on" bigint NOT NULL,
|
|
"last_used_on" bigint,
|
|
"is_active" boolean DEFAULT true,
|
|
"metadata" json
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "financial_balances" ADD CONSTRAINT "financial_balances_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "incomes" ADD CONSTRAINT "incomes_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "lease_documents" ADD CONSTRAINT "lease_documents_lease_id_leases_id_fk" FOREIGN KEY ("lease_id") REFERENCES "public"."leases"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "leases" ADD CONSTRAINT "leases_property_id_properties_id_fk" FOREIGN KEY ("property_id") REFERENCES "public"."properties"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "leases" ADD CONSTRAINT "leases_tenant_id_users_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "leases" ADD CONSTRAINT "leases_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "leases" ADD CONSTRAINT "leases_default_payment_method_id_payment_methods_id_fk" FOREIGN KEY ("default_payment_method_id") REFERENCES "public"."payment_methods"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "leases" ADD CONSTRAINT "leases_payment_schedule_id_rent_schedules_id_fk" FOREIGN KEY ("payment_schedule_id") REFERENCES "public"."rent_schedules"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "leases" ADD CONSTRAINT "leases_approved_by_users_id_fk" FOREIGN KEY ("approved_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "maintenance_requests" ADD CONSTRAINT "maintenance_requests_property_id_properties_id_fk" FOREIGN KEY ("property_id") REFERENCES "public"."properties"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "maintenance_requests" ADD CONSTRAINT "maintenance_requests_tenant_id_users_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "messages" ADD CONSTRAINT "messages_lease_id_leases_id_fk" FOREIGN KEY ("lease_id") REFERENCES "public"."leases"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "messages" ADD CONSTRAINT "messages_sender_id_users_id_fk" FOREIGN KEY ("sender_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "messages" ADD CONSTRAINT "messages_recipient_id_users_id_fk" FOREIGN KEY ("recipient_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "obligations" ADD CONSTRAINT "obligations_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "payment_methods" ADD CONSTRAINT "payment_methods_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "payment_transactions" ADD CONSTRAINT "payment_transactions_lease_id_leases_id_fk" FOREIGN KEY ("lease_id") REFERENCES "public"."leases"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "properties" ADD CONSTRAINT "properties_default_payment_method_id_payment_methods_id_fk" FOREIGN KEY ("default_payment_method_id") REFERENCES "public"."payment_methods"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "properties" ADD CONSTRAINT "properties_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "property_media" ADD CONSTRAINT "property_media_property_id_properties_id_fk" FOREIGN KEY ("property_id") REFERENCES "public"."properties"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "reminders" ADD CONSTRAINT "reminders_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "reminders" ADD CONSTRAINT "reminders_lease_id_leases_id_fk" FOREIGN KEY ("lease_id") REFERENCES "public"."leases"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "rent_schedules" ADD CONSTRAINT "rent_schedules_property_id_properties_id_fk" FOREIGN KEY ("property_id") REFERENCES "public"."properties"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "session_configs" ADD CONSTRAINT "session_configs_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "tenant_applications" ADD CONSTRAINT "tenant_applications_property_id_properties_id_fk" FOREIGN KEY ("property_id") REFERENCES "public"."properties"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "tenant_applications" ADD CONSTRAINT "tenant_applications_applicant_id_users_id_fk" FOREIGN KEY ("applicant_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "tenant_invites" ADD CONSTRAINT "tenant_invites_landlord_id_users_id_fk" FOREIGN KEY ("landlord_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "tenant_invites" ADD CONSTRAINT "tenant_invites_property_id_properties_id_fk" FOREIGN KEY ("property_id") REFERENCES "public"."properties"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "tenant_invites" ADD CONSTRAINT "tenant_invites_lease_id_leases_id_fk" FOREIGN KEY ("lease_id") REFERENCES "public"."leases"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_role_id_roles_id_fk" FOREIGN KEY ("role_id") REFERENCES "public"."roles"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "user_tokens" ADD CONSTRAINT "user_tokens_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "user_pins" ADD CONSTRAINT "user_pins_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "web_tokens" ADD CONSTRAINT "web_tokens_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action; |