Services Marketplace

Concordia’s Services Marketplace enables providers to list professional services with flexible scheduling, booking management, multi-language translations, and customer reviews.

Overview

The marketplace is built from:

services_listings.schema.ts - Core service listings
services_categories.schema.ts - Service categorization
services_translations.schema.ts - Multi-language content
services_availability.schema.ts - Availability schedules
services_bookings.schema.ts - Reservation system
services_reviews.schema.ts - Customer reviews

Service Listings

From src/database/schemas/services_listings.schema.ts:

Core Fields

text

required

Service unique identifier

text

required

Unique URL-friendly identifier

text

Reference to service category

text

required

User ID of the service provider

text

Optional organization offering this service

Service Status

text

default:"pending_review"

Listing status: pending_review, active, suspended, archived

boolean

default:true

Whether the service is currently accepting bookings

boolean

default:false

Featured services get prominent placement

boolean

default:false

Display on homepage

Pricing

text

Base price as string (for precise decimal handling)Example: "50.00"

text

Pricing model: hourly, fixed, free, negotiable

text

default:"EUR"

ISO 4217 currency code

Service Details

integer

Standard service duration in minutesExample: 60 for 1-hour service

boolean

default:false

Whether provider travels to customer location

integer

Maximum number of participants (for workshops/classes)Example: 10

Booking Policies

integer

Minimum hours in advance required for bookingExample: 24 (1 day notice)

integer

Hours before service start when free cancellation is allowedExample: 48 (2 days)

Content Settings

boolean

default:true

Enable customer reviews for this service

text

default:"fr"

Primary language of the service

Service Categories

From src/database/schemas/services_categories.schema.ts:

text

required

Category identifier

text

required

Unique URL slug

jsonb

required

Category name in multiple languages

{
  "fr": "Plomberie",
  "en": "Plumbing"
}

jsonb

Category description in multiple languages

text

Icon identifier (e.g., MDI icon name)Example: "mdi:wrench"

text

Reference to featured image

text

Parent category for hierarchical structure

integer

default:0

Display order within parent

boolean

default:false

Show on homepage

Show in navigation menu

boolean

default:true

Category is active and visible

boolean

default:false

Featured categories get special prominence

Categories are hierarchical via parentId and ordered by sortOrder for flexible taxonomy.

Multi-Language Translations

From src/database/schemas/services_translations.schema.ts:

text

required

Translation entry ID

text

required

Reference to parent service listing

text

required

ISO 639-1 language code

jsonb

required

Service title in this languageExample: {"fr": "Réparation de plomberie d'urgence"}

jsonb

required

Full service description

jsonb

Brief summary for listings

jsonb

SEO-optimized page title

jsonb

Meta description for search engines

jsonb

SEO keywords array

jsonb

Canonical URL for this translation

Availability Scheduling

From src/database/schemas/services_availability.schema.ts:

text

required

Availability entry ID

text

required

Reference to service listing

integer

required

Day of week: 0 (Sunday) to 6 (Saturday)

text

required

Start time in HH:MM formatExample: "09:00"

text

required

End time in HH:MM formatExample: "17:00"

boolean

default:true

Whether this time slot is available for booking

Unique Constraint

The schema enforces uniqueness on (serviceId, dayOfWeek, startTime) to prevent duplicate time slots:

uniqueIndex("idx_services_availability_unique").on(
  table.serviceId,
  table.dayOfWeek,
  table.startTime,
)

This allows providers to define recurring weekly schedules. Each service can have multiple availability windows per day.

Bookings & Reservations

From src/database/schemas/services_bookings.schema.ts:

Booking Fields

text

required

Booking unique identifier

text

required

Reference to booked service

text

required

User ID of the customer

text

required

User ID of the service provider

Booking Schedule

text

required

Date of service in YYYY-MM-DD formatExample: "2026-03-15"

text

required

Time of service in HH:MM formatExample: "14:00"

integer

required

Duration of this booking in minutesExample: 90

Payment

text

Final price as string for precisionExample: "75.00"

text

default:"EUR"

ISO 4217 currency code

Booking Status

text

default:"pending"

Booking status: pending, confirmed, completed, cancelled, no_show

Communication

text

Message from customer to provider

text

Provider’s response to booking

Lifecycle Timestamps

timestamp

When booking was cancelled (if applicable)

timestamp

When service was completed

timestamp

required

When booking was created

timestamp

required

Last modification time

Reviews & Ratings

From src/database/schemas/services_reviews.schema.ts:

text

required

Review identifier

text

required

Service being reviewed

text

Parent review ID for threaded replies (provider responses)

Author Information

text

required

Reviewer’s display name

text

required

Reviewer’s email (for notifications)

text

Optional authenticated user ID

Review Content

jsonb

required

Review text as structured JSON

integer

required

Star rating (typically 1-5)

text

required

Language code of the review

Moderation

text

default:"pending"

Review status: pending, approved, rejected

Reviews require moderation before being visible to prevent spam and inappropriate content.

Threaded Replies

The schema supports threaded discussions via parentId:

export const servicesReviewsRelations = relations(servicesReviews, ({ one, many }) => ({
  service: one(servicesListings, {
    fields: [servicesReviews.serviceId],
    references: [servicesListings.id],
  }),
  parent: one(servicesReviews, {
    fields: [servicesReviews.parentId],
    references: [servicesReviews.id],
    relationName: "review_replies",
  }),
  replies: many(servicesReviews, {
    relationName: "review_replies",
  }),
}));

Providers can respond to reviews by creating a reply with parentId set to the original review.

Media Management

From services_listings.schema.ts, services can have multiple media items:

export const servicesMediaLinks = pgTable("services_media_links", {
  serviceId: text("service_id").notNull(),
  mediaId: text("media_id").notNull(),
  type: text("type").notNull(), // "cover", "gallery"
  position: text("position"),
});

text

required

Media usage type: cover for main image, gallery for additional images

text

Display order for gallery images

Database Indexes

Performance optimizations from schema files:

Listings Indexes

CREATE UNIQUE INDEX idx_services_listings_slug ON services_listings(slug);
CREATE INDEX idx_services_listings_status ON services_listings(status);
CREATE INDEX idx_services_listings_category ON services_listings(category_id);
CREATE INDEX idx_services_listings_provider ON services_listings(provider_id);
CREATE INDEX idx_services_listings_featured ON services_listings(is_featured);
CREATE INDEX idx_services_listings_active ON services_listings(is_active);

Bookings Indexes

CREATE INDEX idx_services_bookings_service ON services_bookings(service_id);
CREATE INDEX idx_services_bookings_customer ON services_bookings(customer_id);
CREATE INDEX idx_services_bookings_provider ON services_bookings(provider_id);
CREATE INDEX idx_services_bookings_status ON services_bookings(status);
CREATE INDEX idx_services_bookings_date ON services_bookings(booking_date);

Reviews Indexes

CREATE INDEX idx_services_reviews_service ON services_reviews(service_id);
CREATE INDEX idx_services_reviews_status ON services_reviews(status);
CREATE INDEX idx_services_reviews_rating ON services_reviews(rating);
CREATE INDEX idx_services_reviews_parent ON services_reviews(parent_id);

Integration with Places

From concordia-specs.md (lines 955-980), services can be linked to physical places:

place_id: uuid FK → place.id  // Professional location

This enables:

Professional services tied to a business location
Geographic search for local services
Reviews aggregation across place and services

Marketplace Flow

Provider creates service → Status: pending_review
Admin moderates → Status: active or rejected
Provider sets availability → Define weekly schedule
Customer books service → Creates booking with pending status
Provider confirms → Booking status: confirmed
Service completed → Booking status: completed, completedAt set
Customer reviews → Review created with pending status
Moderator approves → Review status: approved

Best Practices

Pricing Strategy

Use priceType to set customer expectations
Store prices as strings for decimal precision
Always specify currency for international users

Availability Management

Define clear weekly schedules
Use isAvailable: false for temporary blocks
Consider bookingAdvanceHours for preparation time

Booking Policies

Set reasonable cancellationHours for flexibility
Use customerMessage for special requirements
Update status promptly for good customer experience

Review Moderation

Review all pending reviews within 24 hours
Allow providers to respond via threaded replies
Use approved status only for legitimate reviews

​Services Marketplace

​Overview

​Service Listings

​Core Fields

​Service Status

​Pricing

​Service Details

​Booking Policies

​Content Settings

​Service Categories

​Multi-Language Translations

​Availability Scheduling

​Unique Constraint

​Bookings & Reservations

​Booking Fields

​Booking Schedule

​Payment

​Booking Status

​Communication

​Lifecycle Timestamps

​Reviews & Ratings

​Author Information

​Review Content

​Moderation

​Threaded Replies

​Media Management

​Database Indexes

​Listings Indexes

​Bookings Indexes

​Reviews Indexes

​Integration with Places

​Marketplace Flow

​Best Practices

​Pricing Strategy

​Availability Management

​Booking Policies

​Review Moderation

Services Marketplace

Overview

Service Listings

Core Fields

Service Status

Pricing

Service Details

Booking Policies

Content Settings

Service Categories

Multi-Language Translations

Availability Scheduling

Unique Constraint

Bookings & Reservations

Booking Fields

Booking Schedule

Payment

Booking Status

Communication

Lifecycle Timestamps

Reviews & Ratings

Author Information

Review Content

Moderation

Threaded Replies

Media Management

Database Indexes

Listings Indexes

Bookings Indexes

Reviews Indexes

Integration with Places

Marketplace Flow

Best Practices

Pricing Strategy

Availability Management

Booking Policies

Review Moderation