goodgo-platform/docs/db/ERD.md

GoodGo Platform — Entity Relationship Diagram

Generated from prisma/schema.prisma as part of TEC-3010 (Epic: TEC-3006 — Database Schema Hardening).

• Database: PostgreSQL 16 + PostGIS
• ORM: Prisma
• Models: 41
• Enums: 37
• Geospatial models: Property, ProjectDevelopment, IndustrialPark, POI, TransferListing, InfrastructureProject (all use geometry(Point, 4326) indexed with GiST)

High-level domain map

Domain	Key models
Auth / identity	User, Agent, RefreshToken, OAuthAccount, MfaChallenge
Residential listings	Property, PropertyMedia, Listing, PriceHistory, ProjectDevelopment
Industrial / KCN	IndustrialPark, IndustrialListing
Transfer (furniture + premises)	TransferListing, TransferItem
Search & save	SavedSearch, SavedListing
Transactions / commerce	Transaction, Inquiry, Lead, Order, Escrow, Payment
Subscriptions	Plan, Subscription, UsageRecord
Messaging	Conversation, ConversationParticipant, Message
Analytics / AI	Valuation, MarketIndex, Report, MacroeconomicData, InfrastructureProject
Neighborhood	POI, NeighborhoodScore
Moderation / audit	AdminAuditLog, ModerationAuditLog, Review
Notifications	NotificationLog, NotificationPreference
System	SystemSetting

Mermaid ERD

erDiagram
  User ||--o| Agent : has
  User ||--o{ Listing : sells
  User ||--o{ SavedSearch : owns
  User ||--o| Subscription : holds
  User ||--o{ Payment : makes
  User ||--o{ Review : writes
  User ||--o{ Inquiry : sends
  User ||--o{ RefreshToken : owns
  User ||--o{ OAuthAccount : links
  User ||--o{ Transaction : buys
  User ||--o{ Order : buys_or_sells
  User ||--o{ MfaChallenge : authenticates
  User ||--o{ TransferListing : lists
  User ||--o{ Report : requests
  User ||--o{ SavedListing : saves
  User ||--o{ ProjectDevelopment : owns
  User ||--o{ IndustrialPark : operates

  Agent ||--o{ Listing : represents
  Agent ||--o{ Lead : manages

  ProjectDevelopment ||--o{ Property : contains
  Property ||--o{ Listing : published_as
  Property ||--o{ Valuation : valued_by
  Property ||--o{ PropertyMedia : has

  Listing ||--o{ Transaction : spawns
  Listing ||--o{ Inquiry : receives
  Listing ||--o{ Order : generates
  Listing ||--o{ PriceHistory : tracks
  Listing ||--o{ SavedListing : saved_as

  Transaction ||--o{ Payment : settled_by
  Order ||--o| Escrow : protected_by
  Order ||--o{ Payment : paid_by

  Plan ||--o{ Subscription : sold_as
  Subscription ||--o{ UsageRecord : meters

  IndustrialPark ||--o{ IndustrialListing : leases

  Conversation ||--o{ ConversationParticipant : has
  Conversation ||--o{ Message : contains

  TransferListing ||--o{ TransferItem : contains

The Mermaid diagram above intentionally omits cardinality-only links that do not have Prisma-level relations (e.g. Review.targetId polymorphic references, ModerationAuditLog.targetId, AdminAuditLog.targetId, NotificationLog.userId).

Detached / polymorphic references

These fields reference other entities by id but lack a Prisma relation — they are polymorphic on purpose, but they sidestep referential integrity:

• Review.targetType + Review.targetId → any listing/property/agent
• ModerationAuditLog.targetType + .targetId → any moderatable entity
• AdminAuditLog.targetId + .targetType → user/listing/subscription
• NotificationLog.userId → User.id (no FK)
• ConversationParticipant.userId → User.id (no FK)
• Message.senderId → User.id (no FK)
• IndustrialListing.sellerId / .agentId → User.id / Agent.id (no FK)
• Lead.source (string), Review.targetType (string) — no enum
• Conversation.listingId → Listing.id (no FK)

See schema-audit.md → section Findings for severity.

Rendered diagram

The Mermaid block above renders directly in GitHub, GitLab and most Markdown viewers. For an offline PNG/SVG, run:

npx @mermaid-js/mermaid-cli -i docs/db/ERD.md -o docs/db/ERD.png