> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/Ishaq74/concordia/llms.txt
> Use this file to discover all available pages before exploring further.
# 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:
```typescript theme={null}
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
```
A user can have multiple active sessions across devices. When a user is deleted, all sessions are automatically removed.
A user can link multiple OAuth providers (Google, GitHub, etc.). Deleting the user removes all linked accounts.
A user can be a member of multiple organizations. User deletion removes all memberships.
Tracks invitations sent by this user. User deletion removes invitation records.
### Session Relations
```typescript theme={null}
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
```typescript theme={null}
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
```typescript theme={null}
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
```typescript theme={null}
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
```typescript theme={null}
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`:
```typescript theme={null}
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
```typescript theme={null}
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`
```typescript theme={null}
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`
```typescript theme={null}
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`
```typescript theme={null}
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
```typescript theme={null}
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
```typescript theme={null}
export const blogCategoriesRelations = relations(blogCategories, ({ one }) => ({
featuredImage: one(blogMedia, {
fields: [blogCategories.featuredImageId],
references: [blogMedia.id]
}),
}));
```
**Hierarchical Categories:**
Categories support self-referencing hierarchy:
```typescript theme={null}
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
```typescript theme={null}
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
```typescript theme={null}
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
```typescript theme={null}
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`
```typescript theme={null}
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
```typescript theme={null}
export const servicesCategoriesRelations = relations(servicesCategories, ({ one }) => ({
featuredImage: one(servicesMedia, {
fields: [servicesCategories.featuredImageId],
references: [servicesMedia.id],
}),
}));
```
**Hierarchical Categories:**
Like blog categories, service categories support hierarchy:
```typescript theme={null}
export const servicesCategories = pgTable("services_categories", {
id: text("id").primaryKey(),
parentId: text("parent_id"), // Self-reference
// ... other fields
});
```
### Service Reviews Relations
```typescript theme={null}
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
```typescript theme={null}
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
```typescript theme={null}
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
```typescript theme={null}
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
```typescript theme={null}
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
```typescript theme={null}
// 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
```typescript theme={null}
// 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
```typescript theme={null}
// 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:
```typescript theme={null}
// ✅ 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:
```typescript theme={null}
// ✅ 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:
```typescript theme={null}
// ✅ 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:
```typescript theme={null}
export const blogPosts = pgTable("blog_posts", {
id: text("id").primaryKey(),
status: text("status").notNull(), // 'published', 'draft', 'deleted'
deletedAt: timestamp("deleted_at"),
});
```
## Next Steps
View detailed schema documentation
Learn how to create and apply migrations