Skip to content

reference data models

khanhthanhdev edited this page Jun 2, 2026 · 1 revision

Data models

The database schema is defined in prisma/schema.prisma. It uses PostgreSQL with the citext and pg_trgm extensions.

Entity relationship diagram

erDiagram
    Tool ||--o{ Report : has
    Tool }o--o{ Category : belongs to
    Tool }o--o{ Collection : belongs to
    Tool }o--o{ Tag : tagged with

    Tool {
        string id PK
        string name
        string nameVi
        string slug UK
        string tagline
        string taglineVi
        string description
        string descriptionVi
        string summary
        string summaryVi
        string content
        string contentVi
        string websiteUrl UK
        string affiliateUrl
        string faviconUrl
        string screenshotUrl
        string pricing
        string pricingVi
        PricingTier pricingTier
        json socials
        boolean isFeatured
        boolean isBroken
        TranslationStatus translationStatusVi
        DateTime translationUpdatedAtVi
        DateTime lastCheckedAt
        string xHandle
        string submitterName
        string submitterEmail
        DateTime publishedAt
        DateTime updatedAt
        DateTime createdAt
        string[] relatedTools
    }

    Category {
        string id PK
        string name
        string nameVi
        string label
        string labelVi
        string slug UK
        string description
        string descriptionVi
        TranslationStatus translationStatusVi
        DateTime translationUpdatedAtVi
        DateTime updatedAt
        DateTime createdAt
    }

    Collection {
        string id PK
        string name
        string nameVi
        string slug UK
        string description
        string descriptionVi
        TranslationStatus translationStatusVi
        DateTime translationUpdatedAtVi
        DateTime updatedAt
        DateTime createdAt
    }

    Tag {
        string id PK
        string name
        string nameVi
        string slug UK
        TranslationStatus translationStatusVi
        DateTime translationUpdatedAtVi
        DateTime updatedAt
        DateTime createdAt
    }

    Report {
        string id PK
        ReportType type
        string message
        string userEmail
        string toolId FK
        DateTime updatedAt
        DateTime createdAt
    }

    Ad {
        string id PK
        string email
        string name
        string description
        string websiteUrl
        string buttonLabel
        string faviconUrl
        AdType type
        DateTime startsAt
        DateTime endsAt
        int stepOrder
        int listInjectionIndex
        DateTime updatedAt
        DateTime createdAt
    }
Loading

Enums

PricingTier

Controls how a tool's pricing is categorized.

Value Description
FREE Completely free to use
FREEMIUM Free tier with paid upgrades
PAID Requires payment
OPEN_SOURCE Open source (may have paid hosting)
CUSTOM Custom or enterprise pricing

TranslationStatus

Tracks the Vietnamese translation state for bilingual fields.

Value Description
MISSING No Vietnamese translation exists
MACHINE Translated by machine (needs review)
REVIEWED Human-reviewed translation

ReportType

Categories for user-submitted reports on tools.

Value Description
BROKEN_LINK The tool's website URL is broken
WRONG_CATEGORY Tool is in the wrong category
OUTDATED Tool information is outdated
OTHER Other issue (requires message)

AdType

Placement types for advertisements.

Value Description
Banner Banner ad placement
Similars In the "similar tools" section
Tools In the tools listing
ToolPage On individual tool pages
BlogPost In blog post content
All All placements

Models

Tool

The main entity. Represents an AI tool in the directory.

Key characteristics:

  • Bilingual fields (English + Vietnamese) for name, tagline, description, summary, content, pricing
  • slug is the URL-safe unique identifier
  • websiteUrl is unique -- one tool per URL
  • relatedTools is a string array of slugs (not a foreign key relation)
  • isFeatured and isBroken are boolean flags for curation
  • translationStatusVi tracks Vietnamese translation state
  • Many-to-many relations with Category, Collection, and Tag
  • One-to-many relation with Report

Indexes:

  • [id, slug] -- composite index for lookups
  • [name] -- text search on English name
  • [nameVi] -- text search on Vietnamese name
  • [description] -- text search on English description
  • [descriptionVi] -- text search on Vietnamese description

Category

Tool classification. Tools can belong to multiple categories.

Key characteristics:

  • Bilingual name, label, and description fields
  • slug is the URL-safe unique identifier
  • Many-to-many relation with Tool

Collection

Curated groupings of tools (e.g., "Best tools for students", "Free alternatives to ChatGPT").

Key characteristics:

  • Bilingual name and description fields
  • slug is the URL-safe unique identifier
  • Many-to-many relation with Tool

Tag

Freeform labels for tools (e.g., "free", "no-signup", "browser-extension").

Key characteristics:

  • Bilingual name field
  • slug is the URL-safe unique identifier
  • Many-to-many relation with Tool

Report

User-submitted reports about tools. Used to flag broken links, wrong categories, or outdated information.

Key characteristics:

  • type uses the ReportType enum
  • message is optional (required for OTHER type)
  • Cascade deletes when the parent Tool is deleted

Ad

Advertisement management with date-based scheduling.

Key characteristics:

  • type uses the AdType enum for placement targeting
  • startsAt and endsAt define the ad's active period
  • stepOrder controls display ordering
  • listInjectionIndex controls where the ad appears in tool lists
  • No relation to Tool -- ads are independent entities

Bilingual pattern

All user-facing text fields follow this pattern:

fieldName     String?   // English (primary)
fieldNameVi   String?   // Vietnamese (translated)
translationStatusVi TranslationStatus @default(MISSING)
translationUpdatedAtVi DateTime?

This applies to: name, tagline, description, summary, content, pricing on Tool; name, label, description on Category; name, description on Collection; name on Tag.

Vector search integration

The database models are mirrored in Qdrant collections for vector search:

  • tools_hybrid -- mirrors Tool data with dense + sparse vectors
  • alternatives_hybrid -- alternative tool suggestions
  • categories_hybrid -- category-based search
  • semantic_cache -- cached AI responses

See Architecture for the full vector search design.

Clone this wiki locally