TypeScript 50.6%
JavaScript 46.1%
SQL 2.1%
Shell 0.6%
CSS 0.5%
1CREATE TABLE "audit_events" (2 "id" bigserial PRIMARY KEY NOT NULL,3 "actor_id" uuid,4 "action" text NOT NULL,5 "object_type" text,6 "object_id" text,7 "ip_hash" text,8 "metadata_redacted" jsonb DEFAULT '{}'::jsonb NOT NULL,9 "created_at" timestamp with time zone DEFAULT now() NOT NULL10);11--> statement-breakpoint12CREATE TABLE "email_deliveries" (13 "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,14 "to_hash" text NOT NULL,15 "template" text NOT NULL,16 "provider_id" text,17 "status" text NOT NULL,18 "error" text,19 "created_at" timestamp with time zone DEFAULT now() NOT NULL20);21--> statement-breakpoint22CREATE TABLE "favorites" (23 "user_id" uuid NOT NULL,24 "listing_uid" text NOT NULL,25 "created_at" timestamp with time zone DEFAULT now() NOT NULL,26 CONSTRAINT "favorites_user_id_listing_uid_pk" PRIMARY KEY("user_id","listing_uid")27);28--> statement-breakpoint29CREATE TABLE "inquiries" (30 "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,31 "listing_id" uuid NOT NULL,32 "sender_user_id" uuid NOT NULL,33 "recipient_user_id" uuid NOT NULL,34 "category" text DEFAULT 'INFO' NOT NULL,35 "status" text DEFAULT 'OPEN' NOT NULL,36 "last_message_at" timestamp with time zone DEFAULT now() NOT NULL,37 "created_at" timestamp with time zone DEFAULT now() NOT NULL38);39--> statement-breakpoint40CREATE TABLE "listing_claims" (41 "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,42 "aggregated_uid" text NOT NULL,43 "requester_id" uuid NOT NULL,44 "status" text DEFAULT 'PENDING' NOT NULL,45 "message" text,46 "proof_keys" jsonb DEFAULT '[]'::jsonb NOT NULL,47 "reviewed_by" uuid,48 "reviewed_at" timestamp with time zone,49 "created_at" timestamp with time zone DEFAULT now() NOT NULL50);51--> statement-breakpoint52CREATE TABLE "listing_media" (53 "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,54 "listing_id" uuid NOT NULL,55 "storage_key" text NOT NULL,56 "media_type" text DEFAULT 'IMAGE' NOT NULL,57 "mime_type" text,58 "position" integer DEFAULT 0 NOT NULL,59 "alt_text" text,60 "license_status" text DEFAULT 'OWNER_DECLARED' NOT NULL,61 "scan_status" text DEFAULT 'PENDING' NOT NULL,62 "width" integer,63 "height" integer,64 "bytes" integer,65 "status" text DEFAULT 'UPLOADING' NOT NULL,66 "upload_token" text,67 "uploaded_at" timestamp with time zone DEFAULT now() NOT NULL68);69--> statement-breakpoint70CREATE TABLE "listing_versions" (71 "id" bigserial PRIMARY KEY NOT NULL,72 "listing_id" uuid NOT NULL,73 "version" integer NOT NULL,74 "actor_id" uuid,75 "snapshot" jsonb NOT NULL,76 "created_at" timestamp with time zone DEFAULT now() NOT NULL77);78--> statement-breakpoint79CREATE TABLE "listings" (80 "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,81 "slug" text NOT NULL,82 "property_id" uuid NOT NULL,83 "owner_user_id" uuid,84 "listing_type" text NOT NULL,85 "source_type" text DEFAULT 'DIRECT_OWNER' NOT NULL,86 "status" text DEFAULT 'DRAFT' NOT NULL,87 "title" text,88 "description" text,89 "price_amount" numeric(12, 2),90 "currency" text DEFAULT 'CAD' NOT NULL,91 "rental_period" text,92 "availability_date" text,93 "furnished" boolean,94 "pets_policy" text,95 "smoking_policy" text,96 "parking" text,97 "lease_terms" text,98 "inclusions" jsonb DEFAULT '[]'::jsonb NOT NULL,99 "features" jsonb DEFAULT '{}'::jsonb NOT NULL,100 "publication_at" timestamp with time zone,101 "expires_at" timestamp with time zone,102 "moderation_state" text DEFAULT 'NONE' NOT NULL,103 "moderation_note" text,104 "external_url" text,105 "upstream_source" text,106 "upstream_id" text,107 "upstream_sync_at" timestamp with time zone,108 "wizard_step" integer DEFAULT 0 NOT NULL,109 "attested_at" timestamp with time zone,110 "version" integer DEFAULT 1 NOT NULL,111 "views_count" integer DEFAULT 0 NOT NULL,112 "created_at" timestamp with time zone DEFAULT now() NOT NULL,113 "updated_at" timestamp with time zone DEFAULT now() NOT NULL,114 CONSTRAINT "listings_type_ck" CHECK ("listings"."listing_type" in ('SALE','RENT')),115 CONSTRAINT "listings_source_ck" CHECK ("listings"."source_type" in ('DIRECT_OWNER','DIRECT_AGENT','AGGREGATED_PARTNER')),116 CONSTRAINT "listings_status_ck" CHECK ("listings"."status" in ('DRAFT','PENDING_REVIEW','PUBLISHED','PAUSED','REJECTED','ARCHIVED','SOLD','RENTED')),117 CONSTRAINT "listings_price_ck" CHECK ("listings"."price_amount" is null or "listings"."price_amount" >= 0),118 CONSTRAINT "listings_owner_direct_ck" CHECK ("listings"."source_type" = 'AGGREGATED_PARTNER' or "listings"."owner_user_id" is not null),119 CONSTRAINT "listings_rent_period_ck" CHECK ("listings"."listing_type" <> 'RENT' or "listings"."rental_period" is null or "listings"."rental_period" in ('MONTH','WEEK'))120);121--> statement-breakpoint122CREATE TABLE "messages" (123 "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,124 "inquiry_id" uuid NOT NULL,125 "sender_id" uuid NOT NULL,126 "body" text NOT NULL,127 "idempotency_key" text,128 "created_at" timestamp with time zone DEFAULT now() NOT NULL,129 "read_at" timestamp with time zone130);131--> statement-breakpoint132CREATE TABLE "moderation_actions" (133 "id" bigserial PRIMARY KEY NOT NULL,134 "actor_id" uuid NOT NULL,135 "target_type" text NOT NULL,136 "target_id" text NOT NULL,137 "action" text NOT NULL,138 "note" text,139 "created_at" timestamp with time zone DEFAULT now() NOT NULL140);141--> statement-breakpoint142CREATE TABLE "otp_challenges" (143 "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,144 "email_normalized" text NOT NULL,145 "purpose" text DEFAULT 'LOGIN' NOT NULL,146 "secret_digest" text NOT NULL,147 "expires_at" timestamp with time zone NOT NULL,148 "attempts" integer DEFAULT 0 NOT NULL,149 "consumed_at" timestamp with time zone,150 "requested_ip_hash" text,151 "meta" jsonb DEFAULT '{}'::jsonb NOT NULL,152 "created_at" timestamp with time zone DEFAULT now() NOT NULL153);154--> statement-breakpoint155CREATE TABLE "properties" (156 "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,157 "canonical_address" text,158 "unit" text,159 "city" text,160 "region" text,161 "postal_code" text,162 "lat" double precision,163 "lng" double precision,164 "geographic_precision" text DEFAULT 'EXACT' NOT NULL,165 "geo_source" text,166 "role_unit_id" text,167 "property_type" text NOT NULL,168 "bedrooms" numeric(4, 1),169 "bathrooms" numeric(4, 1),170 "area_sqft" integer,171 "lot_sqft" integer,172 "year_built" integer,173 "privacy_level" text DEFAULT 'EXACT' NOT NULL,174 "created_at" timestamp with time zone DEFAULT now() NOT NULL175);176--> statement-breakpoint177CREATE TABLE "rate_limits" (178 "key" text NOT NULL,179 "window_start" timestamp with time zone NOT NULL,180 "count" integer DEFAULT 0 NOT NULL,181 CONSTRAINT "rate_limits_key_window_start_pk" PRIMARY KEY("key","window_start")182);183--> statement-breakpoint184CREATE TABLE "reports" (185 "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,186 "target_type" text NOT NULL,187 "target_id" text NOT NULL,188 "reporter_id" uuid,189 "reason" text NOT NULL,190 "details" text,191 "status" text DEFAULT 'OPEN' NOT NULL,192 "created_at" timestamp with time zone DEFAULT now() NOT NULL193);194--> statement-breakpoint195CREATE TABLE "saved_searches" (196 "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,197 "user_id" uuid NOT NULL,198 "label" text NOT NULL,199 "query" jsonb NOT NULL,200 "alert_enabled" boolean DEFAULT false NOT NULL,201 "alert_frequency" text DEFAULT 'DAILY' NOT NULL,202 "last_alert_at" timestamp with time zone,203 "created_at" timestamp with time zone DEFAULT now() NOT NULL204);205--> statement-breakpoint206CREATE TABLE "sessions" (207 "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,208 "user_id" uuid NOT NULL,209 "token_digest" text NOT NULL,210 "issued_at" timestamp with time zone DEFAULT now() NOT NULL,211 "last_seen_at" timestamp with time zone DEFAULT now() NOT NULL,212 "expires_at" timestamp with time zone NOT NULL,213 "revoked_at" timestamp with time zone,214 "device_label" text,215 "ip_hash" text,216 "mfa_at" timestamp with time zone217);218--> statement-breakpoint219CREATE TABLE "user_profiles" (220 "user_id" uuid PRIMARY KEY NOT NULL,221 "public_name" text,222 "bio" text,223 "avatar_key" text,224 "seller_type" text DEFAULT 'OWNER' NOT NULL,225 "verification_status" text DEFAULT 'NONE' NOT NULL,226 "contact_preferences" jsonb DEFAULT '{"email":true,"phone":false}'::jsonb NOT NULL,227 "updated_at" timestamp with time zone DEFAULT now() NOT NULL228);229--> statement-breakpoint230CREATE TABLE "user_roles" (231 "user_id" uuid NOT NULL,232 "role" text NOT NULL,233 "granted_by" uuid,234 "granted_at" timestamp with time zone DEFAULT now() NOT NULL,235 "note" text,236 CONSTRAINT "user_roles_user_id_role_pk" PRIMARY KEY("user_id","role"),237 CONSTRAINT "user_roles_role_ck" CHECK ("user_roles"."role" in ('ADMIN','MODERATOR','AGENT_VERIFIED'))238);239--> statement-breakpoint240CREATE TABLE "users" (241 "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,242 "email" text NOT NULL,243 "email_verified_at" timestamp with time zone,244 "display_name" text,245 "phone_encrypted" text,246 "locale" text DEFAULT 'fr-CA' NOT NULL,247 "status" text DEFAULT 'ACTIVE' NOT NULL,248 "prefs" jsonb DEFAULT '{}'::jsonb NOT NULL,249 "totp_secret_encrypted" text,250 "created_at" timestamp with time zone DEFAULT now() NOT NULL,251 "updated_at" timestamp with time zone DEFAULT now() NOT NULL,252 "deleted_at" timestamp with time zone,253 CONSTRAINT "users_status_ck" CHECK ("users"."status" in ('ACTIVE','SUSPENDED','DELETED'))254);255--> statement-breakpoint256CREATE TABLE "viewing_requests" (257 "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,258 "listing_id" uuid NOT NULL,259 "inquiry_id" uuid,260 "requester_id" uuid NOT NULL,261 "owner_id" uuid NOT NULL,262 "proposed_slots" jsonb NOT NULL,263 "confirmed_slot" timestamp with time zone,264 "status" text DEFAULT 'REQUESTED' NOT NULL,265 "note" text,266 "created_at" timestamp with time zone DEFAULT now() NOT NULL,267 "updated_at" timestamp with time zone DEFAULT now() NOT NULL,268 CONSTRAINT "viewings_status_ck" CHECK ("viewing_requests"."status" in ('REQUESTED','CONFIRMED','CANCELLED','DONE'))269);270--> statement-breakpoint271ALTER TABLE "favorites" ADD CONSTRAINT "favorites_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint272ALTER TABLE "inquiries" ADD CONSTRAINT "inquiries_listing_id_listings_id_fk" FOREIGN KEY ("listing_id") REFERENCES "public"."listings"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint273ALTER TABLE "inquiries" ADD CONSTRAINT "inquiries_sender_user_id_users_id_fk" FOREIGN KEY ("sender_user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint274ALTER TABLE "inquiries" ADD CONSTRAINT "inquiries_recipient_user_id_users_id_fk" FOREIGN KEY ("recipient_user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint275ALTER TABLE "listing_claims" ADD CONSTRAINT "listing_claims_requester_id_users_id_fk" FOREIGN KEY ("requester_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint276ALTER TABLE "listing_media" ADD CONSTRAINT "listing_media_listing_id_listings_id_fk" FOREIGN KEY ("listing_id") REFERENCES "public"."listings"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint277ALTER TABLE "listing_versions" ADD CONSTRAINT "listing_versions_listing_id_listings_id_fk" FOREIGN KEY ("listing_id") REFERENCES "public"."listings"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint278ALTER TABLE "listings" ADD CONSTRAINT "listings_property_id_properties_id_fk" FOREIGN KEY ("property_id") REFERENCES "public"."properties"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint279ALTER TABLE "listings" ADD CONSTRAINT "listings_owner_user_id_users_id_fk" FOREIGN KEY ("owner_user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint280ALTER TABLE "messages" ADD CONSTRAINT "messages_inquiry_id_inquiries_id_fk" FOREIGN KEY ("inquiry_id") REFERENCES "public"."inquiries"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint281ALTER TABLE "messages" ADD CONSTRAINT "messages_sender_id_users_id_fk" FOREIGN KEY ("sender_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint282ALTER TABLE "reports" ADD CONSTRAINT "reports_reporter_id_users_id_fk" FOREIGN KEY ("reporter_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint283ALTER TABLE "saved_searches" ADD CONSTRAINT "saved_searches_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint284ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint285ALTER TABLE "user_profiles" ADD CONSTRAINT "user_profiles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint286ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint287ALTER TABLE "viewing_requests" ADD CONSTRAINT "viewing_requests_listing_id_listings_id_fk" FOREIGN KEY ("listing_id") REFERENCES "public"."listings"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint288ALTER TABLE "viewing_requests" ADD CONSTRAINT "viewing_requests_inquiry_id_inquiries_id_fk" FOREIGN KEY ("inquiry_id") REFERENCES "public"."inquiries"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint289ALTER TABLE "viewing_requests" ADD CONSTRAINT "viewing_requests_requester_id_users_id_fk" FOREIGN KEY ("requester_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint290ALTER TABLE "viewing_requests" ADD CONSTRAINT "viewing_requests_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint291CREATE INDEX "audit_created_idx" ON "audit_events" USING btree ("created_at");--> statement-breakpoint292CREATE INDEX "audit_actor_idx" ON "audit_events" USING btree ("actor_id");--> statement-breakpoint293CREATE INDEX "email_deliveries_created_idx" ON "email_deliveries" USING btree ("created_at");--> statement-breakpoint294CREATE INDEX "inquiries_listing_idx" ON "inquiries" USING btree ("listing_id");--> statement-breakpoint295CREATE INDEX "inquiries_sender_idx" ON "inquiries" USING btree ("sender_user_id");--> statement-breakpoint296CREATE INDEX "inquiries_recipient_idx" ON "inquiries" USING btree ("recipient_user_id");--> statement-breakpoint297CREATE UNIQUE INDEX "inquiries_unique_thread" ON "inquiries" USING btree ("listing_id","sender_user_id");--> statement-breakpoint298CREATE INDEX "listing_claims_uid_idx" ON "listing_claims" USING btree ("aggregated_uid");--> statement-breakpoint299CREATE INDEX "listing_media_listing_idx" ON "listing_media" USING btree ("listing_id","position");--> statement-breakpoint300CREATE INDEX "listing_versions_listing_idx" ON "listing_versions" USING btree ("listing_id","version");--> statement-breakpoint301CREATE UNIQUE INDEX "listings_slug_uq" ON "listings" USING btree ("slug");--> statement-breakpoint302CREATE INDEX "listings_status_idx" ON "listings" USING btree ("status","listing_type","publication_at");--> statement-breakpoint303CREATE INDEX "listings_owner_idx" ON "listings" USING btree ("owner_user_id");--> statement-breakpoint304CREATE UNIQUE INDEX "listings_upstream_uq" ON "listings" USING btree ("upstream_source","upstream_id");--> statement-breakpoint305CREATE INDEX "messages_inquiry_idx" ON "messages" USING btree ("inquiry_id","created_at");--> statement-breakpoint306CREATE UNIQUE INDEX "messages_idem_uq" ON "messages" USING btree ("inquiry_id","idempotency_key");--> statement-breakpoint307CREATE INDEX "otp_email_idx" ON "otp_challenges" USING btree ("email_normalized","created_at");--> statement-breakpoint308CREATE INDEX "otp_expires_idx" ON "otp_challenges" USING btree ("expires_at");--> statement-breakpoint309CREATE INDEX "properties_geo_idx" ON "properties" USING btree ("lat","lng");--> statement-breakpoint310CREATE INDEX "properties_city_idx" ON "properties" USING btree ("city");--> statement-breakpoint311CREATE INDEX "reports_status_idx" ON "reports" USING btree ("status","created_at");--> statement-breakpoint312CREATE INDEX "saved_searches_user_idx" ON "saved_searches" USING btree ("user_id");--> statement-breakpoint313CREATE UNIQUE INDEX "sessions_token_uq" ON "sessions" USING btree ("token_digest");--> statement-breakpoint314CREATE INDEX "sessions_user_idx" ON "sessions" USING btree ("user_id");--> statement-breakpoint315CREATE UNIQUE INDEX "users_email_uq" ON "users" USING btree ("email");--> statement-breakpoint316CREATE INDEX "viewings_listing_idx" ON "viewing_requests" USING btree ("listing_id");--> statement-breakpoint317CREATE INDEX "viewings_requester_idx" ON "viewing_requests" USING btree ("requester_id");--> statement-breakpoint318CREATE INDEX "viewings_owner_idx" ON "viewing_requests" USING btree ("owner_id");