Skip to main content
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. 
// 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.
​
id
text
required
Primary key for the user
​
name
text
required
User’s display name
​
email
text
required
User’s email address (unique)
​
emailVerified
boolean
default:"false"
Whether the email has been verified
​
image
text
Profile image URL
​
username
text
Unique username (unique)
​
displayUsername
text
Display version of username
​
role
text
User role for permissions
​
banned
boolean
default:"false"
Whether the user is banned
​
banReason
text
Reason for ban
​
banExpires
timestamp
When the ban expires
​
createdAt
timestamp
required
Account creation timestamp
​
updatedAt
timestamp
required
Last update timestamp (auto-updated)

​
session

User session management with device tracking.
​
id
text
required
Primary key for the session
​
token
text
required
Session token (unique)
​
expiresAt
timestamp
required
Session expiration timestamp
​
userId
text
required
Foreign key to user table (cascade delete)
​
ipAddress
text
IP address of the session
​
userAgent
text
Browser/device user agent
​
activeOrganizationId
text
Currently active organization for this session
​
impersonatedBy
text
User ID if this session is an impersonation
​
createdAt
timestamp
required
Session creation timestamp
​
updatedAt
timestamp
required
Last activity timestamp
Indexes:
  • session_userId_idx on userId

​
account

OAuth provider accounts linked to users.
​
id
text
required
Primary key
​
accountId
text
required
Provider-specific account ID
​
providerId
text
required
OAuth provider identifier (google, github, etc.)
​
userId
text
required
Foreign key to user (cascade delete)
​
accessToken
text
OAuth access token
​
refreshToken
text
OAuth refresh token
​
idToken
text
OAuth ID token
​
accessTokenExpiresAt
timestamp
Access token expiration
​
refreshTokenExpiresAt
timestamp
Refresh token expiration
​
scope
text
OAuth scope granted
​
password
text
Hashed password for credential-based auth
​
createdAt
timestamp
required
Account link creation timestamp
​
updatedAt
timestamp
required
Last update timestamp
Indexes:
  • account_userId_idx on userId

​
verification

Email verification and password reset tokens.
​
id
text
required
Primary key
​
identifier
text
required
Email or identifier to verify
​
value
text
required
Verification token/code
​
expiresAt
timestamp
required
Token expiration timestamp
​
usedAt
timestamp
When the token was used (null if unused)
​
createdAt
timestamp
required
Token creation timestamp
​
updatedAt
timestamp
required
Last update timestamp
Indexes:
  • verification_identifier_idx on identifier

​
organization

Multi-tenant organization/workspace management.
​
id
text
required
Primary key
​
name
text
required
Organization name
​
slug
text
required
URL-friendly slug (unique)
Logo URL
​
createdAt
timestamp
required
Organization creation timestamp
​
metadata
text
Additional organization metadata (JSON string)
Indexes:
  • organization_slug_uidx unique index on slug

​
member

Organization membership and roles.
​
id
text
required
Primary key
​
organizationId
text
required
Foreign key to organization (cascade delete)
​
userId
text
required
Foreign key to user (cascade delete)
​
role
text
default:"member"
Member role (member, admin, owner, etc.)
​
createdAt
timestamp
required
Membership creation timestamp
Indexes:
  • member_organizationId_idx on organizationId
  • member_userId_idx on userId

​
invitation

Pending organization invitations.
​
id
text
required
Primary key
​
organizationId
text
required
Foreign key to organization (cascade delete)
​
email
text
required
Invited email address
​
role
text
Role to grant upon acceptance
​
status
text
default:"pending"
Invitation status (pending, accepted, rejected)
​
expiresAt
timestamp
required
Invitation expiration timestamp
​
inviterId
text
required
Foreign key to user who sent the invitation (cascade delete)
​
createdAt
timestamp
required
Invitation creation timestamp
Indexes:
  • invitation_organizationId_idx on organizationId
  • invitation_email_idx on email

​
rate_limit

Rate limiting tracking table.
​
id
text
required
Primary key
​
key
text
Rate limit key (IP, user ID, etc.)
​
count
integer
Request count
​
lastRequest
bigint
Timestamp of last request (milliseconds)

​
audit_logs

Audit trail for important actions.
​
id
text
required
Primary key
​
action
text
required
Action performed (e.g., “user.created”, “post.published”)
​
userId
text
User who performed the action
​
targetId
text
ID of the affected resource
​
ip
text
IP address of the request
​
userAgent
text
User agent string
​
data
jsonb
Additional action data as JSON
​
createdAt
timestamp
required
Action timestamp

​
Profile Table

​
profile

Extended user profile information beyond core auth.
​
id
text
required
Primary key
​
userId
text
required
Foreign key to user (unique)
​
username
text
Profile username
​
fullName
text
User’s full name
​
bio
text
Biography/about text
​
avatarUrl
text
Avatar image URL
​
location
text
User location
​
website
text
Personal website URL
​
preferredLanguage
text
default:"fr"
Preferred UI language
​
createdAt
timestamp
required
Profile creation timestamp
​
updatedAt
timestamp
required
Last update timestamp (auto-updated)

​
Blog Tables

​
blog_posts

Main blog post content table.
​
id
text
required
Primary key
​
slug
text
required
URL slug (unique)
​
status
text
required
Publication status (draft, published, archived)
​
organizationId
text
Foreign key to blog_organizations
​
publishedAt
timestamp
Publication timestamp
​
displayInHome
boolean
default:"false"
Show on homepage
​
displayInBlog
boolean
default:"true"
Show in blog listing
​
isFeatured
boolean
default:"false"
Featured post flag
​
readingTime
text
Estimated reading time
​
wordCount
text
Article word count
​
timeRequired
text
Schema.org time required field
​
allowComments
boolean
default:"true"
Whether comments are enabled
​
inLanguage
text
required
Primary language code
​
license
text
Content license
​
discussionUrl
text
External discussion URL
​
url
text
Canonical URL
​
identifier
text
External identifier
​
createdAt
timestamp
required
Post creation timestamp
​
updatedAt
timestamp
required
Last update timestamp
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).
​
postId
text
required
Foreign key to blog_posts
​
authorId
text
required
Foreign key to blog_authors
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).
​
postId
text
required
Foreign key to blog_posts
​
categoryId
text
required
Foreign key to blog_categories
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).
​
postId
text
required
Foreign key to blog_posts
​
mediaId
text
required
Foreign key to blog_media
​
type
text
required
Media type (cover, inline, etc.)
​
position
text
Position/order in post
Indexes:
  • idx_blog_post_media_post on postId
  • idx_blog_post_media_media on mediaId

​
blog_authors

Blog author profiles.
​
id
text
required
Primary key
​
slug
text
required
URL slug (unique)
​
givenName
jsonb
First name (multilingual)
​
familyName
jsonb
Last name (multilingual)
​
displayName
jsonb
required
Display name (multilingual)
​
bio
jsonb
Biography (multilingual)
​
jobTitle
jsonb
Job title (multilingual)
​
email
text
Contact email (unique)
​
avatarId
text
Foreign key to blog_media
​
avatarUrl
text
Direct avatar URL
​
website
text
Personal website
​
sameAs
jsonb
Social media links array
​
worksForId
text
Foreign key to blog_organizations
​
displayInHome
boolean
default:"false"
Show on homepage
​
displayInBlog
boolean
default:"true"
Show in blog
​
isFeatured
boolean
default:"false"
Featured author flag
​
seoTitle
jsonb
SEO title (multilingual)
​
seoDescription
jsonb
SEO description (multilingual)
​
seoKeywords
jsonb
SEO keywords (multilingual)
​
canonicalUrl
jsonb
Canonical URL (multilingual)
​
createdAt
timestamp
required
Creation timestamp
​
updatedAt
timestamp
required
Update timestamp
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.
​
id
text
required
Primary key
​
slug
text
required
URL slug (unique)
​
name
jsonb
required
Category name (multilingual)
​
description
jsonb
Category description (multilingual)
​
featuredImageId
text
Foreign key to blog_media
​
displayInHome
boolean
default:"false"
Show on homepage
​
displayInMenu
boolean
default:"true"
Show in navigation menu
​
displayInBlog
boolean
default:"true"
Show in blog
​
isFeatured
boolean
default:"false"
Featured category flag
​
parentId
text
Foreign key to parent category (self-reference)
​
seoTitle
jsonb
SEO title (multilingual)
​
seoDescription
jsonb
SEO description (multilingual)
​
seoKeywords
jsonb
SEO keywords (multilingual)
​
canonicalUrl
jsonb
Canonical URL (multilingual)
​
createdAt
timestamp
required
Creation timestamp
​
updatedAt
timestamp
required
Update timestamp
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.
​
id
text
required
Primary key
​
postId
text
required
ID of the related entity
​
postType
text
required
Type of entity (blog, place, event, hike, classified)
​
parentId
text
Foreign key to parent comment for threading
​
authorName
text
required
Comment author name
​
authorEmail
text
required
Comment author email
​
content
jsonb
required
Comment content (multilingual)
​
rating
integer
default:"0"
Optional rating (0-5)
​
status
text
required
Moderation status (pending, approved, rejected)
​
inLanguage
text
required
Comment language
​
createdAt
timestamp
required
Creation timestamp
​
updatedAt
timestamp
required
Update timestamp
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.
​
id
text
required
Primary key
​
url
text
required
Media URL
​
contentUrl
text
Alternative content URL
​
type
text
required
Media type (image, video, audio)
​
encodingFormat
text
MIME type
​
width
text
Image/video width
​
height
text
Image/video height
​
duration
text
Video/audio duration
​
license
text
Media license
​
copyrightHolder
text
Copyright holder name
​
caption
jsonb
Media caption (multilingual)
​
description
jsonb
Media description (multilingual)
​
alt
jsonb
Alt text for accessibility (multilingual)
​
thumbnailUrl
text
Thumbnail URL
​
createdAt
timestamp
required
Upload timestamp
​
updatedAt
timestamp
required
Update timestamp
Indexes:
  • idx_blog_media_type on type
  • idx_blog_media_url on url

​
blog_translations

Multilingual translations for blog posts.
​
id
text
required
Primary key
​
postId
text
required
Foreign key to blog_posts
​
inLanguage
text
required
Language code (fr, en, etc.)
​
headline
jsonb
required
Post headline (translated)
​
alternativeHeadline
jsonb
Alternative headline
​
articleBody
jsonb
required
Full article content (translated)
​
excerpt
jsonb
required
Post excerpt (translated)
​
seoTitle
jsonb
SEO title (translated)
​
seoDescription
jsonb
SEO description (translated)
​
seoKeywords
jsonb
SEO keywords (translated)
​
canonicalUrl
jsonb
Canonical URL (translated)
​
createdAt
timestamp
required
Translation creation timestamp
​
updatedAt
timestamp
required
Translation update timestamp
Indexes:
  • idx_blog_translations_post on postId
  • idx_blog_translations_language on inLanguage

​
blog_organizations

Schema.org-compatible organization profiles for blog publishers.
​
isActive
boolean
default:"true"
Active status
​
isFeatured
boolean
default:"false"
Featured flag
​
createdAt
timestamp
required
Creation timestamp
​
updatedAt
timestamp
required
Update timestamp (auto-updated)

​
Services Tables

​
services_listings

Service marketplace listings.
​
id
text
required
Primary key
​
slug
text
required
URL slug (unique)
​
categoryId
text
Foreign key to services_categories
​
providerId
text
required
Service provider user ID
​
organizationId
text
Foreign key to blog_organizations
​
status
text
default:"pending_review"
Listing status (pending_review, active, inactive)
​
basePrice
text
Base price
​
priceType
text
Pricing model (fixed, hourly, etc.)
​
currency
text
default:"EUR"
Currency code
​
durationMinutes
integer
Service duration in minutes
​
isMobile
boolean
default:"false"
Mobile service flag
​
maxParticipants
integer
Maximum participants
​
bookingAdvanceHours
integer
Minimum advance booking hours
​
cancellationHours
integer
Cancellation notice hours
​
isActive
boolean
default:"true"
Active status
​
isFeatured
boolean
default:"false"
Featured listing flag
​
displayInHome
boolean
default:"false"
Show on homepage
​
allowReviews
boolean
default:"true"
Allow customer reviews
​
inLanguage
text
default:"fr"
Primary language
​
createdAt
timestamp
required
Creation timestamp
​
updatedAt
timestamp
required
Update timestamp
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
Join table linking services to media.
​
serviceId
text
required
Foreign key to services_listings
​
mediaId
text
required
Foreign key to services_media
​
type
text
required
Media type (cover, gallery)
​
position
text
Display position/order
Indexes:
  • idx_services_media_links_service on serviceId
  • idx_services_media_links_media on mediaId

​
services_categories

Service category taxonomy with hierarchy.
​
id
text
required
Primary key
​
slug
text
required
URL slug (unique)
​
name
jsonb
required
Category name (multilingual)
​
description
jsonb
Category description (multilingual)
​
icon
text
Icon identifier
​
featuredImageId
text
Foreign key to services_media
​
parentId
text
Parent category ID (self-reference)
​
sortOrder
integer
default:"0"
Display sort order
​
displayInHome
boolean
default:"false"
Show on homepage
​
displayInMenu
boolean
default:"true"
Show in menu
​
isActive
boolean
default:"true"
Active status
​
isFeatured
boolean
default:"false"
Featured flag
​
seoTitle
jsonb
SEO title (multilingual)
​
seoDescription
jsonb
SEO description (multilingual)
​
createdAt
timestamp
required
Creation timestamp
​
updatedAt
timestamp
required
Update timestamp
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.
​
id
text
required
Primary key
​
serviceId
text
required
Foreign key to services_listings
​
parentId
text
Parent review ID for replies
​
authorName
text
required
Reviewer name
​
authorEmail
text
required
Reviewer email
​
authorId
text
Reviewer user ID
​
content
jsonb
required
Review content (multilingual)
​
rating
integer
required
Rating (1-5)
​
status
text
default:"pending"
Moderation status (pending, approved, rejected)
​
inLanguage
text
required
Review language
​
createdAt
timestamp
required
Review creation timestamp
​
updatedAt
timestamp
required
Update timestamp
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.
​
id
text
required
Primary key
​
serviceId
text
required
Foreign key to services_listings
​
dayOfWeek
integer
required
Day of week (0=Sunday, 6=Saturday)
​
startTime
text
required
Start time (HH:MM format)
​
endTime
text
required
End time (HH:MM format)
​
isAvailable
boolean
default:"true"
Availability flag
​
createdAt
timestamp
required
Creation timestamp
Indexes:
  • idx_services_availability_unique unique on (serviceId, dayOfWeek, startTime)
  • idx_services_availability_service on serviceId

​
services_bookings

Service booking records.
​
id
text
required
Primary key
​
serviceId
text
required
Foreign key to services_listings
​
customerId
text
required
Customer user ID
​
providerId
text
required
Provider user ID
​
bookingDate
text
required
Booking date (YYYY-MM-DD)
​
bookingTime
text
required
Booking time (HH:MM)
​
durationMinutes
integer
required
Booking duration in minutes
​
totalPrice
text
Total booking price
​
currency
text
default:"EUR"
Currency code
​
status
text
default:"pending"
Booking status (pending, confirmed, cancelled, completed)
​
customerMessage
text
Message from customer
​
providerResponse
text
Response from provider
​
cancelledAt
timestamp
Cancellation timestamp
​
completedAt
timestamp
Completion timestamp
​
createdAt
timestamp
required
Booking creation timestamp
​
updatedAt
timestamp
required
Update timestamp
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.
​
id
text
required
Primary key
​
url
text
required
Media URL
​
contentUrl
text
Alternative content URL
​
type
text
required
Media type (image, video, audio)
​
encodingFormat
text
MIME type
​
width
text
Width in pixels
​
height
text
Height in pixels
​
duration
text
Video/audio duration
​
license
text
Media license
​
copyrightHolder
text
Copyright holder
​
caption
jsonb
Media caption (multilingual)
​
description
jsonb
Media description (multilingual)
​
alt
jsonb
Alt text (multilingual)
​
thumbnailUrl
text
Thumbnail URL
​
createdAt
timestamp
required
Upload timestamp
​
updatedAt
timestamp
required
Update timestamp
Indexes:
  • idx_services_media_type on type
  • idx_services_media_url on url

​
services_translations

Multilingual translations for service listings.
​
id
text
required
Primary key
​
serviceId
text
required
Foreign key to services_listings
​
inLanguage
text
required
Language code
​
title
jsonb
required
Service title (translated)
​
description
jsonb
required
Full description (translated)
​
shortDescription
jsonb
Short description (translated)
​
seoTitle
jsonb
SEO title (translated)
​
seoDescription
jsonb
SEO description (translated)
​
seoKeywords
jsonb
SEO keywords (translated)
​
canonicalUrl
jsonb
Canonical URL (translated)
​
createdAt
timestamp
required
Translation creation timestamp
​
updatedAt
timestamp
required
Update timestamp
Indexes:
  • idx_services_translations_service on serviceId
  • idx_services_translations_language on inLanguage

​
Notification Table

​
notification

User notification system.
​
id
text
required
Primary key
​
userId
text
required
Target user ID
​
type
text
required
Notification type (email, push, in-app, etc.)
​
title
text
required
Notification title
​
body
text
Notification body content
​
message
text
Additional message text
​
status
text
Delivery status (sent, pending, failed)
​
targetType
text
Type of related entity (post, booking, etc.)
​
targetId
text
ID of related entity
​
data
jsonb
Additional notification data as JSON
​
isRead
boolean
default:"false"
Read status
​
readAt
timestamp
When notification was read
​
createdAt
timestamp
required
Notification creation timestamp
​
updatedAt
timestamp
Update timestamp

​
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: 
// 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

Migrations

Learn how to generate and run database migrations

Relationships

Understand table relationships and foreign keys