> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/Ishaq74/concordia/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Schema

> Complete database schema reference for Concordia

Concordia uses PostgreSQL with Drizzle ORM for type-safe database access. The schema is organized into several functional areas: authentication, profiles, blog content, services marketplace, and notifications.

## Schema Organization

The database schema is defined in TypeScript files located at `src/database/schemas/`. Each schema file represents a logical grouping of related tables.

```typescript theme={null}
// Main schema export file
export * from './schemas/auth-schema';
export * from './schemas/audit-log.schema';
export * from './schemas/profile.schema';
export * from "./schemas/blog_posts.schema";
export * from "./schemas/blog_authors.schema";
export * from "./schemas/blog_categories.schema";
// ... more exports
```

## Authentication Tables

### user

Core user account table with authentication and profile data.

<ParamField path="id" type="text" required>
  Primary key for the user
</ParamField>

<ParamField path="name" type="text" required>
  User's display name
</ParamField>

<ParamField path="email" type="text" required>
  User's email address (unique)
</ParamField>

<ParamField path="emailVerified" type="boolean" default="false">
  Whether the email has been verified
</ParamField>

<ParamField path="image" type="text">
  Profile image URL
</ParamField>

<ParamField path="username" type="text">
  Unique username (unique)
</ParamField>

<ParamField path="displayUsername" type="text">
  Display version of username
</ParamField>

<ParamField path="role" type="text">
  User role for permissions
</ParamField>

<ParamField path="banned" type="boolean" default="false">
  Whether the user is banned
</ParamField>

<ParamField path="banReason" type="text">
  Reason for ban
</ParamField>

<ParamField path="banExpires" type="timestamp">
  When the ban expires
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Account creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Last update timestamp (auto-updated)
</ParamField>

### session

User session management with device tracking.

<ParamField path="id" type="text" required>
  Primary key for the session
</ParamField>

<ParamField path="token" type="text" required>
  Session token (unique)
</ParamField>

<ParamField path="expiresAt" type="timestamp" required>
  Session expiration timestamp
</ParamField>

<ParamField path="userId" type="text" required>
  Foreign key to user table (cascade delete)
</ParamField>

<ParamField path="ipAddress" type="text">
  IP address of the session
</ParamField>

<ParamField path="userAgent" type="text">
  Browser/device user agent
</ParamField>

<ParamField path="activeOrganizationId" type="text">
  Currently active organization for this session
</ParamField>

<ParamField path="impersonatedBy" type="text">
  User ID if this session is an impersonation
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Session creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Last activity timestamp
</ParamField>

**Indexes:**

* `session_userId_idx` on `userId`

### account

OAuth provider accounts linked to users.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="accountId" type="text" required>
  Provider-specific account ID
</ParamField>

<ParamField path="providerId" type="text" required>
  OAuth provider identifier (google, github, etc.)
</ParamField>

<ParamField path="userId" type="text" required>
  Foreign key to user (cascade delete)
</ParamField>

<ParamField path="accessToken" type="text">
  OAuth access token
</ParamField>

<ParamField path="refreshToken" type="text">
  OAuth refresh token
</ParamField>

<ParamField path="idToken" type="text">
  OAuth ID token
</ParamField>

<ParamField path="accessTokenExpiresAt" type="timestamp">
  Access token expiration
</ParamField>

<ParamField path="refreshTokenExpiresAt" type="timestamp">
  Refresh token expiration
</ParamField>

<ParamField path="scope" type="text">
  OAuth scope granted
</ParamField>

<ParamField path="password" type="text">
  Hashed password for credential-based auth
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Account link creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Last update timestamp
</ParamField>

**Indexes:**

* `account_userId_idx` on `userId`

### verification

Email verification and password reset tokens.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="identifier" type="text" required>
  Email or identifier to verify
</ParamField>

<ParamField path="value" type="text" required>
  Verification token/code
</ParamField>

<ParamField path="expiresAt" type="timestamp" required>
  Token expiration timestamp
</ParamField>

<ParamField path="usedAt" type="timestamp">
  When the token was used (null if unused)
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Token creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Last update timestamp
</ParamField>

**Indexes:**

* `verification_identifier_idx` on `identifier`

### organization

Multi-tenant organization/workspace management.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="name" type="text" required>
  Organization name
</ParamField>

<ParamField path="slug" type="text" required>
  URL-friendly slug (unique)
</ParamField>

<ParamField path="logo" type="text">
  Logo URL
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Organization creation timestamp
</ParamField>

<ParamField path="metadata" type="text">
  Additional organization metadata (JSON string)
</ParamField>

**Indexes:**

* `organization_slug_uidx` unique index on `slug`

### member

Organization membership and roles.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="organizationId" type="text" required>
  Foreign key to organization (cascade delete)
</ParamField>

<ParamField path="userId" type="text" required>
  Foreign key to user (cascade delete)
</ParamField>

<ParamField path="role" type="text" default="member">
  Member role (member, admin, owner, etc.)
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Membership creation timestamp
</ParamField>

**Indexes:**

* `member_organizationId_idx` on `organizationId`
* `member_userId_idx` on `userId`

### invitation

Pending organization invitations.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="organizationId" type="text" required>
  Foreign key to organization (cascade delete)
</ParamField>

<ParamField path="email" type="text" required>
  Invited email address
</ParamField>

<ParamField path="role" type="text">
  Role to grant upon acceptance
</ParamField>

<ParamField path="status" type="text" default="pending">
  Invitation status (pending, accepted, rejected)
</ParamField>

<ParamField path="expiresAt" type="timestamp" required>
  Invitation expiration timestamp
</ParamField>

<ParamField path="inviterId" type="text" required>
  Foreign key to user who sent the invitation (cascade delete)
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Invitation creation timestamp
</ParamField>

**Indexes:**

* `invitation_organizationId_idx` on `organizationId`
* `invitation_email_idx` on `email`

### rate\_limit

Rate limiting tracking table.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="key" type="text">
  Rate limit key (IP, user ID, etc.)
</ParamField>

<ParamField path="count" type="integer">
  Request count
</ParamField>

<ParamField path="lastRequest" type="bigint">
  Timestamp of last request (milliseconds)
</ParamField>

### audit\_logs

Audit trail for important actions.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="action" type="text" required>
  Action performed (e.g., "user.created", "post.published")
</ParamField>

<ParamField path="userId" type="text">
  User who performed the action
</ParamField>

<ParamField path="targetId" type="text">
  ID of the affected resource
</ParamField>

<ParamField path="ip" type="text">
  IP address of the request
</ParamField>

<ParamField path="userAgent" type="text">
  User agent string
</ParamField>

<ParamField path="data" type="jsonb">
  Additional action data as JSON
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Action timestamp
</ParamField>

## Profile Table

### profile

Extended user profile information beyond core auth.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="userId" type="text" required>
  Foreign key to user (unique)
</ParamField>

<ParamField path="username" type="text">
  Profile username
</ParamField>

<ParamField path="fullName" type="text">
  User's full name
</ParamField>

<ParamField path="bio" type="text">
  Biography/about text
</ParamField>

<ParamField path="avatarUrl" type="text">
  Avatar image URL
</ParamField>

<ParamField path="location" type="text">
  User location
</ParamField>

<ParamField path="website" type="text">
  Personal website URL
</ParamField>

<ParamField path="preferredLanguage" type="text" default="fr">
  Preferred UI language
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Profile creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Last update timestamp (auto-updated)
</ParamField>

## Blog Tables

### blog\_posts

Main blog post content table.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="slug" type="text" required>
  URL slug (unique)
</ParamField>

<ParamField path="status" type="text" required>
  Publication status (draft, published, archived)
</ParamField>

<ParamField path="organizationId" type="text">
  Foreign key to blog\_organizations
</ParamField>

<ParamField path="publishedAt" type="timestamp">
  Publication timestamp
</ParamField>

<ParamField path="displayInHome" type="boolean" default="false">
  Show on homepage
</ParamField>

<ParamField path="displayInBlog" type="boolean" default="true">
  Show in blog listing
</ParamField>

<ParamField path="isFeatured" type="boolean" default="false">
  Featured post flag
</ParamField>

<ParamField path="readingTime" type="text">
  Estimated reading time
</ParamField>

<ParamField path="wordCount" type="text">
  Article word count
</ParamField>

<ParamField path="timeRequired" type="text">
  Schema.org time required field
</ParamField>

<ParamField path="allowComments" type="boolean" default="true">
  Whether comments are enabled
</ParamField>

<ParamField path="inLanguage" type="text" required>
  Primary language code
</ParamField>

<ParamField path="license" type="text">
  Content license
</ParamField>

<ParamField path="discussionUrl" type="text">
  External discussion URL
</ParamField>

<ParamField path="url" type="text">
  Canonical URL
</ParamField>

<ParamField path="identifier" type="text">
  External identifier
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Post creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Last update timestamp
</ParamField>

**Indexes:**

* `idx_blog_posts_slug` unique on `slug`
* `idx_blog_posts_status` on `status`
* `idx_blog_posts_published_at` on `publishedAt`
* `idx_blog_posts_home` on `displayInHome`
* `idx_blog_posts_featured` on `isFeatured`

### blog\_post\_authors

Join table linking posts to authors (many-to-many).

<ParamField path="postId" type="text" required>
  Foreign key to blog\_posts
</ParamField>

<ParamField path="authorId" type="text" required>
  Foreign key to blog\_authors
</ParamField>

**Indexes:**

* `idx_blog_post_authors_post` on `postId`
* `idx_blog_post_authors_author` on `authorId`

### blog\_post\_categories

Join table linking posts to categories (many-to-many).

<ParamField path="postId" type="text" required>
  Foreign key to blog\_posts
</ParamField>

<ParamField path="categoryId" type="text" required>
  Foreign key to blog\_categories
</ParamField>

**Indexes:**

* `idx_blog_post_categories_post` on `postId`
* `idx_blog_post_categories_category` on `categoryId`

### blog\_post\_media

Join table linking posts to media (many-to-many).

<ParamField path="postId" type="text" required>
  Foreign key to blog\_posts
</ParamField>

<ParamField path="mediaId" type="text" required>
  Foreign key to blog\_media
</ParamField>

<ParamField path="type" type="text" required>
  Media type (cover, inline, etc.)
</ParamField>

<ParamField path="position" type="text">
  Position/order in post
</ParamField>

**Indexes:**

* `idx_blog_post_media_post` on `postId`
* `idx_blog_post_media_media` on `mediaId`

### blog\_authors

Blog author profiles.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="slug" type="text" required>
  URL slug (unique)
</ParamField>

<ParamField path="givenName" type="jsonb">
  First name (multilingual)
</ParamField>

<ParamField path="familyName" type="jsonb">
  Last name (multilingual)
</ParamField>

<ParamField path="displayName" type="jsonb" required>
  Display name (multilingual)
</ParamField>

<ParamField path="bio" type="jsonb">
  Biography (multilingual)
</ParamField>

<ParamField path="jobTitle" type="jsonb">
  Job title (multilingual)
</ParamField>

<ParamField path="email" type="text">
  Contact email (unique)
</ParamField>

<ParamField path="avatarId" type="text">
  Foreign key to blog\_media
</ParamField>

<ParamField path="avatarUrl" type="text">
  Direct avatar URL
</ParamField>

<ParamField path="website" type="text">
  Personal website
</ParamField>

<ParamField path="sameAs" type="jsonb">
  Social media links array
</ParamField>

<ParamField path="worksForId" type="text">
  Foreign key to blog\_organizations
</ParamField>

<ParamField path="displayInHome" type="boolean" default="false">
  Show on homepage
</ParamField>

<ParamField path="displayInBlog" type="boolean" default="true">
  Show in blog
</ParamField>

<ParamField path="isFeatured" type="boolean" default="false">
  Featured author flag
</ParamField>

<ParamField path="seoTitle" type="jsonb">
  SEO title (multilingual)
</ParamField>

<ParamField path="seoDescription" type="jsonb">
  SEO description (multilingual)
</ParamField>

<ParamField path="seoKeywords" type="jsonb">
  SEO keywords (multilingual)
</ParamField>

<ParamField path="canonicalUrl" type="jsonb">
  Canonical URL (multilingual)
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Update timestamp
</ParamField>

**Indexes:**

* `idx_blog_authors_slug` unique on `slug`
* `idx_blog_authors_home` on `displayInHome`
* `idx_blog_authors_featured` on `isFeatured`
* `idx_blog_authors_email` on `email`

### blog\_categories

Blog post categories with hierarchical support.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="slug" type="text" required>
  URL slug (unique)
</ParamField>

<ParamField path="name" type="jsonb" required>
  Category name (multilingual)
</ParamField>

<ParamField path="description" type="jsonb">
  Category description (multilingual)
</ParamField>

<ParamField path="featuredImageId" type="text">
  Foreign key to blog\_media
</ParamField>

<ParamField path="displayInHome" type="boolean" default="false">
  Show on homepage
</ParamField>

<ParamField path="displayInMenu" type="boolean" default="true">
  Show in navigation menu
</ParamField>

<ParamField path="displayInBlog" type="boolean" default="true">
  Show in blog
</ParamField>

<ParamField path="isFeatured" type="boolean" default="false">
  Featured category flag
</ParamField>

<ParamField path="parentId" type="text">
  Foreign key to parent category (self-reference)
</ParamField>

<ParamField path="seoTitle" type="jsonb">
  SEO title (multilingual)
</ParamField>

<ParamField path="seoDescription" type="jsonb">
  SEO description (multilingual)
</ParamField>

<ParamField path="seoKeywords" type="jsonb">
  SEO keywords (multilingual)
</ParamField>

<ParamField path="canonicalUrl" type="jsonb">
  Canonical URL (multilingual)
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Update timestamp
</ParamField>

**Indexes:**

* `idx_blog_categories_slug` unique on `slug`
* `idx_blog_categories_parent` on `parentId`
* `idx_blog_categories_home` on `displayInHome`
* `idx_blog_categories_featured` on `isFeatured`

### blog\_comments

Universal comments table supporting threading.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="postId" type="text" required>
  ID of the related entity
</ParamField>

<ParamField path="postType" type="text" required>
  Type of entity (blog, place, event, hike, classified)
</ParamField>

<ParamField path="parentId" type="text">
  Foreign key to parent comment for threading
</ParamField>

<ParamField path="authorName" type="text" required>
  Comment author name
</ParamField>

<ParamField path="authorEmail" type="text" required>
  Comment author email
</ParamField>

<ParamField path="content" type="jsonb" required>
  Comment content (multilingual)
</ParamField>

<ParamField path="rating" type="integer" default="0">
  Optional rating (0-5)
</ParamField>

<ParamField path="status" type="text" required>
  Moderation status (pending, approved, rejected)
</ParamField>

<ParamField path="inLanguage" type="text" required>
  Comment language
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Update timestamp
</ParamField>

**Indexes:**

* `idx_blog_comments_entity` on `postId`
* `idx_blog_comments_type` on `postType`
* `idx_blog_comments_status` on `status`
* `idx_blog_comments_parent` on `parentId`

### blog\_media

Media assets for blog content.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="url" type="text" required>
  Media URL
</ParamField>

<ParamField path="contentUrl" type="text">
  Alternative content URL
</ParamField>

<ParamField path="type" type="text" required>
  Media type (image, video, audio)
</ParamField>

<ParamField path="encodingFormat" type="text">
  MIME type
</ParamField>

<ParamField path="width" type="text">
  Image/video width
</ParamField>

<ParamField path="height" type="text">
  Image/video height
</ParamField>

<ParamField path="duration" type="text">
  Video/audio duration
</ParamField>

<ParamField path="license" type="text">
  Media license
</ParamField>

<ParamField path="copyrightHolder" type="text">
  Copyright holder name
</ParamField>

<ParamField path="caption" type="jsonb">
  Media caption (multilingual)
</ParamField>

<ParamField path="description" type="jsonb">
  Media description (multilingual)
</ParamField>

<ParamField path="alt" type="jsonb">
  Alt text for accessibility (multilingual)
</ParamField>

<ParamField path="thumbnailUrl" type="text">
  Thumbnail URL
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Upload timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Update timestamp
</ParamField>

**Indexes:**

* `idx_blog_media_type` on `type`
* `idx_blog_media_url` on `url`

### blog\_translations

Multilingual translations for blog posts.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="postId" type="text" required>
  Foreign key to blog\_posts
</ParamField>

<ParamField path="inLanguage" type="text" required>
  Language code (fr, en, etc.)
</ParamField>

<ParamField path="headline" type="jsonb" required>
  Post headline (translated)
</ParamField>

<ParamField path="alternativeHeadline" type="jsonb">
  Alternative headline
</ParamField>

<ParamField path="articleBody" type="jsonb" required>
  Full article content (translated)
</ParamField>

<ParamField path="excerpt" type="jsonb" required>
  Post excerpt (translated)
</ParamField>

<ParamField path="seoTitle" type="jsonb">
  SEO title (translated)
</ParamField>

<ParamField path="seoDescription" type="jsonb">
  SEO description (translated)
</ParamField>

<ParamField path="seoKeywords" type="jsonb">
  SEO keywords (translated)
</ParamField>

<ParamField path="canonicalUrl" type="jsonb">
  Canonical URL (translated)
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Translation creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Translation update timestamp
</ParamField>

**Indexes:**

* `idx_blog_translations_post` on `postId`
* `idx_blog_translations_language` on `inLanguage`

### blog\_organizations

Schema.org-compatible organization profiles for blog publishers.

<Expandable title="Identity Fields">
  <ParamField path="id" type="text" required>
    Primary key
  </ParamField>

  <ParamField path="name" type="text" required>
    Organization name
  </ParamField>

  <ParamField path="slug" type="text" required>
    URL slug (unique)
  </ParamField>

  <ParamField path="alternateName" type="jsonb">
    Alternative names array
  </ParamField>

  <ParamField path="description" type="jsonb">
    Organization description (multilingual)
  </ParamField>

  <ParamField path="url" type="text">
    Organization website
  </ParamField>

  <ParamField path="logo" type="text">
    Logo URL
  </ParamField>

  <ParamField path="image" type="text">
    Main image URL
  </ParamField>

  <ParamField path="slogan" type="jsonb">
    Organization slogan (multilingual)
  </ParamField>
</Expandable>

<Expandable title="Contact Information">
  <ParamField path="email" type="text">
    Contact email
  </ParamField>

  <ParamField path="telephone" type="text">
    Contact phone number
  </ParamField>

  <ParamField path="faxNumber" type="text">
    Fax number
  </ParamField>

  <ParamField path="address" type="jsonb">
    Physical address object
  </ParamField>

  <ParamField path="contactPoint" type="jsonb">
    Contact points array
  </ParamField>
</Expandable>

<Expandable title="Legal & Identifiers">
  <ParamField path="legalName" type="jsonb">
    Legal name (multilingual)
  </ParamField>

  <ParamField path="taxID" type="text">
    Tax identifier
  </ParamField>

  <ParamField path="vatID" type="text">
    VAT number
  </ParamField>

  <ParamField path="leiCode" type="text">
    Legal Entity Identifier
  </ParamField>

  <ParamField path="duns" type="text">
    DUNS number
  </ParamField>

  <ParamField path="isicV4" type="text">
    ISIC industry classification
  </ParamField>

  <ParamField path="naics" type="text">
    NAICS industry code
  </ParamField>

  <ParamField path="nonprofitStatus" type="text">
    Nonprofit status
  </ParamField>
</Expandable>

<Expandable title="People & Structure">
  <ParamField path="founder" type="jsonb">
    Founder(s)
  </ParamField>

  <ParamField path="foundingDate" type="timestamp">
    Founding date
  </ParamField>

  <ParamField path="foundingLocation" type="text">
    Founding location
  </ParamField>

  <ParamField path="numberOfEmployees" type="integer">
    Employee count
  </ParamField>

  <ParamField path="employee" type="jsonb">
    Employee list
  </ParamField>

  <ParamField path="alumni" type="jsonb">
    Alumni list
  </ParamField>

  <ParamField path="parentOrganization" type="text">
    Parent organization ID
  </ParamField>

  <ParamField path="subOrganization" type="jsonb">
    Sub-organization IDs
  </ParamField>

  <ParamField path="department" type="jsonb">
    Departments array
  </ParamField>
</Expandable>

<Expandable title="Editorial Policies (NewsMediaOrganization)">
  <ParamField path="publishingPrinciples" type="text">
    Publishing principles URL
  </ParamField>

  <ParamField path="actionableFeedbackPolicy" type="text">
    Feedback policy URL
  </ParamField>

  <ParamField path="correctionsPolicy" type="text">
    Corrections policy URL
  </ParamField>

  <ParamField path="diversityPolicy" type="text">
    Diversity policy URL
  </ParamField>

  <ParamField path="ethicsPolicy" type="text">
    Ethics policy URL
  </ParamField>

  <ParamField path="masthead" type="text">
    Masthead URL
  </ParamField>

  <ParamField path="verificationFactCheckingPolicy" type="text">
    Fact-checking policy URL
  </ParamField>
</Expandable>

<ParamField path="isActive" type="boolean" default="true">
  Active status
</ParamField>

<ParamField path="isFeatured" type="boolean" default="false">
  Featured flag
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Update timestamp (auto-updated)
</ParamField>

## Services Tables

### services\_listings

Service marketplace listings.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="slug" type="text" required>
  URL slug (unique)
</ParamField>

<ParamField path="categoryId" type="text">
  Foreign key to services\_categories
</ParamField>

<ParamField path="providerId" type="text" required>
  Service provider user ID
</ParamField>

<ParamField path="organizationId" type="text">
  Foreign key to blog\_organizations
</ParamField>

<ParamField path="status" type="text" default="pending_review">
  Listing status (pending\_review, active, inactive)
</ParamField>

<ParamField path="basePrice" type="text">
  Base price
</ParamField>

<ParamField path="priceType" type="text">
  Pricing model (fixed, hourly, etc.)
</ParamField>

<ParamField path="currency" type="text" default="EUR">
  Currency code
</ParamField>

<ParamField path="durationMinutes" type="integer">
  Service duration in minutes
</ParamField>

<ParamField path="isMobile" type="boolean" default="false">
  Mobile service flag
</ParamField>

<ParamField path="maxParticipants" type="integer">
  Maximum participants
</ParamField>

<ParamField path="bookingAdvanceHours" type="integer">
  Minimum advance booking hours
</ParamField>

<ParamField path="cancellationHours" type="integer">
  Cancellation notice hours
</ParamField>

<ParamField path="isActive" type="boolean" default="true">
  Active status
</ParamField>

<ParamField path="isFeatured" type="boolean" default="false">
  Featured listing flag
</ParamField>

<ParamField path="displayInHome" type="boolean" default="false">
  Show on homepage
</ParamField>

<ParamField path="allowReviews" type="boolean" default="true">
  Allow customer reviews
</ParamField>

<ParamField path="inLanguage" type="text" default="fr">
  Primary language
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Update timestamp
</ParamField>

**Indexes:**

* `idx_services_listings_slug` unique on `slug`
* `idx_services_listings_status` on `status`
* `idx_services_listings_category` on `categoryId`
* `idx_services_listings_provider` on `providerId`
* `idx_services_listings_org` on `organizationId`
* `idx_services_listings_featured` on `isFeatured`
* `idx_services_listings_home` on `displayInHome`
* `idx_services_listings_active` on `isActive`

### services\_media\_links

Join table linking services to media.

<ParamField path="serviceId" type="text" required>
  Foreign key to services\_listings
</ParamField>

<ParamField path="mediaId" type="text" required>
  Foreign key to services\_media
</ParamField>

<ParamField path="type" type="text" required>
  Media type (cover, gallery)
</ParamField>

<ParamField path="position" type="text">
  Display position/order
</ParamField>

**Indexes:**

* `idx_services_media_links_service` on `serviceId`
* `idx_services_media_links_media` on `mediaId`

### services\_categories

Service category taxonomy with hierarchy.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="slug" type="text" required>
  URL slug (unique)
</ParamField>

<ParamField path="name" type="jsonb" required>
  Category name (multilingual)
</ParamField>

<ParamField path="description" type="jsonb">
  Category description (multilingual)
</ParamField>

<ParamField path="icon" type="text">
  Icon identifier
</ParamField>

<ParamField path="featuredImageId" type="text">
  Foreign key to services\_media
</ParamField>

<ParamField path="parentId" type="text">
  Parent category ID (self-reference)
</ParamField>

<ParamField path="sortOrder" type="integer" default="0">
  Display sort order
</ParamField>

<ParamField path="displayInHome" type="boolean" default="false">
  Show on homepage
</ParamField>

<ParamField path="displayInMenu" type="boolean" default="true">
  Show in menu
</ParamField>

<ParamField path="isActive" type="boolean" default="true">
  Active status
</ParamField>

<ParamField path="isFeatured" type="boolean" default="false">
  Featured flag
</ParamField>

<ParamField path="seoTitle" type="jsonb">
  SEO title (multilingual)
</ParamField>

<ParamField path="seoDescription" type="jsonb">
  SEO description (multilingual)
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Update timestamp
</ParamField>

**Indexes:**

* `idx_services_categories_slug` unique on `slug`
* `idx_services_categories_parent` on `parentId`
* `idx_services_categories_sort` on `sortOrder`
* `idx_services_categories_active` on `isActive`

### services\_reviews

Service reviews and ratings with threading.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="serviceId" type="text" required>
  Foreign key to services\_listings
</ParamField>

<ParamField path="parentId" type="text">
  Parent review ID for replies
</ParamField>

<ParamField path="authorName" type="text" required>
  Reviewer name
</ParamField>

<ParamField path="authorEmail" type="text" required>
  Reviewer email
</ParamField>

<ParamField path="authorId" type="text">
  Reviewer user ID
</ParamField>

<ParamField path="content" type="jsonb" required>
  Review content (multilingual)
</ParamField>

<ParamField path="rating" type="integer" required>
  Rating (1-5)
</ParamField>

<ParamField path="status" type="text" default="pending">
  Moderation status (pending, approved, rejected)
</ParamField>

<ParamField path="inLanguage" type="text" required>
  Review language
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Review creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Update timestamp
</ParamField>

**Indexes:**

* `idx_services_reviews_service` on `serviceId`
* `idx_services_reviews_status` on `status`
* `idx_services_reviews_rating` on `rating`
* `idx_services_reviews_parent` on `parentId`
* `idx_services_reviews_author` on `authorId`

### services\_availability

Service provider availability schedule.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="serviceId" type="text" required>
  Foreign key to services\_listings
</ParamField>

<ParamField path="dayOfWeek" type="integer" required>
  Day of week (0=Sunday, 6=Saturday)
</ParamField>

<ParamField path="startTime" type="text" required>
  Start time (HH:MM format)
</ParamField>

<ParamField path="endTime" type="text" required>
  End time (HH:MM format)
</ParamField>

<ParamField path="isAvailable" type="boolean" default="true">
  Availability flag
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Creation timestamp
</ParamField>

**Indexes:**

* `idx_services_availability_unique` unique on (`serviceId`, `dayOfWeek`, `startTime`)
* `idx_services_availability_service` on `serviceId`

### services\_bookings

Service booking records.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="serviceId" type="text" required>
  Foreign key to services\_listings
</ParamField>

<ParamField path="customerId" type="text" required>
  Customer user ID
</ParamField>

<ParamField path="providerId" type="text" required>
  Provider user ID
</ParamField>

<ParamField path="bookingDate" type="text" required>
  Booking date (YYYY-MM-DD)
</ParamField>

<ParamField path="bookingTime" type="text" required>
  Booking time (HH:MM)
</ParamField>

<ParamField path="durationMinutes" type="integer" required>
  Booking duration in minutes
</ParamField>

<ParamField path="totalPrice" type="text">
  Total booking price
</ParamField>

<ParamField path="currency" type="text" default="EUR">
  Currency code
</ParamField>

<ParamField path="status" type="text" default="pending">
  Booking status (pending, confirmed, cancelled, completed)
</ParamField>

<ParamField path="customerMessage" type="text">
  Message from customer
</ParamField>

<ParamField path="providerResponse" type="text">
  Response from provider
</ParamField>

<ParamField path="cancelledAt" type="timestamp">
  Cancellation timestamp
</ParamField>

<ParamField path="completedAt" type="timestamp">
  Completion timestamp
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Booking creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Update timestamp
</ParamField>

**Indexes:**

* `idx_services_bookings_service` on `serviceId`
* `idx_services_bookings_customer` on `customerId`
* `idx_services_bookings_provider` on `providerId`
* `idx_services_bookings_status` on `status`
* `idx_services_bookings_date` on `bookingDate`

### services\_media

Media assets for service listings.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="url" type="text" required>
  Media URL
</ParamField>

<ParamField path="contentUrl" type="text">
  Alternative content URL
</ParamField>

<ParamField path="type" type="text" required>
  Media type (image, video, audio)
</ParamField>

<ParamField path="encodingFormat" type="text">
  MIME type
</ParamField>

<ParamField path="width" type="text">
  Width in pixels
</ParamField>

<ParamField path="height" type="text">
  Height in pixels
</ParamField>

<ParamField path="duration" type="text">
  Video/audio duration
</ParamField>

<ParamField path="license" type="text">
  Media license
</ParamField>

<ParamField path="copyrightHolder" type="text">
  Copyright holder
</ParamField>

<ParamField path="caption" type="jsonb">
  Media caption (multilingual)
</ParamField>

<ParamField path="description" type="jsonb">
  Media description (multilingual)
</ParamField>

<ParamField path="alt" type="jsonb">
  Alt text (multilingual)
</ParamField>

<ParamField path="thumbnailUrl" type="text">
  Thumbnail URL
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Upload timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Update timestamp
</ParamField>

**Indexes:**

* `idx_services_media_type` on `type`
* `idx_services_media_url` on `url`

### services\_translations

Multilingual translations for service listings.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="serviceId" type="text" required>
  Foreign key to services\_listings
</ParamField>

<ParamField path="inLanguage" type="text" required>
  Language code
</ParamField>

<ParamField path="title" type="jsonb" required>
  Service title (translated)
</ParamField>

<ParamField path="description" type="jsonb" required>
  Full description (translated)
</ParamField>

<ParamField path="shortDescription" type="jsonb">
  Short description (translated)
</ParamField>

<ParamField path="seoTitle" type="jsonb">
  SEO title (translated)
</ParamField>

<ParamField path="seoDescription" type="jsonb">
  SEO description (translated)
</ParamField>

<ParamField path="seoKeywords" type="jsonb">
  SEO keywords (translated)
</ParamField>

<ParamField path="canonicalUrl" type="jsonb">
  Canonical URL (translated)
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Translation creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Update timestamp
</ParamField>

**Indexes:**

* `idx_services_translations_service` on `serviceId`
* `idx_services_translations_language` on `inLanguage`

## Notification Table

### notification

User notification system.

<ParamField path="id" type="text" required>
  Primary key
</ParamField>

<ParamField path="userId" type="text" required>
  Target user ID
</ParamField>

<ParamField path="type" type="text" required>
  Notification type (email, push, in-app, etc.)
</ParamField>

<ParamField path="title" type="text" required>
  Notification title
</ParamField>

<ParamField path="body" type="text">
  Notification body content
</ParamField>

<ParamField path="message" type="text">
  Additional message text
</ParamField>

<ParamField path="status" type="text">
  Delivery status (sent, pending, failed)
</ParamField>

<ParamField path="targetType" type="text">
  Type of related entity (post, booking, etc.)
</ParamField>

<ParamField path="targetId" type="text">
  ID of related entity
</ParamField>

<ParamField path="data" type="jsonb">
  Additional notification data as JSON
</ParamField>

<ParamField path="isRead" type="boolean" default="false">
  Read status
</ParamField>

<ParamField path="readAt" type="timestamp">
  When notification was read
</ParamField>

<ParamField path="createdAt" type="timestamp" required>
  Notification creation timestamp
</ParamField>

<ParamField path="updatedAt" type="timestamp">
  Update timestamp
</ParamField>

## Data Types Reference

### Common Types

* **text**: Variable-length text field
* **integer**: 32-bit integer
* **bigint**: 64-bit integer
* **boolean**: True/false value
* **timestamp**: Date and time with timezone
* **jsonb**: Binary JSON for structured data (indexed, supports multilingual content)

### JSONB Usage

Many fields use `jsonb` type to support multilingual content:

```typescript theme={null}
// Example multilingual field
{
  "fr": "Bonjour",
  "en": "Hello",
  "es": "Hola"
}
```

### Cascade Deletion

Foreign keys with `{ onDelete: "cascade" }` automatically delete child records:

* Deleting a user deletes all their sessions, accounts, and memberships
* Deleting an organization deletes all members and invitations
* Deleting a service listing deletes all bookings and reviews

## Schema Files

All schema definitions are located in:

```
src/database/schemas/
├── auth-schema.ts              # Authentication tables
├── audit-log.schema.ts         # Audit logging
├── profile.schema.ts           # User profiles
├── blog_posts.schema.ts        # Blog posts
├── blog_authors.schema.ts      # Blog authors
├── blog_categories.schema.ts   # Blog categories
├── blog_comments.schema.ts     # Comments
├── blog_media.schema.ts        # Blog media
├── blog_organization.schema.ts # Publishers
├── blog_translations.schema.ts # Blog i18n
├── notification.schema.ts      # Notifications
├── services_listings.schema.ts # Service listings
├── services_categories.schema.ts # Service categories
├── services_reviews.schema.ts  # Reviews
├── services_availability.schema.ts # Schedules
├── services_bookings.schema.ts # Bookings
├── services_media.schema.ts    # Service media
└── services_translations.schema.ts # Service i18n
```

## Next Steps

<CardGroup cols={2}>
  <Card title="Migrations" icon="database" href="/development/database/migrations">
    Learn how to generate and run database migrations
  </Card>

  <Card title="Relationships" icon="diagram-project" href="/development/database/relationships">
    Understand table relationships and foreign keys
  </Card>
</CardGroup>