Database

45 tables defined in ordered SQL migrations under packages/database/migrations/. PostgreSQL with PostGIS for geographic data; @repo/database is a Kysely client (on the pg driver) with generated types — there is no ORM.

Core tables

Group	Tables
Users & Auth	User, Session, Account, Verification, UserAddress
Agents & Teams	AgentProfile, AgentServiceArea, Team, TeamMember, TeamInvitation
Properties	Property, PropertyFeature, PropertyImage, PropertyVideo, FloorPlan, PropertyUnit, UnitInventory, PropertyAgent, PropertyList, PropertyListItem
Listings	ListingVerification, Disclosure, RentalTerms
Leads & Comms	Inquiry, Tour, Note, Task, Notification
Transactions	Offer, Transaction, Commission, Document, Payment
Engagement	Favorite, SavedSearch, PropertyView, Review
Market Data	Comparable, PriceHistory, MarketReport
Config	RedisConfig

Key enums

Read packages/database/src/enums.ts (const-object values) and packages/database/src/generated/db.ts (column types) before using any enum value — guessing is the most common bug class in this repo.

Enum	Values
UserRole	INDIVIDUAL, AGENCY_ADMIN, ADMIN
PropertyType	HOUSE, APARTMENT, SUITE_ROOM, LAND
PropertyStatus	DRAFT, ACTIVE, PAUSED, SOLD, RENTED, ARCHIVED
ListingType	SALE, RENT
TeamType	AGENCY, GROUP, CONSTRUCTION_COMPANY
TeamRole	LEAD, ADMIN, MEMBER
Tour.status	REQUESTED (not PENDING), CONFIRMED, RESCHEDULED, COMPLETED, CANCELLED, DENIED
InquiryStatus	NEW, CONTACTED, IN_PROGRESS, CLOSED, CONVERTED

The Tour.status = REQUESTED vs PENDING mismatch has caused multiple bugs — call it out explicitly in PR review.

Key relationships

erDiagram
    User ||--o| AgentProfile : "optional"
    AgentProfile ||--o{ TeamMember : "joins"
    TeamMember }o--|| Team : "belongs to"
    User ||--o{ Property : "owns"
    Property ||--o{ PropertyAgent : "assigned to"
    AgentProfile ||--o{ PropertyAgent : "manages"
    Property ||--o{ PropertyImage : "has"
    Property ||--o{ PropertyUnit : "has units"
    PropertyUnit ||--o{ UnitInventory : "has inventory"
    Property ||--o{ Inquiry : "receives"
    Property ||--o{ RentalTerms : "has terms"
    User ||--o{ Favorite : "saves"
    User ||--o{ SavedSearch : "creates"
    Property ||--o{ Transaction : "results in"
    PropertyList ||--o{ PropertyListItem : "contains"
    AgentProfile ||--o{ PropertyList : "owns"

Property type validation

Type	Required fields	Optional fields
HOUSE	bedrooms, bathrooms, constructedArea, price	lotSize, yearBuilt, parkingSpaces
APARTMENT	bedrooms, bathrooms, constructedArea, price	yearBuilt, parkingSpaces
SUITE_ROOM	bedrooms (≥1), bathrooms, constructedArea, price	yearBuilt, parkingSpaces
LAND	lotSize, price	yearBuilt, parkingSpaces

Validation rules live in @repo/validation (Zod), shared by web form actions and tRPC inputs.

Property status workflow

DRAFT → ACTIVE → PAUSED → ACTIVE (resume)
                → SOLD / RENTED (terminal)
DRAFT → ARCHIVED (soft delete)

The price_history trigger fires only when the property is in ACTIVE status — INITIAL on publish/reactivate, PRICE_REDUCTION / PRICE_INCREASE on edits. Price-drop badges on property cards are computed from this table during worker re-indexing (30-day window).

Row Level Security

85+ RLS policies across 28 database tables. User data is completely isolated at the DB level:

• Public users see only ACTIVE properties; owners see all statuses.
• Transaction records are visible only to participants.
• Agent profile data is public; user PII is private.

Policies are declared in the 0003_triggers_and_functions.ts migration and applied by db:migrate (and re-applied by the db:reset script) after the baseline schema is created.

ID generation

Most tables have IDs defaulted to generate_*_id(). The generator functions are created by the 0001_extensions_and_id_functions.ts migration and must exist before the 0002_baseline_schema.ts table-create migration runs, which is why the migration chain is strictly ordered (0001 functions → 0002 schema → 0003 triggers) and the db:reset script replays it instead of dropping and recreating in one atomic step. See Conventions → Monorepo.

Reset workflow

cd packages/database && bun run db:reset   # drops + recreates schema with PostGIS,
                                            # replays the migration chain
                                            # (0001 functions → 0002 schema → 0003 triggers),
                                            # verifies state
redis-cli -u "$REDIS_URL" FLUSHDB

packages/database/scripts/db-reset.ts is the source of truth.