Table Relationships

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:

One-to-One - Single record references another (e.g., User → Profile)
One-to-Many - One record has multiple children (e.g., User → Sessions)
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),
}));

Diagram
Details

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"),
});

Architecture

Database

API

Documentation Index

​Relationship Patterns

​Authentication Relationships

​User Relations

​Session Relations

​Account Relations

​Organization Relations

​Member Relations

​Invitation Relations

​Profile Relationships

​User → Profile (One-to-One)

​Blog Relationships

​Blog Post Relations

​Many-to-Many: Posts ↔ Authors

​Many-to-Many: Posts ↔ Categories

​Many-to-Many: Posts ↔ Media

​Blog Author Relations

​Blog Category Relations

​Blog Comments Relations

​Blog Translations Relations

​Services Relationships

​Service Listing Relations

​Many-to-Many: Services ↔ Media

​Service Category Relations

​Service Reviews Relations

​Service Availability Relations

​Service Bookings Relations

​Service Translations Relations

​Complete Relationship Diagram

​Authentication & Users

​Blog System

​Services Marketplace

​Cascade Deletion Rules

​User Deletion Cascades

​Organization Deletion Cascades

​Post/Service Deletion

​Querying Relations with Drizzle

​Basic Relation Query

​Nested Relations

​Many-to-Many Query

​Self-Referencing Query

​Best Practices

​1. Always Use Relations for Type Safety

​2. Use Cascade Deletes Carefully

​3. Optimize Nested Queries

​4. Use Indexes for Foreign Keys

​5. Handle Soft Deletes for Content

​Next Steps

Schema Reference

Migrations

Build docs developers (and LLMs) love

Relationship Patterns

Authentication Relationships

User Relations

Session Relations

Account Relations

Organization Relations

Member Relations

Invitation Relations

Profile Relationships

User → Profile (One-to-One)

Blog Relationships

Blog Post Relations

Many-to-Many: Posts ↔ Authors

Many-to-Many: Posts ↔ Categories

Many-to-Many: Posts ↔ Media

Blog Author Relations

Blog Category Relations

Blog Comments Relations

Blog Translations Relations

Services Relationships

Service Listing Relations

Many-to-Many: Services ↔ Media

Service Category Relations

Service Reviews Relations

Service Availability Relations

Service Bookings Relations

Service Translations Relations

Complete Relationship Diagram

Authentication & Users

Blog System

Services Marketplace

Cascade Deletion Rules

User Deletion Cascades

Organization Deletion Cascades

Post/Service Deletion

Querying Relations with Drizzle

Basic Relation Query

Nested Relations

Many-to-Many Query

Self-Referencing Query

Best Practices

1. Always Use Relations for Type Safety

2. Use Cascade Deletes Carefully

3. Optimize Nested Queries

4. Use Indexes for Foreign Keys

5. Handle Soft Deletes for Content

Next Steps