Public Help

Database Schema Reference (DBA)

A DBA-focused catalog of PostgreSQL tables, columns, enums, and foreign keys used by whoosh360. This page is generated from hosted-services/aim-records-engine/prisma/schema.prisma.

Technical Guide Help Home

Connection (Local Defaults)

  • Database name: aim_records
  • Schema: public
  • Port: 5433
  • Environment variable: DATABASE_URL (see hosted-services/aim-records-engine/.env for local dev)
Security: treat OAuth tokens, SMTP passwords, and API keys as secrets. Store them encrypted at rest and never log them.

Enum Catalog


UserRole: ADMIN, CLIENT
ClientAccessLevel: CAMPAIGN_CREATOR, CREATOR_APPROVER, FULL_CONTROL
ClientStatus: ACTIVE, SUSPENDED, TRIAL, PAST_DUE
SocialPlatform: FACEBOOK, INSTAGRAM, THREADS, WHATSAPP, LINKEDIN, X, TIKTOK, YOUTUBE, PINTEREST, GOOGLE_BUSINESS, WORDPRESS, MEDIUM, SUBSTACK, BLOGGER, EMAIL
PostType: TEXT_POST, IMAGE_POST, VIDEO_POST, REEL_POST, SHORT_VIDEO, CAROUSEL_POST, STORY_POST, POLL_POST, DOCUMENT_POST, LINK_SHARE, COMMUNITY_POST, OFFER_POST, EVENT_POST, STANDARD_PIN, IDEA_PIN, MESSAGE_UPDATE, TEMPLATE_MESSAGE, BLOG_ARTICLE, NEWSLETTER_POST, EMAIL_POST
CampaignStatus: DRAFT, AI_GENERATING, PENDING_APPROVAL, APPROVED, SCHEDULED, PUBLISHING, PUBLISHED, COMPLETED, PARTIALLY_COMPLETED, FAILED, REJECTED
ApprovalQueueType: POST_APPROVAL, CAMPAIGN_APPROVAL
MediaType: IMAGE, VIDEO, DOCUMENT
SocialAccountStatus: CONNECTED, RECONNECT_REQUIRED, DISCONNECTED, MAINTENANCE
AuthProviderOwner: PLATFORM_MANAGED
AiServiceType: TEXT_GENERATION, IMAGE_GENERATION, AUDIO_GENERATION, VIDEO_GENERATION
NotificationType: INFO, WARNING, ACTION

Foreign Keys


analytics_snapshots.organizationId -> organizations.id (onDelete: Cascade)
approval_items.campaignId -> campaigns.id (onDelete: SetNull)
approval_items.organizationId -> organizations.id (onDelete: Cascade)
business_profiles.organizationId -> organizations.id (onDelete: Cascade)
campaigns.organizationId -> organizations.id (onDelete: Cascade)
client_settings.organizationId -> organizations.id (onDelete: Cascade)
connector_credentials.organizationId -> organizations.id (onDelete: Cascade)
media_assets.organizationId -> organizations.id (onDelete: Cascade)
notifications.organizationId -> organizations.id (onDelete: Cascade)
oauth_states.organizationId -> organizations.id (onDelete: Cascade)
organizations.currentPlanId -> client_plans.id
payment_records.organizationId -> organizations.id (onDelete: Cascade)
published_post_metric_snapshots.publishedPostId -> published_posts.id (onDelete: Cascade)
published_posts.campaignId -> campaigns.id (onDelete: SetNull)
published_posts.organizationId -> organizations.id (onDelete: Cascade)
sessions.userId -> users.id (onDelete: Cascade)
social_accounts.organizationId -> organizations.id (onDelete: Cascade)
users.organizationId -> organizations.id
workflow_drafts.organizationId -> organizations.id (onDelete: Cascade)

Tables

admin_client_accounts 

id: String [PK]
businessName: String
primaryContact: String
email: String [UNIQUE]
status: ClientStatus
currentPlanId: String
currentPlanName: String
connectedChannels: Json
aiPostsUsed: Int
manualPostsUsed: Int
planExpiry: DateTime
createdAt: DateTime
updatedAt: DateTime
admin_controls 

id: String @db.Uuid [PK]
allowSelfServeSignup: Boolean
requireClientApprovalForAi: Boolean
requireCampaignApproval: Boolean
enforceChannelCredentialReview: Boolean
defaultNewClientPlanId: String? [NULL]
inviteOnlySignupEnabled dbcol=invite_only_signup_enabled: Boolean
autoSuspendPastDueClients: Boolean
maintenanceBanner: String? [NULL]
defaultDraftRetentionDays: Int
auditLoggingEnabled: Boolean
defaultWorkspaceTimezone: String
allowedPostingStartHour: Int
allowedPostingEndHour: Int
blockWeekendPublishing: Boolean
blackoutDateRanges: String? [NULL]
publishRetryLimit: Int
retryBackoffMinutes: Int
autoDisableChannelAfterFailures: Int
maxConcurrentPublishJobs: Int
maxConcurrentAiJobs: Int
jobTimeoutSeconds: Int
requiredApproversCount: Int
approvalExpiryHours: Int
requireRejectionReason: Boolean
autoRegenerateRejectedAiPosts: Boolean
backgroundAnalyticsSyncEnabled dbcol=background_analytics_sync_enabled: Boolean
analyticsScheduleStartDate dbcol=analytics_schedule_start_date: DateTime? [NULL]
analyticsScheduleEndDate dbcol=analytics_schedule_end_date: DateTime? [NULL]
analyticsSyncFrequencyHours: Int
analyticsLookbackDays: Int
scheduledPublishingEnabled dbcol=scheduled_publishing_enabled: Boolean
publishingScheduleStartDate dbcol=publishing_schedule_start_date: DateTime? [NULL]
publishingScheduleEndDate dbcol=publishing_schedule_end_date: DateTime? [NULL]
publishingFrequencyMinutes dbcol=publishing_frequency_minutes: Int
fetchCommentsOnDemandOnly: Boolean
alertOnTokenExpiry: Boolean
tokenExpiryAlertDays: Int
alertOnPublishFailure: Boolean
alertOnSyncFailure: Boolean
alertOnQuotaThreshold: Boolean
billingReminderLeadDays: Int
billingDueGraceDays: Int
billingOverdueReminderDays: Int
billingReminderEmailEnabled: Boolean
billingReminderWhatsappEnabled: Boolean
billingReminderInternalEnabled: Boolean
blogApiAppId dbcol=blog_api_app_id: String? [NULL]
blogApiAppSecret dbcol=blog_api_app_secret: String? [NULL]
webhookUrlReplaceFrom dbcol=webhook_url_replace_from: String? [NULL]
webhookUrlReplaceTo dbcol=webhook_url_replace_to: String? [NULL]
smtpHost: String? [NULL]
smtpPort: Int
smtpUser: String? [NULL]
smtpPassword: String? [NULL]
smtpFromEmail: String? [NULL]
smtpFromName: String? [NULL]
smtpSecure: Boolean
approvalTokenSecret: String? [NULL]
stripeSecretKey: String? [NULL]
stripePublishableKey: String? [NULL]
stripeWebhookSecret: String? [NULL]
razorpayKeyId: String? [NULL]
razorpayKeySecret: String? [NULL]
razorpayWebhookSecret: String? [NULL]
createdAt: DateTime
updatedAt: DateTime
ai_provider_configs 

id: String [PK]
serviceType: AiServiceType
slot: Int
providerName: String
modelName: String
endpointUrl: String
authType: String
fields: Json
createdAt: DateTime
updatedAt: DateTime
analytics_snapshots 

id: String @db.Uuid [PK]
organizationId: String @db.Uuid
rangeStart: DateTime? [NULL]
rangeEnd: DateTime? [NULL]
impressions: Int
likes: Int
comments: Int
shares: Int
saves: Int
engagementRate: String? [NULL]
topPlatform: SocialPlatform? [NULL]
topPost: String? [NULL]
aiVsManual: String? [NULL]
channelBreakdown: Json? [NULL]
postTypeBreakdown: Json? [NULL]
syncedAt: DateTime
approval_items 

id: String [PK]
organizationId: String @db.Uuid
campaignId: String? [NULL]
queueType: ApprovalQueueType
title: String
status: CampaignStatus
platforms: Json
scheduledFor: DateTime? [NULL]
summary: String
postType: PostType? [NULL]
caption: String? [NULL]
callToAction: String? [NULL]
hashtags: Json? [NULL]
mediaIds: Json? [NULL]
rejectionReason: String? [NULL]
approvalUpdatedAt: DateTime? [NULL]
createdAt: DateTime
updatedAt: DateTime
blog_posts 

id: String @db.Uuid [PK]
title: String
slug: String [UNIQUE]
body: String
excerpt: String? [NULL]
category: String? [NULL]
tags: String[]
coverGradient dbcol=cover_gradient: String? [NULL]
authorName dbcol=author_name: String
authorInitial dbcol=author_initial: String
readMinutes dbcol=read_minutes: Int
status: String
publishedAt dbcol=published_at: DateTime
createdAt dbcol=created_at: DateTime
updatedAt dbcol=updated_at: DateTime
business_profiles 

id: String @db.Uuid [PK]
organizationId: String @db.Uuid [UNIQUE]
category: String
description: String
productsAndServices: String
targetAudience: String
brandTone: String
offers: String
complianceNotes: String
location: String
website: String
primaryEmail: String
primaryPhone: String
alternatePhone: String? [NULL]
logoName: String? [NULL]
logoPreviewStyle: String? [NULL]
servicesImageName: String? [NULL]
servicesImagePreviewStyle: String? [NULL]
businessMediaIds: Json? [NULL]
createdAt: DateTime
updatedAt: DateTime
campaigns 

id: String [PK]
organizationId: String @db.Uuid
name: String
status: CampaignStatus
channels: Json
startDate: DateTime
endDate: DateTime
postsPlanned: Int
creationMode: String? [NULL]
inputSource: String? [NULL]
postType: PostType? [NULL]
scheduledFor: DateTime? [NULL]
recurrence: String? [NULL]
preview: Json? [NULL]
manualPost: Json? [NULL]
aiBrief: Json? [NULL]
createdAt: DateTime
updatedAt: DateTime
channel_controls 

platform: SocialPlatform [PK]
enabled: Boolean
allowedPostTypes: Json
rolloutStage: String
createdAt: DateTime
updatedAt: DateTime
client_plans 

id: String [PK]
name: String
description: String
monthlyPriceLabel: String
regionLabel: String
countryCodes: Json? [NULL]
currencyCode: String
billingInterval: String
recurringEnabled: Boolean
defaultPaymentProvider: String
allowedChannels: Json? [NULL]
aiPostLimit: Int
manualPostLimit: Int
autoScheduledPostLimit: Int
autoRetryLimit: Int
approvalSeats: Int
includesBlogChannels: Boolean
allowImageReferenceInputs: Boolean
allowVideoReferenceInputs: Boolean
supportLevel: String
isVisible: Boolean
createdAt: DateTime
updatedAt: DateTime
client_settings 

id: String @db.Uuid [PK]
organizationId: String @db.Uuid [UNIQUE]
approvalRequiredForAiPosts: Boolean
approvalRequiredForCampaigns: Boolean
campaignApprovalEmail: String? [NULL]
allowAutoPostingAfterApproval: Boolean
slackNotifications: Boolean
emailNotifications: Boolean
whatsappNotifications: Boolean
approvalEmailLimit: Int
approvalPhoneLimit: Int
currentPlanSnapshot: Json? [NULL]
availablePlansSnapshot: Json? [NULL]
usageSnapshot: Json? [NULL]
approvalContacts: Json? [NULL]
users: Json? [NULL]
emailSmtpHost: String? [NULL]
emailSmtpPort: Int? [NULL]
emailSmtpUser: String? [NULL]
emailSmtpPassword: String? [NULL]
emailSmtpFromEmail: String? [NULL]
emailSmtpFromName: String? [NULL]
emailSmtpSecure: Boolean
wordpressSiteUrl: String? [NULL]
wordpressUsername: String? [NULL]
wordpressApplicationPassword: String? [NULL]
wordpressAuthorId: String? [NULL]
createdAt: DateTime
updatedAt: DateTime
connector_credentials 

id: String @db.Uuid [PK]
organizationId: String @db.Uuid
platform: SocialPlatform
handle: String? [NULL]
connectedAt: DateTime? [NULL]
scopes: Json? [NULL]
metadata: Json? [NULL]
credentialsEncrypted: String
createdAt: DateTime
updatedAt: DateTime
invitation_requests 

id: String @db.Uuid [PK]
businessName: String
email: String [UNIQUE]
status: String
inviteTokenHash dbcol=invite_token_hash: String? [NULL]
inviteExpiresAt dbcol=invite_expires_at: DateTime? [NULL]
inviteUsedAt dbcol=invite_used_at: DateTime? [NULL]
approvedAt dbcol=approved_at: DateTime? [NULL]
declinedAt dbcol=declined_at: DateTime? [NULL]
lastNotifiedAt dbcol=last_notified_at: DateTime? [NULL]
createdAt dbcol=created_at: DateTime
updatedAt dbcol=updated_at: DateTime
media_assets 

id: String [PK]
organizationId: String @db.Uuid
name: String
type: MediaType
tag: String
usageCount: Int
dimensions: String
sizeLabel: String
previewStyle: String
previewUrl: String? [NULL]
sourceMethod: String? [NULL]
fileKey: String? [NULL]
createdAt: DateTime
updatedAt: DateTime
media_storage_configs 

id: String @db.Uuid [PK]
mode: String
localBasePath: String
localServeBaseUrl: String
localNgrokPublicUrl dbcol=local_ngrok_public_url: String? [NULL]
imageUploadLimitMb dbcol=image_upload_limit_mb: Int
videoUploadLimitMb dbcol=video_upload_limit_mb: Int
maxVideoDurationSeconds dbcol=max_video_duration_seconds: Int
s3BucketName: String? [NULL]
s3Region: String? [NULL]
s3EndpointUrl: String? [NULL]
s3AccessKeyId: String? [NULL]
s3SecretAccessKey: String? [NULL]
s3PublicBaseUrl: String? [NULL]
createdAt: DateTime
updatedAt: DateTime
notifications 

id: String [PK]
organizationId: String @db.Uuid
title: String
message: String
type: NotificationType
details: String? [NULL]
occurredAt: DateTime
createdAt: DateTime
oauth_states 

id: String @db.Uuid [PK]
organizationId: String @db.Uuid
provider: String
payload: Json
expiresAt: DateTime
createdAt: DateTime
organizations 

id: String @db.Uuid [PK]
slug: String [UNIQUE]
businessName: String
status: ClientStatus
primaryEmail: String
primaryContact: String? [NULL]
currentPlanId: String? [NULL]
timezone: String
countryCode: String
createdAt: DateTime
updatedAt: DateTime
payment_records 

id: String @db.Uuid [PK]
organizationId: String @db.Uuid
planId: String
planName: String
amountLabel: String
currencyCode: String
amountMinorUnits: Int
provider: String
providerOrderId: String? [NULL]
providerPaymentId: String? [NULL]
status: String
billingPeriodStart: DateTime? [NULL]
billingPeriodEnd: DateTime? [NULL]
paidAt: DateTime? [NULL]
failureReason: String? [NULL]
createdAt: DateTime
updatedAt: DateTime
platform_credentials 

id: String @db.Uuid [PK]
platform: SocialPlatform [UNIQUE]
authMode: String
credentialOwner: AuthProviderOwner
fields: Json
createdAt: DateTime
updatedAt: DateTime
published_post_metric_snapshots 

id: String @db.Uuid [PK]
publishedPostId: String
impressions: Int
likes: Int
comments: Int
shares: Int
saves: Int
fetchedAt: DateTime
published_posts 

id: String [PK]
organizationId: String @db.Uuid
campaignId: String? [NULL]
platform: SocialPlatform
platformPostId: String
status: CampaignStatus
failureDetails: String? [NULL]
notes: String? [NULL]
repostCount dbcol=repost_count: Int
emailRecipients: Json? [NULL]
metricsFetchStatus: String? [NULL]
metricsFetchMessage: String? [NULL]
creationMode: String? [NULL]
inputSource: String? [NULL]
postType: PostType? [NULL]
scheduledFor: DateTime? [NULL]
publishedAt: DateTime? [NULL]
preview: Json? [NULL]
boostEligible: Boolean
boostRecord: Json? [NULL]
manualPost: Json? [NULL]
aiBrief: Json? [NULL]
createdAt: DateTime
updatedAt: DateTime
sessions 

id: String @db.Uuid [PK]
userId: String @db.Uuid
token: String [UNIQUE]
role: UserRole
createdAt: DateTime
expiresAt: DateTime? [NULL]
social_accounts 

id: String [PK]
organizationId: String @db.Uuid
platform: SocialPlatform
handle: String
status: SocialAccountStatus
expiresInDays: Int
lastSyncAt: DateTime? [NULL]
followers: Int
maintenanceMode: Boolean
connectionMethod: String? [NULL]
connectionDetails: Json? [NULL]
contentTypes: Json? [NULL]
createdAt: DateTime
updatedAt: DateTime
users 

id: String @db.Uuid [PK]
organizationId: String? @db.Uuid [NULL]
role: UserRole
accessLevel: ClientAccessLevel? [NULL]
displayName: String
email: String [UNIQUE]
passwordHash: String
emailVerified: Boolean
verificationCode: String? [NULL]
verificationCodeExpiresAt dbcol=verification_code_expires_at: DateTime? [NULL]
onboardingCompleted: Boolean
recoveryEmail: String? [NULL]
lastPasswordChangedAt: DateTime? [NULL]
createdAt: DateTime
updatedAt: DateTime
webhook_config_records 

id: String [PK]
label: String
postType dbcol=post_type: String
webhookUrl dbcol=webhook_url: String
samplePayload dbcol=sample_payload: String @db.Text
responseSchema dbcol=response_schema: String @db.Text
useGlobalDomain dbcol=use_global_domain: Boolean
createdAt dbcol=created_at: DateTime
updatedAt dbcol=updated_at: DateTime
worker_jobs 

id: String @db.Uuid [PK]
worker: String
type: String
status: String
organizationId: String? @db.Uuid [NULL]
campaignId: String? [NULL]
campaignName: String? [NULL]
payload: Json? [NULL]
result: Json? [NULL]
message: String? [NULL]
errorMessage: String? [NULL]
startedAt: DateTime? [NULL]
completedAt: DateTime? [NULL]
createdAt: DateTime
updatedAt: DateTime
workflow_drafts 

id: String [PK]
organizationId: String @db.Uuid
title: String
mode: String
postType: PostType
platforms: Json
summary: String
updatedAtLabel: String
expiresAt: DateTime
manualPost: Json? [NULL]
aiBrief: Json? [NULL]
demoPreview: Json? [NULL]
createdAt: DateTime
updatedAt: DateTime

Public URL: /help/db-schema.generated.html