# GoodGo Platform — Entity Relationship Diagram

> Generated from `prisma/schema.prisma` as part of [TEC-3010](/TEC/issues/TEC-3010) (Epic: [TEC-3006](/TEC/issues/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

```mermaid
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:

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