TypeScript 50.6%
JavaScript 46.1%
SQL 2.1%
Shell 0.6%
CSS 0.5%
1// Toveli — schéma PostgreSQL (identité, publications directes, échanges). Drizzle ORM.2// Les annonces agrégées restent dans la base canonique SQLite (lecture seule) ; ici ne vivent que les données Toveli.3import { pgTable, uuid, text, timestamp, integer, boolean, jsonb, numeric, doublePrecision, index, uniqueIndex, primaryKey, check, bigserial } from "drizzle-orm/pg-core";4import { sql } from "drizzle-orm";56export const users = pgTable("users", {7 id: uuid("id").primaryKey().defaultRandom(),8 email: text("email").notNull(), // adresse normalisée (minuscules, espaces retirés — points et + conservés)9 emailVerifiedAt: timestamp("email_verified_at", { withTimezone: true }),10 displayName: text("display_name"),11 phoneEncrypted: text("phone_encrypted"),12 locale: text("locale").notNull().default("fr-CA"),13 status: text("status").notNull().default("ACTIVE"), // ACTIVE | SUSPENDED | DELETED14 prefs: jsonb("prefs").notNull().default(sql`'{}'::jsonb`), // notifications, confidentialité15 totpSecretEncrypted: text("totp_secret_encrypted"), // MFA forte requise pour les rôles élevés16 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),17 updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),18 deletedAt: timestamp("deleted_at", { withTimezone: true }),19}, (t) => [uniqueIndex("users_email_uq").on(t.email), check("users_status_ck", sql`${t.status} in ('ACTIVE','SUSPENDED','DELETED')`)]);2021export const userProfiles = pgTable("user_profiles", {22 userId: uuid("user_id").primaryKey().references(() => users.id, { onDelete: "cascade" }),23 publicName: text("public_name"),24 bio: text("bio"),25 avatarKey: text("avatar_key"),26 sellerType: text("seller_type").notNull().default("OWNER"), // OWNER | AGENT (déclaratif) — AGENT_VERIFIED est un rôle attribué côté serveur27 verificationStatus: text("verification_status").notNull().default("NONE"),28 contactPreferences: jsonb("contact_preferences").notNull().default(sql`'{"email":true,"phone":false}'::jsonb`),29 updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),30});3132export const otpChallenges = pgTable("otp_challenges", {33 id: uuid("id").primaryKey().defaultRandom(),34 emailNormalized: text("email_normalized").notNull(),35 purpose: text("purpose").notNull().default("LOGIN"), // LOGIN | EMAIL_CHANGE36 secretDigest: text("secret_digest").notNull(), // HMAC-SHA256(code, OTP_HMAC_SECRET, id) — jamais le code37 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),38 attempts: integer("attempts").notNull().default(0),39 consumedAt: timestamp("consumed_at", { withTimezone: true }),40 requestedIpHash: text("requested_ip_hash"),41 meta: jsonb("meta").notNull().default(sql`'{}'::jsonb`),42 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),43}, (t) => [index("otp_email_idx").on(t.emailNormalized, t.createdAt), index("otp_expires_idx").on(t.expiresAt)]);4445export const sessions = pgTable("sessions", {46 id: uuid("id").primaryKey().defaultRandom(),47 userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),48 tokenDigest: text("token_digest").notNull(), // sha256(token) — le jeton n'est jamais stocké en clair49 issuedAt: timestamp("issued_at", { withTimezone: true }).notNull().defaultNow(),50 lastSeenAt: timestamp("last_seen_at", { withTimezone: true }).notNull().defaultNow(),51 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),52 revokedAt: timestamp("revoked_at", { withTimezone: true }),53 deviceLabel: text("device_label"),54 ipHash: text("ip_hash"),55 mfaAt: timestamp("mfa_at", { withTimezone: true }), // dernière vérification TOTP (actions admin)56}, (t) => [uniqueIndex("sessions_token_uq").on(t.tokenDigest), index("sessions_user_idx").on(t.userId)]);5758export const userRoles = pgTable("user_roles", {59 userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),60 role: text("role").notNull(), // ADMIN | MODERATOR | AGENT_VERIFIED61 grantedBy: uuid("granted_by"),62 grantedAt: timestamp("granted_at", { withTimezone: true }).notNull().defaultNow(),63 note: text("note"),64}, (t) => [primaryKey({ columns: [t.userId, t.role] }), check("user_roles_role_ck", sql`${t.role} in ('ADMIN','MODERATOR','AGENT_VERIFIED')`)]);6566export const properties = pgTable("properties", {67 id: uuid("id").primaryKey().defaultRandom(),68 canonicalAddress: text("canonical_address"),69 unit: text("unit"),70 city: text("city"),71 region: text("region"),72 postalCode: text("postal_code"),73 lat: doublePrecision("lat"),74 lng: doublePrecision("lng"),75 geographicPrecision: text("geographic_precision").notNull().default("EXACT"), // EXACT | STREET | AREA76 geoSource: text("geo_source"), // role_unit:<id> | manual | geocoder77 roleUnitId: text("role_unit_id"),78 propertyType: text("property_type").notNull(), // appartement | condo | maison | jumele | plex | terrain | commercial | chalet | autre79 bedrooms: numeric("bedrooms", { precision: 4, scale: 1 }),80 bathrooms: numeric("bathrooms", { precision: 4, scale: 1 }),81 areaSqft: integer("area_sqft"),82 lotSqft: integer("lot_sqft"),83 yearBuilt: integer("year_built"),84 privacyLevel: text("privacy_level").notNull().default("EXACT"), // EXACT | STREET_ONLY | AREA_ONLY85 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),86}, (t) => [index("properties_geo_idx").on(t.lat, t.lng), index("properties_city_idx").on(t.city)]);8788export const listings = pgTable("listings", {89 id: uuid("id").primaryKey().defaultRandom(),90 slug: text("slug").notNull(),91 propertyId: uuid("property_id").notNull().references(() => properties.id, { onDelete: "restrict" }),92 ownerUserId: uuid("owner_user_id").references(() => users.id, { onDelete: "set null" }),93 listingType: text("listing_type").notNull(), // SALE | RENT94 sourceType: text("source_type").notNull().default("DIRECT_OWNER"), // DIRECT_OWNER | DIRECT_AGENT | AGGREGATED_PARTNER95 status: text("status").notNull().default("DRAFT"), // DRAFT | PENDING_REVIEW | PUBLISHED | PAUSED | REJECTED | ARCHIVED | SOLD | RENTED96 title: text("title"),97 description: text("description"),98 priceAmount: numeric("price_amount", { precision: 12, scale: 2 }),99 currency: text("currency").notNull().default("CAD"),100 rentalPeriod: text("rental_period"), // MONTH (location)101 availabilityDate: text("availability_date"), // YYYY-MM-DD ou 'now'102 furnished: boolean("furnished"),103 petsPolicy: text("pets_policy"), // allowed | not_allowed | ask104 smokingPolicy: text("smoking_policy"),105 parking: text("parking"),106 leaseTerms: text("lease_terms"),107 inclusions: jsonb("inclusions").notNull().default(sql`'[]'::jsonb`),108 features: jsonb("features").notNull().default(sql`'{}'::jsonb`), // champs extensibles par type109 publicationAt: timestamp("publication_at", { withTimezone: true }),110 expiresAt: timestamp("expires_at", { withTimezone: true }),111 moderationState: text("moderation_state").notNull().default("NONE"), // NONE | AUTO_OK | FLAGGED | HUMAN_OK | REJECTED112 moderationNote: text("moderation_note"),113 externalUrl: text("external_url"),114 upstreamSource: text("upstream_source"),115 upstreamId: text("upstream_id"),116 upstreamSyncAt: timestamp("upstream_sync_at", { withTimezone: true }),117 wizardStep: integer("wizard_step").notNull().default(0),118 attestedAt: timestamp("attested_at", { withTimezone: true }), // attestation du droit de publier119 version: integer("version").notNull().default(1), // contrôle de concurrence optimiste120 viewsCount: integer("views_count").notNull().default(0),121 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),122 updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),123}, (t) => [124 uniqueIndex("listings_slug_uq").on(t.slug),125 index("listings_status_idx").on(t.status, t.listingType, t.publicationAt),126 index("listings_owner_idx").on(t.ownerUserId),127 uniqueIndex("listings_upstream_uq").on(t.upstreamSource, t.upstreamId),128 check("listings_type_ck", sql`${t.listingType} in ('SALE','RENT')`),129 check("listings_source_ck", sql`${t.sourceType} in ('DIRECT_OWNER','DIRECT_AGENT','AGGREGATED_PARTNER')`),130 check("listings_status_ck", sql`${t.status} in ('DRAFT','PENDING_REVIEW','PUBLISHED','PAUSED','REJECTED','ARCHIVED','SOLD','RENTED')`),131 check("listings_price_ck", sql`${t.priceAmount} is null or ${t.priceAmount} >= 0`),132 check("listings_owner_direct_ck", sql`${t.sourceType} = 'AGGREGATED_PARTNER' or ${t.ownerUserId} is not null`),133 check("listings_rent_period_ck", sql`${t.listingType} <> 'RENT' or ${t.rentalPeriod} is null or ${t.rentalPeriod} in ('MONTH','WEEK')`),134]);135136export const listingMedia = pgTable("listing_media", {137 id: uuid("id").primaryKey().defaultRandom(),138 listingId: uuid("listing_id").notNull().references(() => listings.id, { onDelete: "cascade" }),139 storageKey: text("storage_key").notNull(),140 mediaType: text("media_type").notNull().default("IMAGE"),141 mimeType: text("mime_type"),142 position: integer("position").notNull().default(0),143 altText: text("alt_text"),144 licenseStatus: text("license_status").notNull().default("OWNER_DECLARED"),145 scanStatus: text("scan_status").notNull().default("PENDING"), // PENDING | CLEAN | REJECTED146 width: integer("width"), height: integer("height"), bytes: integer("bytes"),147 status: text("status").notNull().default("UPLOADING"), // UPLOADING | READY | DELETED148 uploadToken: text("upload_token"),149 uploadedAt: timestamp("uploaded_at", { withTimezone: true }).notNull().defaultNow(),150}, (t) => [index("listing_media_listing_idx").on(t.listingId, t.position)]);151152export const listingVersions = pgTable("listing_versions", {153 id: bigserial("id", { mode: "number" }).primaryKey(),154 listingId: uuid("listing_id").notNull().references(() => listings.id, { onDelete: "cascade" }),155 version: integer("version").notNull(),156 actorId: uuid("actor_id"),157 snapshot: jsonb("snapshot").notNull(),158 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),159}, (t) => [index("listing_versions_listing_idx").on(t.listingId, t.version)]);160161export const listingClaims = pgTable("listing_claims", {162 id: uuid("id").primaryKey().defaultRandom(),163 aggregatedUid: text("aggregated_uid").notNull(), // uid de l'annonce agrégée (base canonique)164 requesterId: uuid("requester_id").notNull().references(() => users.id, { onDelete: "cascade" }),165 status: text("status").notNull().default("PENDING"), // PENDING | APPROVED | REJECTED166 message: text("message"),167 proofKeys: jsonb("proof_keys").notNull().default(sql`'[]'::jsonb`),168 reviewedBy: uuid("reviewed_by"),169 reviewedAt: timestamp("reviewed_at", { withTimezone: true }),170 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),171}, (t) => [index("listing_claims_uid_idx").on(t.aggregatedUid)]);172173export const favorites = pgTable("favorites", {174 userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),175 listingUid: text("listing_uid").notNull(), // 'toveli:<id>' ou uid agrégé176 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),177}, (t) => [primaryKey({ columns: [t.userId, t.listingUid] })]);178179export const savedSearches = pgTable("saved_searches", {180 id: uuid("id").primaryKey().defaultRandom(),181 userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),182 label: text("label").notNull(),183 query: jsonb("query").notNull(),184 alertEnabled: boolean("alert_enabled").notNull().default(false),185 alertFrequency: text("alert_frequency").notNull().default("DAILY"), // INSTANT | DAILY | WEEKLY | OFF186 lastAlertAt: timestamp("last_alert_at", { withTimezone: true }),187 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),188}, (t) => [index("saved_searches_user_idx").on(t.userId)]);189190export const emailDeliveries = pgTable("email_deliveries", {191 id: uuid("id").primaryKey().defaultRandom(),192 toHash: text("to_hash").notNull(),193 template: text("template").notNull(),194 providerId: text("provider_id"),195 status: text("status").notNull(), // QUEUED | SENT | MOCKED | FAILED196 error: text("error"),197 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),198}, (t) => [index("email_deliveries_created_idx").on(t.createdAt)]);199200export const inquiries = pgTable("inquiries", {201 id: uuid("id").primaryKey().defaultRandom(),202 listingId: uuid("listing_id").notNull().references(() => listings.id, { onDelete: "cascade" }),203 senderUserId: uuid("sender_user_id").notNull().references(() => users.id, { onDelete: "cascade" }),204 recipientUserId: uuid("recipient_user_id").notNull().references(() => users.id, { onDelete: "cascade" }),205 category: text("category").notNull().default("INFO"), // INFO | VISIT | APPLICATION | OFFER206 status: text("status").notNull().default("OPEN"), // OPEN | ARCHIVED | BLOCKED207 lastMessageAt: timestamp("last_message_at", { withTimezone: true }).notNull().defaultNow(),208 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),209}, (t) => [index("inquiries_listing_idx").on(t.listingId), index("inquiries_sender_idx").on(t.senderUserId), index("inquiries_recipient_idx").on(t.recipientUserId), uniqueIndex("inquiries_unique_thread").on(t.listingId, t.senderUserId)]);210211export const messages = pgTable("messages", {212 id: uuid("id").primaryKey().defaultRandom(),213 inquiryId: uuid("inquiry_id").notNull().references(() => inquiries.id, { onDelete: "cascade" }),214 senderId: uuid("sender_id").notNull().references(() => users.id, { onDelete: "cascade" }),215 body: text("body").notNull(),216 idempotencyKey: text("idempotency_key"),217 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),218 readAt: timestamp("read_at", { withTimezone: true }),219}, (t) => [index("messages_inquiry_idx").on(t.inquiryId, t.createdAt), uniqueIndex("messages_idem_uq").on(t.inquiryId, t.idempotencyKey)]);220221export const viewingRequests = pgTable("viewing_requests", {222 id: uuid("id").primaryKey().defaultRandom(),223 listingId: uuid("listing_id").notNull().references(() => listings.id, { onDelete: "cascade" }),224 inquiryId: uuid("inquiry_id").references(() => inquiries.id, { onDelete: "set null" }),225 requesterId: uuid("requester_id").notNull().references(() => users.id, { onDelete: "cascade" }),226 ownerId: uuid("owner_id").notNull().references(() => users.id, { onDelete: "cascade" }),227 proposedSlots: jsonb("proposed_slots").notNull(), // [ISO America/Toronto]228 confirmedSlot: timestamp("confirmed_slot", { withTimezone: true }),229 status: text("status").notNull().default("REQUESTED"), // REQUESTED | CONFIRMED | CANCELLED | DONE230 note: text("note"),231 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),232 updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),233}, (t) => [index("viewings_listing_idx").on(t.listingId), index("viewings_requester_idx").on(t.requesterId), index("viewings_owner_idx").on(t.ownerId), check("viewings_status_ck", sql`${t.status} in ('REQUESTED','CONFIRMED','CANCELLED','DONE')`)]);234235export const reports = pgTable("reports", {236 id: uuid("id").primaryKey().defaultRandom(),237 targetType: text("target_type").notNull(), // LISTING | USER | MESSAGE | AGGREGATED_LISTING238 targetId: text("target_id").notNull(),239 reporterId: uuid("reporter_id").references(() => users.id, { onDelete: "set null" }),240 reason: text("reason").notNull(),241 details: text("details"),242 status: text("status").notNull().default("OPEN"), // OPEN | RESOLVED | DISMISSED243 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),244}, (t) => [index("reports_status_idx").on(t.status, t.createdAt)]);245246export const moderationActions = pgTable("moderation_actions", {247 id: bigserial("id", { mode: "number" }).primaryKey(),248 actorId: uuid("actor_id").notNull(),249 targetType: text("target_type").notNull(),250 targetId: text("target_id").notNull(),251 action: text("action").notNull(),252 note: text("note"),253 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),254});255256export const auditEvents = pgTable("audit_events", {257 id: bigserial("id", { mode: "number" }).primaryKey(),258 actorId: uuid("actor_id"),259 action: text("action").notNull(),260 objectType: text("object_type"),261 objectId: text("object_id"),262 ipHash: text("ip_hash"),263 metadataRedacted: jsonb("metadata_redacted").notNull().default(sql`'{}'::jsonb`),264 createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),265}, (t) => [index("audit_created_idx").on(t.createdAt), index("audit_actor_idx").on(t.actorId)]);266267export const rateLimits = pgTable("rate_limits", {268 key: text("key").notNull(), // ex. otp:ip:<hash> | otp:email:<hash>269 windowStart: timestamp("window_start", { withTimezone: true }).notNull(),270 count: integer("count").notNull().default(0),271}, (t) => [primaryKey({ columns: [t.key, t.windowStart] })]);272