Database Schema - 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.

// 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.

text

required

Primary key for the user

text

required

User’s display name

text

required

User’s email address (unique)

boolean

default:"false"

Whether the email has been verified

text

Profile image URL

text

Unique username (unique)

text

Display version of username

text

User role for permissions

boolean

default:"false"

Whether the user is banned

text

Reason for ban

timestamp

When the ban expires

timestamp

required

Account creation timestamp

timestamp

required

Last update timestamp (auto-updated)

session

User session management with device tracking.

text

required

Primary key for the session

text

required

Session token (unique)

timestamp

required

Session expiration timestamp

text

required

Foreign key to user table (cascade delete)

text

IP address of the session

text

Browser/device user agent

text

Currently active organization for this session

text

User ID if this session is an impersonation

timestamp

required

Session creation timestamp

timestamp

required

Last activity timestamp

Indexes:

session_userId_idx on userId

account

OAuth provider accounts linked to users.

text

required

Primary key

text

required

Provider-specific account ID

text

required

OAuth provider identifier (google, github, etc.)

text

required

Foreign key to user (cascade delete)

text

OAuth access token

text

OAuth refresh token

text

OAuth ID token

timestamp

Access token expiration

timestamp

Refresh token expiration

text

OAuth scope granted

text

Hashed password for credential-based auth

timestamp

required

Account link creation timestamp

timestamp

required

Last update timestamp

Indexes:

account_userId_idx on userId

verification

Email verification and password reset tokens.

text

required

Primary key

text

required

Email or identifier to verify

text

required

Verification token/code

timestamp

required

Token expiration timestamp

timestamp

When the token was used (null if unused)

timestamp

required

Token creation timestamp

timestamp

required

Last update timestamp

Indexes:

verification_identifier_idx on identifier

organization

Multi-tenant organization/workspace management.

text

required

Primary key

text

required

Organization name

text

required

URL-friendly slug (unique)

text

Logo URL

timestamp

required

Organization creation timestamp

text

Additional organization metadata (JSON string)

Indexes:

organization_slug_uidx unique index on slug

member

Organization membership and roles.

text

required

Primary key

text

required

Foreign key to organization (cascade delete)

text

required

Foreign key to user (cascade delete)

text

default:"member"

Member role (member, admin, owner, etc.)

timestamp

required

Membership creation timestamp

Indexes:

member_organizationId_idx on organizationId
member_userId_idx on userId

invitation

Pending organization invitations.

text

required

Primary key

text

required

Foreign key to organization (cascade delete)

text

required

Invited email address

text

Role to grant upon acceptance

text

default:"pending"

Invitation status (pending, accepted, rejected)

timestamp

required

Invitation expiration timestamp

text

required

Foreign key to user who sent the invitation (cascade delete)

timestamp

required

Invitation creation timestamp

Indexes:

invitation_organizationId_idx on organizationId
invitation_email_idx on email

rate_limit

Rate limiting tracking table.

text

required

Primary key

text

Rate limit key (IP, user ID, etc.)

integer

Request count

bigint

Timestamp of last request (milliseconds)

audit_logs

Audit trail for important actions.

text

required

Primary key

text

required

Action performed (e.g., “user.created”, “post.published”)

text

User who performed the action

text

ID of the affected resource

text

IP address of the request

text

User agent string

jsonb

Additional action data as JSON

timestamp

required

Action timestamp

Profile Table

profile

Extended user profile information beyond core auth.

text

required

Primary key

text

required

Foreign key to user (unique)

text

Profile username

text

User’s full name

text

Biography/about text

text

Avatar image URL

text

User location

text

Personal website URL

text

default:"fr"

Preferred UI language

timestamp

required

Profile creation timestamp

timestamp

required

Last update timestamp (auto-updated)

Blog Tables

blog_posts

Main blog post content table.

text

required

Primary key

text

required

URL slug (unique)

text

required

Publication status (draft, published, archived)

text

Foreign key to blog_organizations

timestamp

Publication timestamp

boolean

default:"false"

Show on homepage

boolean

default:"true"

Show in blog listing

boolean

default:"false"

blog_post_authors

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

text

required

Foreign key to blog_posts

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).

text

required

Foreign key to blog_posts

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).

text

required

Foreign key to blog_posts

text

required

Foreign key to blog_media

text

required

Media type (cover, inline, etc.)

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.

text

required

Primary key

text

required

URL slug (unique)

jsonb

First name (multilingual)

jsonb

Last name (multilingual)

jsonb

required

Display name (multilingual)

jsonb

Biography (multilingual)

jsonb

Job title (multilingual)

text

Contact email (unique)

text

Foreign key to blog_media

text

Direct avatar URL

text

Personal website

jsonb

Social media links array

text

Foreign key to blog_organizations

boolean

default:"false"

Show on homepage

boolean

default:"true"

Show in blog

boolean

default:"false"

Featured author flag

jsonb

SEO title (multilingual)

jsonb

SEO description (multilingual)

jsonb

SEO keywords (multilingual)

jsonb

Canonical URL (multilingual)

timestamp

required

Creation timestamp

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.

text

required

Primary key

text

required

URL slug (unique)

jsonb

required

Category name (multilingual)

jsonb

Category description (multilingual)

text

Foreign key to blog_media

boolean

default:"false"

Show on homepage

Show in navigation menu

boolean

default:"true"

Show in blog

boolean

default:"false"

Featured category flag

text

Foreign key to parent category (self-reference)

jsonb

SEO title (multilingual)

jsonb

SEO description (multilingual)

jsonb

SEO keywords (multilingual)

jsonb

Canonical URL (multilingual)

timestamp

required

Creation timestamp

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.

text

required

Primary key

text

required

ID of the related entity

text

required

Type of entity (blog, place, event, hike, classified)

text

Foreign key to parent comment for threading

text

required

Comment author name

text

required

Comment author email

jsonb

required

Comment content (multilingual)

integer

default:"0"

Optional rating (0-5)

text

required

Moderation status (pending, approved, rejected)

text

required

Comment language

timestamp

required

Creation timestamp

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.

text

required

Primary key

text

required

Media URL

text

Alternative content URL

text

required

Media type (image, video, audio)

text

MIME type

text

Image/video width

text

Image/video height

text

Video/audio duration

text

Media license

text

jsonb

Media caption (multilingual)

jsonb

Media description (multilingual)

jsonb

Alt text for accessibility (multilingual)

text

Thumbnail URL

timestamp

required

Upload timestamp

timestamp

required

Update timestamp

Indexes:

idx_blog_media_type on type
idx_blog_media_url on url

blog_translations

Multilingual translations for blog posts.

text

required

Primary key

text

required

Foreign key to blog_posts

text

required

Language code (fr, en, etc.)

jsonb

required

Post headline (translated)

jsonb

Alternative headline

jsonb

required

Full article content (translated)

jsonb

required

Post excerpt (translated)

jsonb

SEO title (translated)

jsonb

SEO description (translated)

jsonb

SEO keywords (translated)

jsonb

Canonical URL (translated)

timestamp

required

Translation creation timestamp

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.

Show Identity Fields

text

required

Primary key

text

required

Organization name

text

required

URL slug (unique)

jsonb

Alternative names array

jsonb

Organization description (multilingual)

text

Organization website

text

Logo URL

text

Main image URL

jsonb

Organization slogan (multilingual)

Show Contact Information

text

Contact email

text

Contact phone number

text

Fax number

jsonb

Physical address object

jsonb

Contact points array

Show Legal & Identifiers

jsonb

Legal name (multilingual)

text

Tax identifier

text

VAT number

text

Legal Entity Identifier

text

DUNS number

text

ISIC industry classification

text

NAICS industry code

text

Nonprofit status

Show People & Structure

jsonb

Founder(s)

timestamp

Founding date

text

Founding location

integer

Employee count

jsonb

Employee list

jsonb

Alumni list

text

Parent organization ID

jsonb

Sub-organization IDs

jsonb

Departments array

Show Editorial Policies (NewsMediaOrganization)

text

Publishing principles URL

text

Feedback policy URL

text

Corrections policy URL

text

Diversity policy URL

text

Ethics policy URL

text

Masthead URL

text

Fact-checking policy URL

boolean

default:"true"

Active status

boolean

default:"false"

Featured flag

timestamp

required

Creation timestamp

timestamp

required

Update timestamp (auto-updated)

Services Tables

services_listings

Service marketplace listings.

text

required

Primary key

text

required

URL slug (unique)

text

Foreign key to services_categories

text

required

Service provider user ID

text

Foreign key to blog_organizations

text

default:"pending_review"

Listing status (pending_review, active, inactive)

text

Base price

text

Pricing model (fixed, hourly, etc.)

text

default:"EUR"

Currency code

integer

Service duration in minutes

boolean

default:"false"

Mobile service flag

integer

Maximum participants

integer

Minimum advance booking hours

integer

Cancellation notice hours

boolean

default:"true"

Active status

boolean

default:"false"

Featured listing flag

boolean

default:"false"

Show on homepage

boolean

default:"true"

Allow customer reviews

text

default:"fr"

Primary language

timestamp

required

Creation timestamp

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

services_media_links

Join table linking services to media.

text

required

Foreign key to services_listings

text

required

Foreign key to services_media

text

required

Media type (cover, gallery)

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.

text

required

Primary key

text

required

URL slug (unique)

jsonb

required

Category name (multilingual)

jsonb

Category description (multilingual)

text

Icon identifier

text

Foreign key to services_media

text

Parent category ID (self-reference)

integer

default:"0"

Display sort order

boolean

default:"false"

Show on homepage

Show in menu

boolean

default:"true"

Active status

boolean

default:"false"

Featured flag

jsonb

SEO title (multilingual)

jsonb

SEO description (multilingual)

timestamp

required

Creation timestamp

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.

text

required

Primary key

text

required

Foreign key to services_listings

text

Parent review ID for replies

text

required

Reviewer name

text

required

Reviewer email

text

Reviewer user ID

jsonb

required

Review content (multilingual)

integer

required

Rating (1-5)

text

default:"pending"

Moderation status (pending, approved, rejected)

text

required

Review language

timestamp

required

Review creation timestamp

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.

text

required

Primary key

text

required

Foreign key to services_listings

integer

required

Day of week (0=Sunday, 6=Saturday)

text

required

Start time (HH:MM format)

text

required

End time (HH:MM format)

boolean

default:"true"

Availability flag

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.

text

required

Primary key

text

required

Foreign key to services_listings

text

required

Customer user ID

text

required

Provider user ID

text

required

Booking date (YYYY-MM-DD)

text

required

Booking time (HH:MM)

integer

required

Booking duration in minutes

text

Total booking price

text

default:"EUR"

Currency code

text

default:"pending"

Booking status (pending, confirmed, cancelled, completed)

text

Message from customer

text

Response from provider

timestamp

Cancellation timestamp

timestamp

Completion timestamp

timestamp

required

Booking creation timestamp

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.

text

required

Primary key

text

required

Media URL

text

Alternative content URL

text

required

Media type (image, video, audio)

text

MIME type

text

Width in pixels

text

Height in pixels

text

Video/audio duration

text

Media license

text

jsonb

Media caption (multilingual)

jsonb

Media description (multilingual)

jsonb

Alt text (multilingual)

text

Thumbnail URL

timestamp

required

Upload timestamp

timestamp

required

Update timestamp

Indexes:

idx_services_media_type on type
idx_services_media_url on url

services_translations

Multilingual translations for service listings.

text

required

Primary key

text

required

Foreign key to services_listings

text

required

Language code

jsonb

required

Service title (translated)

jsonb

required

Full description (translated)

jsonb

Short description (translated)

jsonb

SEO title (translated)

jsonb

SEO description (translated)

jsonb

SEO keywords (translated)

jsonb

Canonical URL (translated)

timestamp

required

Translation creation timestamp

timestamp

required

Update timestamp

Indexes:

idx_services_translations_service on serviceId
idx_services_translations_language on inLanguage

Notification Table

notification

User notification system.

text

required

Primary key

text

required

Target user ID

text

required

Notification type (email, push, in-app, etc.)

text

required

Notification title

text

Notification body content

text

Additional message text

text

Delivery status (sent, pending, failed)

text

Type of related entity (post, booking, etc.)

text

ID of related entity

jsonb

Additional notification data as JSON

boolean

default:"false"

Read status

timestamp

When notification was read

timestamp

required

Notification creation timestamp

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

​Schema Organization

​Authentication Tables

​user

​session

​account

​verification

​organization

​member

​invitation

​rate_limit

​audit_logs

​Profile Table

​profile

​Blog Tables

​blog_posts

​blog_post_authors

​blog_post_categories

​blog_post_media

​blog_authors

​blog_categories

​blog_comments

​blog_media

​blog_translations

​blog_organizations

​Services Tables

​services_listings

​services_media_links

​services_categories

​services_reviews

​services_availability

​services_bookings

​services_media

​services_translations

​Notification Table

​notification

​Data Types Reference

​Common Types

​JSONB Usage

​Cascade Deletion

​Schema Files

​Next Steps

Migrations

Relationships

Schema Organization

Authentication Tables

user

session

account

verification

organization

member

invitation

rate_limit

audit_logs

Profile Table

profile

Blog Tables

blog_posts

blog_post_authors

blog_post_categories

blog_post_media

blog_authors

blog_categories

blog_comments

blog_media

blog_translations

blog_organizations

Services Tables

services_listings

services_media_links

services_categories

services_reviews

services_availability

services_bookings

services_media

services_translations

Notification Table

notification

Data Types Reference

Common Types

JSONB Usage

Cascade Deletion

Schema Files

Next Steps