Skip to main content
Concordia’s database uses Drizzle ORM relations to define type-safe relationships between tables. This page documents the key relationships and how they’re structured.

​
Relationship Patterns

The database uses three main relationship patterns:
  1. One-to-One - Single record references another (e.g., User → Profile)
  2. One-to-Many - One record has multiple children (e.g., User → Sessions)
  3. Many-to-Many - Records relate through join tables (e.g., Posts ↔ Authors)

​
Authentication Relationships

​
User Relations

The user table is central to authentication and serves as the root for most relationships: 
export const userRelations = relations(user, ({ many }) => ({
  sessions: many(session),
  accounts: many(account),
  members: many(member),
  invitations: many(invitation),
}));

user
├── sessions (one-to-many)
│   └── CASCADE DELETE
├── accounts (one-to-many)
│   └── CASCADE DELETE
├── members (one-to-many)
│   └── CASCADE DELETE
└── invitations (one-to-many)
    └── CASCADE DELETE

​
Session Relations

export const sessionRelations = relations(session, ({ one }) => ({
  user: one(user, {
    fields: [session.userId],
    references: [user.id],
  }),
}));
Foreign Key:
  • session.userId → user.id (CASCADE DELETE)
Each session belongs to exactly one user. Sessions are automatically deleted when the user is removed.

​
Account Relations

export const accountRelations = relations(account, ({ one }) => ({
  user: one(user, {
    fields: [account.userId],
    references: [user.id],
  }),
}));
Foreign Key:
  • account.userId → user.id (CASCADE DELETE)
OAuth accounts are linked to a single user. Multiple accounts (Google, GitHub, etc.) can belong to one user.

​
Organization Relations

export const organizationRelations = relations(organization, ({ many }) => ({
  members: many(member),
  invitations: many(invitation),
}));

organization
├── members (one-to-many)
│   └── CASCADE DELETE
└── invitations (one-to-many)
    └── CASCADE DELETE

​
Member Relations

export const memberRelations = relations(member, ({ one }) => ({
  organization: one(organization, {
    fields: [member.organizationId],
    references: [organization.id],
  }),
  user: one(user, {
    fields: [member.userId],
    references: [user.id],
  }),
}));
Foreign Keys:
  • member.organizationId → organization.id (CASCADE DELETE)
  • member.userId → user.id (CASCADE DELETE)
Members form a junction between users and organizations: 
user ←→ member ←→ organization
Deleting either the user or organization removes the membership.

​
Invitation Relations

export const invitationRelations = relations(invitation, ({ one }) => ({
  organization: one(organization, {
    fields: [invitation.organizationId],
    references: [organization.id],
  }),
  user: one(user, {
    fields: [invitation.inviterId],
    references: [user.id],
  }),
}));
Foreign Keys:
  • invitation.organizationId → organization.id (CASCADE DELETE)
  • invitation.inviterId → user.id (CASCADE DELETE)
Invitations track who invited someone to which organization.

​
Profile Relationships

​
User → Profile (One-to-One)

While not explicitly defined with relations in the schema, the profile table has a unique constraint on userId: 
export const profile = pgTable("profile", {
  id: text("id").primaryKey(),
  userId: text("user_id").notNull().unique(),
  // ... other fields
});
Relationship: 
user (1) ←→ (1) profile
Each user has exactly one profile. The userId unique constraint enforces this.

​
Blog Relationships

​
Blog Post Relations

export const blogPostsRelations = relations(blogPosts, ({ one, many }) => ({
  organization: one(blogOrganizations, {
    fields: [blogPosts.organizationId],
    references: [blogOrganizations.id]
  }),
  authors: many(blogPostAuthors),
  categories: many(blogPostCategories),
  media: many(blogPostMedia),
  translations: many(blogTranslations),
  comments: many(blogComments),
}));

blogPosts
├── organization (many-to-one)
├── authors (many-to-many via blogPostAuthors)
├── categories (many-to-many via blogPostCategories)
├── media (many-to-many via blogPostMedia)
├── translations (one-to-many)
└── comments (one-to-many)

​
Many-to-Many: Posts ↔ Authors

Join Table: blog_post_authors 
export const blogPostAuthors = pgTable("blog_post_authors", {
  postId: text("post_id").notNull(),
  authorId: text("author_id").notNull(),
});

export const blogPostAuthorsRelations = relations(blogPostAuthors, ({ one }) => ({
  post: one(blogPosts, {
    fields: [blogPostAuthors.postId],
    references: [blogPosts.id],
  }),
  author: one(blogAuthors, {
    fields: [blogPostAuthors.authorId],
    references: [blogAuthors.id],
  }),
}));
Relationship: 
blogPosts (many) ←→ blog_post_authors ←→ (many) blogAuthors
A post can have multiple authors, and an author can write multiple posts.

​
Many-to-Many: Posts ↔ Categories

Join Table: blog_post_categories 
export const blogPostCategories = pgTable("blog_post_categories", {
  postId: text("post_id").notNull(),
  categoryId: text("category_id").notNull(),
});
Relationship: 
blogPosts (many) ←→ blog_post_categories ←→ (many) blogCategories
Posts can belong to multiple categories, and categories contain multiple posts.

​
Many-to-Many: Posts ↔ Media

Join Table: blog_post_media 
export const blogPostMedia = pgTable("blog_post_media", {
  postId: text("post_id").notNull(),
  mediaId: text("media_id").notNull(),
  type: text("type").notNull(),
  position: text("position"),
});
Relationship: 
blogPosts (many) ←→ blog_post_media ←→ (many) blogMedia
Posts can have multiple media attachments (images, videos), with metadata about type and position.

​
Blog Author Relations

export const blogAuthorsRelations = relations(blogAuthors, ({ one }) => ({
  avatar: one(blogMedia, {
    fields: [blogAuthors.avatarId],
    references: [blogMedia.id]
  }),
  organization: one(blogOrganizations, {
    fields: [blogAuthors.worksForId],
    references: [blogOrganizations.id]
  }),
}));
Foreign Keys:
  • blogAuthors.avatarId → blogMedia.id
  • blogAuthors.worksForId → blogOrganizations.id

​
Blog Category Relations

export const blogCategoriesRelations = relations(blogCategories, ({ one }) => ({
  featuredImage: one(blogMedia, {
    fields: [blogCategories.featuredImageId],
    references: [blogMedia.id]
  }),
}));
Hierarchical Categories: Categories support self-referencing hierarchy: 
export const blogCategories = pgTable("blog_categories", {
  id: text("id").primaryKey(),
  parentId: text("parent_id"), // Self-reference
  // ... other fields
});

Category: Technology
├── Category: Web Development
│   ├── Category: React
│   └── Category: Vue
└── Category: Mobile
    └── Category: React Native

​
Blog Comments Relations

export const blogCommentsRelations = relations(blogComments, ({ one, many }) => ({
  parent: one(blogComments, {
    fields: [blogComments.parentId],
    references: [blogComments.id],
    relationName: "comment_replies",
  }),
  replies: many(blogComments, {
    relationName: "comment_replies",
  }),
}));
Self-Referencing Hierarchy: Comments support threading through self-reference: 
Comment 1
├── Reply 1.1
│   └── Reply 1.1.1
└── Reply 1.2

​
Blog Translations Relations

export const blogTranslationsRelations = relations(blogTranslations, ({ one }) => ({
  post: one(blogPosts, {
    fields: [blogTranslations.postId],
    references: [blogPosts.id]
  }),
}));
Foreign Key:
  • blogTranslations.postId → blogPosts.id
Relationship: 
blogPosts (1) → (many) blogTranslations
Each post can have multiple translations (French, English, Spanish, etc.).

​
Services Relationships

​
Service Listing Relations

export const servicesListingsRelations = relations(servicesListings, ({ one, many }) => ({
  category: one(servicesCategories, {
    fields: [servicesListings.categoryId],
    references: [servicesCategories.id],
  }),
  organization: one(blogOrganizations, {
    fields: [servicesListings.organizationId],
    references: [blogOrganizations.id],
  }),
  translations: many(servicesTranslations),
  media: many(servicesMediaLinks),
  reviews: many(servicesReviews),
  availability: many(servicesAvailability),
  bookings: many(servicesBookings),
}));

servicesListings
├── category (many-to-one)
├── organization (many-to-one)
├── translations (one-to-many)
├── media (many-to-many via servicesMediaLinks)
├── reviews (one-to-many)
├── availability (one-to-many)
└── bookings (one-to-many)

​
Many-to-Many: Services ↔ Media

Join Table: services_media_links 
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"),
});

export const servicesMediaLinksRelations = relations(servicesMediaLinks, ({ one }) => ({
  service: one(servicesListings, {
    fields: [servicesMediaLinks.serviceId],
    references: [servicesListings.id],
  }),
  media: one(servicesMedia, {
    fields: [servicesMediaLinks.mediaId],
    references: [servicesMedia.id],
  }),
}));
Relationship: 
servicesListings (many) ←→ services_media_links ←→ (many) servicesMedia

​
Service Category Relations

export const servicesCategoriesRelations = relations(servicesCategories, ({ one }) => ({
  featuredImage: one(servicesMedia, {
    fields: [servicesCategories.featuredImageId],
    references: [servicesMedia.id],
  }),
}));
Hierarchical Categories: Like blog categories, service categories support hierarchy: 
export const servicesCategories = pgTable("services_categories", {
  id: text("id").primaryKey(),
  parentId: text("parent_id"), // Self-reference
  // ... other fields
});

​
Service Reviews Relations

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",
  }),
}));
Foreign Keys:
  • servicesReviews.serviceId → servicesListings.id
  • servicesReviews.parentId → servicesReviews.id (self-reference)
Self-Referencing for Replies: 
Review: "Great service!"
├── Reply: "Thank you!"
└── Reply: "Glad you enjoyed it"

​
Service Availability Relations

export const servicesAvailabilityRelations = relations(servicesAvailability, ({ one }) => ({
  service: one(servicesListings, {
    fields: [servicesAvailability.serviceId],
    references: [servicesListings.id],
  }),
}));
Foreign Key:
  • servicesAvailability.serviceId → servicesListings.id
Relationship: 
servicesListings (1) → (many) servicesAvailability
A service has multiple availability slots (Monday 9-5, Tuesday 10-3, etc.).

​
Service Bookings Relations

export const servicesBookingsRelations = relations(servicesBookings, ({ one }) => ({
  service: one(servicesListings, {
    fields: [servicesBookings.serviceId],
    references: [servicesListings.id],
  }),
}));
Foreign Key:
  • servicesBookings.serviceId → servicesListings.id
Additional References (not enforced by FK):
  • servicesBookings.customerId → user.id
  • servicesBookings.providerId → user.id

​
Service Translations Relations

export const servicesTranslationsRelations = relations(servicesTranslations, ({ one }) => ({
  service: one(servicesListings, {
    fields: [servicesTranslations.serviceId],
    references: [servicesListings.id],
  }),
}));
Foreign Key:
  • servicesTranslations.serviceId → servicesListings.id

​
Complete Relationship Diagram

​
Authentication & Users

user
├── session [1:N, CASCADE]
├── account [1:N, CASCADE]
├── profile [1:1, UNIQUE userId]
├── member [1:N, CASCADE]
│   └── organization [N:1, CASCADE]
│       ├── member [1:N]
│       └── invitation [1:N, CASCADE]
└── invitation (as inviter) [1:N, CASCADE]

​
Blog System

blogPosts
├── blogOrganizations [N:1]
├── blogTranslations [1:N]
├── blogComments [1:N]
│   └── blogComments (parent) [self-reference]
├── blogPostAuthors [N:M]
│   └── blogAuthors [N:1]
│       ├── blogMedia (avatar) [N:1]
│       └── blogOrganizations [N:1]
├── blogPostCategories [N:M]
│   └── blogCategories [N:1]
│       ├── blogMedia (featuredImage) [N:1]
│       └── blogCategories (parent) [self-reference]
└── blogPostMedia [N:M]
    └── blogMedia [N:1]

​
Services Marketplace

servicesListings
├── servicesCategories [N:1]
│   ├── servicesMedia (featuredImage) [N:1]
│   └── servicesCategories (parent) [self-reference]
├── blogOrganizations [N:1]
├── servicesTranslations [1:N]
├── servicesMediaLinks [N:M]
│   └── servicesMedia [N:1]
├── servicesReviews [1:N]
│   └── servicesReviews (parent) [self-reference]
├── servicesAvailability [1:N]
└── servicesBookings [1:N]

​
Cascade Deletion Rules

​
User Deletion Cascades

When a user is deleted, these are automatically removed:
  • All sessions
  • All linked accounts (OAuth)
  • All organization memberships
  • All invitations sent by user

​
Organization Deletion Cascades

When an organization is deleted:
  • All members are removed
  • All pending invitations are removed

​
Post/Service Deletion

Note that blog posts and services don’t have explicit cascade rules defined at the database level, but should be handled at the application level:
  • Deleting a post should remove translations, comments, and join table entries
  • Deleting a service should remove reviews, bookings, and availability

​
Querying Relations with Drizzle

​
Basic Relation Query

import { db } from './database/drizzle';
import { user, session } from './database/schemas';

// Get user with all sessions
const userWithSessions = await db.query.user.findFirst({
  where: (user, { eq }) => eq(user.id, userId),
  with: {
    sessions: true,
  },
});

​
Nested Relations

// Get post with authors and their organizations
const postWithDetails = await db.query.blogPosts.findFirst({
  where: (post, { eq }) => eq(post.slug, 'my-post'),
  with: {
    authors: {
      with: {
        author: {
          with: {
            organization: true,
          },
        },
      },
    },
    categories: {
      with: {
        category: true,
      },
    },
    translations: true,
  },
});

​
Many-to-Many Query

// Get all posts by an author
const authorPosts = await db.query.blogAuthors.findFirst({
  where: (author, { eq }) => eq(author.slug, 'john-doe'),
  with: {
    posts: {
      with: {
        post: {
          with: {
            translations: true,
          },
        },
      },
    },
  },
});

​
Self-Referencing Query

// Get comment with all replies
const commentThread = await db.query.blogComments.findFirst({
  where: (comment, { eq }) => eq(comment.id, commentId),
  with: {
    replies: {
      with: {
        replies: true, // Nested replies
      },
    },
  },
});

​
Best Practices

​
1. Always Use Relations for Type Safety

Drizzle relations provide full TypeScript inference: 
// ✅ Type-safe relation query
const user = await db.query.user.findFirst({
  with: { sessions: true },
});
user?.sessions[0].token // TypeScript knows this is a string

// ❌ Manual join (no type safety)
const result = await db.execute(sql`
  SELECT * FROM user JOIN session ON user.id = session.user_id
`);

​
2. Use Cascade Deletes Carefully

Cascade deletes are powerful but dangerous:
  • Use CASCADE for dependent data (sessions, tokens)
  • Consider soft deletes for user-generated content
  • Always test deletion in development first

​
3. Optimize Nested Queries

Avoid over-fetching with selective includes: 
// ✅ Only fetch what you need
const post = await db.query.blogPosts.findFirst({
  with: {
    translations: {
      where: (t, { eq }) => eq(t.inLanguage, 'fr'),
    },
  },
});

// ❌ Fetches all nested data
const post = await db.query.blogPosts.findFirst({
  with: {
    authors: { with: { author: true } },
    categories: { with: { category: true } },
    media: { with: { media: true } },
    translations: true,
    comments: { with: { replies: true } },
  },
});

​
4. Use Indexes for Foreign Keys

All foreign key columns should be indexed: 
// ✅ Foreign key with index
export const session = pgTable(
  "session",
  {
    userId: text("user_id").notNull().references(() => user.id),
  },
  (table) => [index("session_userId_idx").on(table.userId)],
);

​
5. Handle Soft Deletes for Content

For user-generated content, consider soft deletes: 
export const blogPosts = pgTable("blog_posts", {
  id: text("id").primaryKey(),
  status: text("status").notNull(), // 'published', 'draft', 'deleted'
  deletedAt: timestamp("deleted_at"),
});

​
Next Steps

Schema Reference

View detailed schema documentation

Migrations

Learn how to create and apply migrations