Table Relationships

Understanding database relationships and foreign keys in Concordia

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

Diagram

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

Details

user → sessions · one-to-many

A user can have multiple active sessions across devices. When a user is deleted, all sessions are automatically removed.

user → accounts · one-to-many

A user can link multiple OAuth providers (Google, GitHub, etc.). Deleting the user removes all linked accounts.

user → members · one-to-many

A user can be a member of multiple organizations. User deletion removes all memberships.

user → invitations · one-to-many

Tracks invitations sent by this user. User deletion removes invitation records.

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