Zorbit DB Design

Note

Author: Amit Patil

This doc is shared with authorized users for educational purposes only . Usage for reverse engineering of the platform is strictly prohibited and subject to copyright and licensing as per Zorbit -license

Database Schema Overview

The Zorbit database is built on PostgreSQL. The schema is designed to support a comprehensive LinkedIn content generation and management platform with subscription-based access control, usage tracking, and multi-persona content creation capabilities.

Key Design Decisions

Soft Delete Pattern

All major entities implement soft deletion through the isDeleted boolean field rather than hard deletion. This design decision provides several critical benefits:

• Data Recovery: Accidental deletions can be reversed without data loss
• Audit Trail: Historical data remains accessible for analytics and compliance
• Referential Integrity: Related records maintain their relationships even after “deletion”
• User Experience: Users can restore content they thought was permanently removed

This pattern is consistently applied across User, Post, Template, Persona, ChatMessage, Thread, ContentIdea, and Comment tables.

Content Generation Architecture

The Post table supports multiple content types (post, knowledge_stack, post_stack, idea, comment) and tracks generation sources. This design enables:

• Unified Content Management: All content types share the same storage and management infrastructure
• Source Tracking: The source field identifies where content originated (feed, generate, etc.)
• Metadata Flexibility: The metadata JSON field allows storing varying attributes without schema changes
• System Persona Support: systemPersonaId enables personas that don’t require database storage

Subscription and Usage Quota System

The platform implements a two-tier quota management system:

1. Plan-Based Limits: Each Plan defines maximum allowances for different features
2. Usage Tracking: UsageQuota and UsageLog tables provide granular tracking and reset capabilities

This architecture supports:

• Flexible Billing Cycles: Quotas reset based on resetDate and billing cycle boundaries
• Detailed Analytics: UsageLog captures metadata about each usage event
• Multiple Quota Types: Supports post generation, idea generation, personas, image creation, comment generation, and post scheduling

External Payment Integration

The schema includes fields for external payment provider integration (CustomerId, ProductId, SubscriptionId, InvoiceId). This design:

• Decouples Payment Logic: Payment provider changes don’t require schema modifications
• Maintains Local Records: All subscription and payment data remains in the database
• Enables Multi-Provider Support: Can support multiple payment providers simultaneously

Thread-Based Chat Organization

Chat messages are organized into threads, enabling:

• Conversation Context: Related messages are grouped together
• Thread Types: Support for different conversation types (post, idea)
• Scalable History: Users can maintain multiple concurrent conversations

Important Database Queries

User Subscription Status and Quota Check

This query retrieves a user’s active subscription and current usage quotas, essential for feature access control:

-- Get user subscription status and plan limits
SELECT
  s.status AS subscription_status,
  p.post_generation_limit,
  p.idea_generation_limit,
  p.comment_generation_limit,
  p.post_scheduling_limit
FROM "User" u
LEFT JOIN "Subscription" s ON u.id = s."userId"
LEFT JOIN "Plan" p ON s."planId" = p.id
WHERE u.id = $1 AND u."isDeleted" = false;

-- Get current usage quotas
SELECT
  type,
  used,
  limit
FROM "UsageQuota"
WHERE "userId" = $1
  AND "resetDate" >= CURRENT_DATE;

Posts by Persona with Generation Metadata

Retrieves posts generated using a specific persona, including generation source and metadata:

SELECT
  p.id,
  p.content,
  p.published,
  per.name AS persona_name
FROM "Post" p
INNER JOIN "Persona" per ON p."personaId" = per.id
WHERE p."personaId" = $1
  AND p."isDeleted" = false
  AND p.published = true
ORDER BY p."createdAt" DESC;

Usage Quota Exhaustion Check

Determines if a user has exceeded their quota for a specific feature type:

SELECT
  (used < limit) AS has_quota,
  (limit - used) AS remaining_quota
FROM "UsageQuota"
WHERE "userId" = $1
  AND type = $2
  AND "resetDate" >= CURRENT_DATE
LIMIT 1;

Scheduled Posts for LinkedIn Publishing

Retrieves posts scheduled for publication, critical for automated publishing workflows:

SELECT
  p.id,
  p.content,
  p."scheduledOn",
  u.email AS user_email,
  s.status AS subscription_status
FROM "Post" p
INNER JOIN "User" u ON p."userId" = u.id
LEFT JOIN "Subscription" s ON u.id = s."userId"
WHERE p."scheduledOn" IS NOT NULL
  AND p."scheduledOn" <= CURRENT_TIMESTAMP
  AND p."isPostedOnLinkedin" = false
  AND p."isDeleted" = false
ORDER BY p."scheduledOn" ASC;

Content Ideas with Key Takeaways

Fetches saved content ideas with their associated chat context:

SELECT
  ci.id,
  ci.title,
  ci.content,
  ci."keyTakeaways"
FROM "ContentIdea" ci
INNER JOIN "ChatMessage" cm ON ci."chatMessageId" = cm.id
WHERE ci."isSaved" = true
  AND ci."isDeleted" = false
  AND cm."userId" = $1
ORDER BY ci."createdAt" DESC;

Database Migration Strategy

Versioning Approach

Database migrations should be managed through a version-controlled migration system that provides:

• Version Control: Each migration is a numbered SQL file tracked in version control
• Reproducibility: Migrations can be applied consistently across environments
• History Tracking: Complete audit trail of all schema changes

Migration Workflow

1. Development: Create SQL migration files during development
2. Review: All migrations are committed to version control for team review
3. Testing: Migrations are tested in staging before production deployment
4. Production: Applied using migration tools or scripts in production environments

Rollback Strategy

For rollback scenarios:

• Create Reverse Migrations: When a rollback is needed, create a new migration that reverses the changes
• Data Preservation: Soft delete patterns ensure data isn’t lost during rollbacks
• Staged Rollouts: Use feature flags to disable new features before rolling back migrations
• Backup Before Migration: Always backup production databases before applying migrations

Data Integrity Measures

Several mechanisms ensure data integrity throughout the migration process:

• Foreign Key Constraints: All relationships use appropriate ON DELETE CASCADE or ON DELETE RESTRICT clauses
• Transaction Wrappers: Critical migrations are wrapped in database transactions
• Validation Rules: Database constraints enforce data types, required fields, and unique constraints
• Soft Delete Consistency: Queries consistently filter isDeleted: false to maintain logical consistency

Migration Best Practices

• Atomic Changes: Keep migrations focused on single, atomic changes
• Non-Breaking First: Add new fields as nullable before making them required
• Index Management: Add indexes in separate migrations to monitor performance impact
• Data Migrations: Separate schema changes from data transformations
• Testing: Test migrations on production-like data volumes before deployment

Database Schema Reference

This section provides a complete reference of all database tables with their fields, data types, constraints, and example data.

User Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	550e8400-e29b-41d4-a716-446655440000
email	VARCHAR	UNQ	UNQ	user@example.com
name	VARCHAR	NULL	-	John Doe
role	VARCHAR	DEF ‘user’	-	user
dodoCustomerId	VARCHAR	NULL	-	cus_abc123
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
isDeleted	BOOLEAN	DEF false	-	false

Post Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	660e8400-e29b-41d4-a716-446655440000
title	VARCHAR	NULL	-	My LinkedIn Post
content	TEXT	-	-	This is the post content...
imageUrl	VARCHAR	NULL	-	https://cdn.example.com/image.jpg
imageKey	VARCHAR	NULL	-	posts/123/image.jpg
published	BOOLEAN	DEF false	-	true
isPostedOnLinkedin	BOOLEAN	DEF false	-	false
linkedinUrl	VARCHAR	NULL	-	https://linkedin.com/posts/123
linkedinPostedAt	TIMESTAMP	NULL	-	2024-01-15 12:00:00
scheduledOn	TIMESTAMP	NULL	-	2024-01-20 09:00:00
type	VARCHAR	DEF ‘post’	-	post
source	VARCHAR	NULL	-	generate
metadata	JSONB	NULL	-	{"persona": "tech-expert"}
systemPersonaId	VARCHAR	NULL	-	sys_persona_123
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
isDeleted	BOOLEAN	DEF false	-	false
isLiked	BOOLEAN	DEF false	-	false
isDisliked	BOOLEAN	DEF false	-	false
isSaved	BOOLEAN	DEF false	-	false
userId	UUID	FK	FK → User.id	550e8400-e29b-41d4-a716-446655440000
templateId	UUID	NULL, FK	FK → Template.id	770e8400-e29b-41d4-a716-446655440000
chatMessageId	UUID	NULL, FK	FK → ChatMessage.id	880e8400-e29b-41d4-a716-446655440000
personaId	UUID	NULL, FK	FK → Persona.id	990e8400-e29b-41d4-a716-446655440000

Template Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	770e8400-e29b-41d4-a716-446655440000
name	VARCHAR	-	-	Weekly Update Template
content	TEXT	-	-	Template content here...
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
isDeleted	BOOLEAN	DEF false	-	false
userId	UUID	FK	FK → User.id	550e8400-e29b-41d4-a716-446655440000

Persona Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	990e8400-e29b-41d4-a716-446655440000
name	VARCHAR	-	-	Tech Thought Leader
description	TEXT	NULL	-	Expert in cloud computing...
defaultPrompt	TEXT	-	-	Write as a tech expert...
targetAudience	VARCHAR	NULL	-	Software engineers
profession	VARCHAR	NULL	-	Cloud Architect
relevantKeywords	VARCHAR	NULL	-	AWS, cloud, serverless
opinions	TEXT	NULL	-	Believes in microservices...
vocabulary	VARCHAR	NULL	-	Technical, precise
avoidWords	VARCHAR	NULL	-	buzzwords, jargon
endGoal	VARCHAR	NULL	-	Educate and inspire
customHashtag	VARCHAR	NULL	-	#CloudTech
fixCta	VARCHAR	NULL	-	Learn more in comments
preferences	JSONB	NULL	-	{"tone": "professional"}
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
isDeleted	BOOLEAN	DEF false	-	false
userId	UUID	FK	FK → User.id	550e8400-e29b-41d4-a716-446655440000

ChatMessage Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	880e8400-e29b-41d4-a716-446655440000
content	TEXT	-	-	Generate a post about AI...
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
isDeleted	BOOLEAN	DEF false	-	false
generationTime	FLOAT	NULL	-	2.5
userId	UUID	FK	FK → User.id	550e8400-e29b-41d4-a716-446655440000
threadId	UUID	FK	FK → Thread.id	aa0e8400-e29b-41d4-a716-446655440000

Thread Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	aa0e8400-e29b-41d4-a716-446655440000
title	VARCHAR	NULL	-	AI Content Ideas
type	VARCHAR	NULL, DEF ‘post’	-	post
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
isDeleted	BOOLEAN	DEF false	-	false
userId	UUID	FK	FK → User.id	550e8400-e29b-41d4-a716-446655440000

ContentIdea Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	bb0e8400-e29b-41d4-a716-446655440000
title	VARCHAR	-	-	The Future of AI
content	TEXT	-	-	Full content idea text...
keyTakeaways	TEXT[]	NULL	-	["AI is transformative", "Ethics matter"]
controversialAngle	TEXT	NULL	-	AI will replace jobs...
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
isDeleted	BOOLEAN	DEF false	-	false
isSaved	BOOLEAN	DEF false	-	false
chatMessageId	UUID	FK	FK → ChatMessage.id	880e8400-e29b-41d4-a716-446655440000

Plan Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	cc0e8400-e29b-41d4-a716-446655440000
name	VARCHAR	-	-	Pro
description	TEXT	NULL	-	Professional plan with advanced features
price	DECIMAL(10,2)	-	-	29.99
currency	VARCHAR	DEF ‘USD’	-	USD
interval	VARCHAR	DEF ‘month’	-	month
features	JSONB	NULL	-	["feature1", "feature2"]
postGenerationLimit	INTEGER	-	-	100
ideaGenerationLimit	INTEGER	-	-	50
commentGenerationLimit	INTEGER	DEF 0	-	20
postSchedulingLimit	INTEGER	DEF 0	-	30
personaLimit	INTEGER	-	-	10
imageCreationLimit	INTEGER	-	-	25
dodoProductId	VARCHAR	NULL	-	prod_abc123
isActive	BOOLEAN	DEF true	-	true
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00

Subscription Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	dd0e8400-e29b-41d4-a716-446655440000
status	VARCHAR	-	-	active
startDate	TIMESTAMP	-	-	2024-01-01 00:00:00
endDate	TIMESTAMP	NULL	-	2024-12-31 23:59:59
renewalDate	TIMESTAMP	-	-	2024-02-01 00:00:00
canceledAt	TIMESTAMP	NULL	-	NULL
trialEndsAt	TIMESTAMP	NULL	-	2024-01-15 00:00:00
userId	UUID	FK, UNQ	FK → User.id, UNQ	550e8400-e29b-41d4-a716-446655440000
planId	UUID	FK	FK → Plan.id	cc0e8400-e29b-41d4-a716-446655440000
dodoSubscriptionId	VARCHAR	NULL	-	sub_abc123
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00

PaymentMethod Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	ee0e8400-e29b-41d4-a716-446655440000
type	VARCHAR	-	-	credit_card
cardBrand	VARCHAR	NULL	-	visa
lastFour	VARCHAR	NULL	-	4242
expiryMonth	INTEGER	NULL	-	12
expiryYear	INTEGER	NULL	-	2025
isDefault	BOOLEAN	DEF false	-	true
userId	UUID	FK	FK → User.id	550e8400-e29b-41d4-a716-446655440000
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00

Invoice Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	ff0e8400-e29b-41d4-a716-446655440000
invoiceNumber	VARCHAR	UNQ	UNQ	INV-2024-001
amount	DECIMAL(10,2)	-	-	29.99
currency	VARCHAR	DEF ‘USD’	-	USD
status	VARCHAR	-	-	paid
billingDate	TIMESTAMP	-	-	2024-01-15 00:00:00
dueDate	TIMESTAMP	-	-	2024-01-30 00:00:00
paidDate	TIMESTAMP	NULL	-	2024-01-16 14:30:00
stripeInvoiceId	VARCHAR	NULL	-	in_abc123
userId	UUID	FK	FK → User.id	550e8400-e29b-41d4-a716-446655440000
paymentMethodId	UUID	NULL, FK	FK → PaymentMethod.id	ee0e8400-e29b-41d4-a716-446655440000
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00

InvoiceItem Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	110e8400-e29b-41d4-a716-446655440000
description	VARCHAR	-	-	Pro Plan - Monthly
quantity	INTEGER	-	-	1
unitPrice	DECIMAL(10,2)	-	-	29.99
amount	DECIMAL(10,2)	-	-	29.99
invoiceId	UUID	FK	FK → Invoice.id	ff0e8400-e29b-41d4-a716-446655440000
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00

UsageQuota Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	220e8400-e29b-41d4-a716-446655440000
type	VARCHAR	-	-	post_generation
used	INTEGER	DEF 0	-	45
limit	INTEGER	-	-	100
resetDate	TIMESTAMP	-	-	2024-02-01 00:00:00
billingCycleStart	TIMESTAMP	-	-	2024-01-01 00:00:00
billingCycleEnd	TIMESTAMP	-	-	2024-01-31 23:59:59
userId	UUID	FK	FK → User.id	550e8400-e29b-41d4-a716-446655440000
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00

UsageLog Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	330e8400-e29b-41d4-a716-446655440000
type	VARCHAR	-	-	post_generation
description	VARCHAR	NULL	-	Generated LinkedIn post
metadata	JSONB	NULL	-	{"postId": "123", "persona": "tech"}
userId	VARCHAR	-	-	550e8400-e29b-41d4-a716-446655440000
quotaId	UUID	FK	FK → UsageQuota.id	220e8400-e29b-41d4-a716-446655440000
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00

Comment Table

Field	Type	Constraints	Key	Example
id	UUID	PK	PK	440e8400-e29b-41d4-a716-446655440000
firstName	VARCHAR	-	-	Jane
lastName	VARCHAR	-	-	Smith
title	VARCHAR	NULL	-	Senior Engineer
image	VARCHAR	NULL	-	https://cdn.example.com/avatar.jpg
quote	TEXT	-	-	Great insights on AI!
date	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
profileUrl	VARCHAR	NULL	-	https://linkedin.com/in/janesmith
createdAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
updatedAt	TIMESTAMP	DEF now()	-	2024-01-15 10:30:00
isDeleted	BOOLEAN	DEF false	-	false
userId	UUID	FK	FK → User.id	550e8400-e29b-41d4-a716-446655440000
personaId	UUID	NULL, FK	FK → Persona.id	990e8400-e29b-41d4-a716-446655440000