Property & rooms

Building, floor, room hierarchy. Pricing config, photo gallery, occupancy tracking. v1's 33 BO files in Property domain — collapses to ~10 tables.

Phase 1

Scope

In

Out / deferred

Data model

// packages/db/src/schema/properties.ts
export const properties = pgTable("properties", {
  id: uuid().primaryKey().defaultRandom(),
  ownerId: uuid("owner_id").notNull().references(() => propertyOwners.id),
  name: text().notNull(),                          // "Гранд тауэр", "ХУД-1 байр"
  address: text(),
  district: text(),                                // ref or text (ref recommended — see Open Q)
  city: text().notNull().default("Улаанбаатар"),
  totalArea: numeric("total_area", { precision: 12, scale: 2 }),
  floorCount: integer("floor_count"),
  builtYear: integer("built_year"),
  coverImageUrl: text("cover_image_url"),          // R2 url
  status: text({ enum: ["active","under_construction","decommissioned"] }).notNull().default("active"),
  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp({ withTimezone: true }).notNull().defaultNow().$onUpdate(() => new Date()),
}, (t) => [
  index("prop_owner_idx").on(t.ownerId),
  pgPolicy("prop_owner_scope", {
    as: "permissive", for: "all", to: "authenticated",
    using: sql`owner_id::text = (select current_setting('app.owner_id', true))
               or current_setting('app.role', true) = 'admin'`,
  }),
]);

// packages/db/src/schema/floors.ts
export const floors = pgTable("floors", {
  id: uuid().primaryKey().defaultRandom(),
  propertyId: uuid("property_id").notNull().references(() => properties.id, { onDelete: "cascade" }),
  ownerId: uuid("owner_id").notNull(),             // denormalized for RLS — avoids join
  number: integer().notNull(),                     // 1, 2, ..., -1 (basement)
  label: text(),                                   // "G", "M", "2A"
  planImageUrl: text("plan_image_url"),
}, (t) => [
  unique().on(t.propertyId, t.number),
  index("floor_owner_idx").on(t.ownerId),
  pgPolicy("floor_owner_scope", { /* same shape */ }),
]);

// packages/db/src/schema/rooms.ts
export const rooms = pgTable("rooms", {
  id: uuid().primaryKey().defaultRandom(),
  propertyId: uuid("property_id").notNull().references(() => properties.id),
  floorId: uuid("floor_id").references(() => floors.id),
  ownerId: uuid("owner_id").notNull(),
  unitNumber: text("unit_number").notNull(),       // "101", "201A"
  kind: text({ enum: ["studio","1br","2br","3br","office","shop","storage","parking"] }).notNull(),
  area: numeric({ precision: 10, scale: 2 }),       // m²
  baseRent: bigint("base_rent", { mode: "bigint" }), // minor units (mungo for MNT)
  depositMultiplier: numeric({ precision: 4, scale: 2 }).default("1.00"),
  features: jsonb().notNull().default(sql`'{}'::jsonb`),  // { balcony, furnished, view, etc. }
  status: text({ enum: ["available","occupied","reserved","unavailable"] }).notNull().default("available"),
  notes: text(),
  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  unique().on(t.propertyId, t.unitNumber),
  index("room_owner_idx").on(t.ownerId),
  index("room_property_status").on(t.propertyId, t.status),
  pgPolicy("room_owner_scope", { /* same shape */ }),
]);

// packages/db/src/schema/property-photos.ts
export const propertyPhotos = pgTable("property_photos", {
  id: uuid().primaryKey().defaultRandom(),
  propertyId: uuid("property_id").notNull().references(() => properties.id, { onDelete: "cascade" }),
  roomId: uuid("room_id").references(() => rooms.id, { onDelete: "cascade" }),
  ownerId: uuid("owner_id").notNull(),
  url: text().notNull(),
  thumbnailUrl: text("thumbnail_url"),
  caption: text(),
  sortOrder: integer("sort_order").notNull().default(0),
});

Denormalization decision

Every owner-scoped table carries ownerId directly (denormalized) so RLS policies don't join. Saves a join per query × per row. Trade: ownerId must be set correctly on insert (enforce via trigger or app code).

Computed occupancy

Don't store occupancy on the Room. Derive from active contracts:

// packages/db/src/views/room-occupancy.ts (Postgres view + Drizzle view binding)
export const roomOccupancy = pgView("room_occupancy").as((qb) =>
  qb.select({
    roomId: rooms.id,
    ownerId: rooms.ownerId,
    isOccupied: sql<boolean>`exists(
      select 1 from contracts c
      where c.room_id = ${rooms.id}
        and c.status in ('active','grace_period')
        and now() between c.starts_at and coalesce(c.terminates_at, c.ends_at)
    )`.as("is_occupied"),
    activeContractId: sql<string | null>`(
      select id from contracts c
      where c.room_id = ${rooms.id}
        and c.status in ('active','grace_period')
      order by c.starts_at desc limit 1
    )`.as("active_contract_id"),
  }).from(rooms)
);

API surface

# Properties
GET    /v2/properties?cursor=&limit=
POST   /v2/properties
GET    /v2/properties/{id}
PATCH  /v2/properties/{id}
DELETE /v2/properties/{id}                 # soft delete via status

# Floors
GET    /v2/properties/{id}/floors
POST   /v2/properties/{id}/floors
PATCH  /v2/floors/{id}
DELETE /v2/floors/{id}

# Rooms
GET    /v2/properties/{id}/rooms?status=available&kind=studio
POST   /v2/properties/{id}/rooms
GET    /v2/rooms/{id}                      # includes computed occupancy
PATCH  /v2/rooms/{id}
DELETE /v2/rooms/{id}
POST   /v2/rooms/bulk                      # mass create (replaces v1 NewRoomController)

# Photos
POST   /v2/properties/{id}/photos          # returns presigned R2 URL
POST   /v2/photos/{id}/confirm             # client calls after PUT
DELETE /v2/photos/{id}
GET    /v2/properties/{id}/photos

Build steps (Phase 1, week 4-5)

  1. Drizzle schemas for properties, floors, rooms, photos. Manual migration adds RLS policies.
  2. Drizzle view for room_occupancy.
  3. Hono routes with Zod schemas (drizzle-zod derives base, extend for create/update DTOs).
  4. R2 presigned upload integration for photos (uses files.html infra).
  5. Web: property list (card grid), property detail (tabs: overview, floors, rooms, photos), room editor.
  6. Bulk room create — CSV upload or wizard with "add N rooms per floor with pattern 101, 102…".
  7. Backfill from v1: Property + Floor + Room tables → new schema. Map district/region enum.

Open questions

Next: Contracts.