Open source Loom alternative. Beautiful, shareable screen recordings.
Rust appcapcossloommacnextjsnextjs15open-sourceossreactrecordrustscreen-capturescreen-recorderscreenshotsolidjstauritauri-apptypescriptvite
import type {
Agent,
AiGenerationLanguage,
Comment,
Folder,
ImageUpload,
Organisation,
PublicCollection,
S3Bucket,
Space,
Storage,
User,
Video,
} from "@cap/web-domain";
import { sql } from "drizzle-orm";
import {
bigint,
boolean,
customType,
datetime,
float,
foreignKey,
index,
int,
json,
longtext,
mysqlTable,
primaryKey,
text,
timestamp,
unique,
uniqueIndex,
varchar,
} from "drizzle-orm/mysql-core";
import { relations } from "drizzle-orm/relations";
import { nanoIdLength } from "./helpers.ts";
import type {
VideoCallToAction,
VideoEditSpec,
VideoMetadata,
} from "./types/index.ts";
export type AuthApiKeySource = "desktop" | "extension" | "mobile" | "unknown";
type GoogleDriveStorageQuotaCache = {
limit?: string | null;
usage?: string | null;
usageInDrive?: string | null;
usageInDriveTrash?: string | null;
fetchedAt: string;
};
const nanoId = customType<{ data: string; notNull: true }>({
dataType() {
return `varchar(${nanoIdLength})`;
},
});
// TODO: This will replace `nanoId` in: https://github.com/CapSoftware/Cap/pull/1105
const nanoIdRequired = (name: string) =>
varchar(name, { length: nanoIdLength }).notNull();
const nanoIdNullable = customType<{ data: string; notNull: false }>({
dataType() {
return `varchar(${nanoIdLength})`;
},
});
// Add a custom type for encrypted strings
const encryptedText = customType<{ data: string; notNull: true }>({
dataType() {
return "text";
},
});
const encryptedTextNullable = customType<{ data: string; notNull: false }>({
dataType() {
return "text";
},
});
export const users = mysqlTable(
"users",
{
id: nanoId("id").notNull().primaryKey().$type<User.UserId>(),
name: varchar("name", { length: 255 }),
lastName: varchar("lastName", { length: 255 }),
email: varchar("email", { length: 255 }).notNull(),
emailVerified: timestamp("emailVerified"),
image: varchar("image", { length: 255 }).$type<ImageUpload.ImageUrlOrKey>(),
stripeCustomerId: varchar("stripeCustomerId", { length: 255 }),
stripeSubscriptionId: varchar("stripeSubscriptionId", {
length: 255,
}),
thirdPartyStripeSubscriptionId: varchar("thirdPartyStripeSubscriptionId", {
length: 255,
}),
stripeSubscriptionStatus: varchar("stripeSubscriptionStatus", {
length: 255,
}),
stripeSubscriptionPriceId: varchar("stripeSubscriptionPriceId", {
length: 255,
}),
preferences: json("preferences")
.$type<{
notifications: {
pauseComments: boolean;
pauseReplies: boolean;
pauseViews: boolean;
pauseReactions: boolean;
pauseAnonViews?: boolean;
};
trackedEvents?: {
user_signed_up?: boolean;
};
} | null>()
.default(null),
activeOrganizationId: nanoId(
"activeOrganizationId",
).$type<Organisation.OrganisationId>(),
created_at: timestamp("created_at").notNull().defaultNow(),
updated_at: timestamp("updated_at").notNull().defaultNow().onUpdateNow(),
onboardingSteps: json("onboardingSteps").$type<{
welcome?: boolean;
organizationSetup?: boolean;
customDomain?: boolean;
inviteTeam?: boolean;
download?: boolean;
}>(),
onboarding_completed_at: timestamp("onboarding_completed_at"),
customBucket: nanoIdNullable("customBucket"),
inviteQuota: int("inviteQuota").notNull().default(1),
defaultOrgId:
nanoIdNullable("defaultOrgId").$type<Organisation.OrganisationId>(),
authSessionVersion: int("authSessionVersion").notNull().default(0),
marketingOrigin: varchar("marketingOrigin", { length: 20 })
.notNull()
.default("unknown"),
},
(table) => ({
emailIndex: uniqueIndex("email_idx").on(table.email),
}),
);
export const loopsSyncJobs = mysqlTable(
"loops_sync_jobs",
{
userId: nanoId("userId").notNull().primaryKey().$type<User.UserId>(),
revision: int("revision").notNull().default(1),
nextAttemptAt: datetime("nextAttemptAt", { mode: "date" }).notNull(),
leaseToken: varchar("leaseToken", { length: 36 }),
leaseUntil: datetime("leaseUntil", { mode: "date" }),
failures: int("failures").notNull().default(0),
lastError: varchar("lastError", { length: 64 }),
profileHash: varchar("profileHash", { length: 64 }),
teammateJoinedAt: datetime("teammateJoinedAt", { mode: "date" }),
syncedEmail: varchar("syncedEmail", { length: 255 }),
lastSyncedAt: datetime("lastSyncedAt", { mode: "date" }),
},
(table) => ({
dueIndex: index("loops_sync_due_idx").on(table.nextAttemptAt),
}),
);
export const accounts = mysqlTable(
"accounts",
{
id: nanoId("id").notNull().primaryKey(),
userId: nanoId("userId").notNull(),
type: varchar("type", { length: 255 }).notNull(),
provider: varchar("provider", { length: 255 }).notNull(),
providerAccountId: varchar("providerAccountId", { length: 255 }).notNull(),
access_token: text("access_token"),
expires_in: int("expires_in"),
id_token: text("id_token"),
refresh_token: text("refresh_token"),
refresh_token_expires_in: int("refresh_token_expires_in"),
scope: varchar("scope", { length: 255 }),
token_type: varchar("token_type", { length: 255 }),
createdAt: timestamp("createdAt").defaultNow().notNull(),
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
tempColumn: text("tempColumn"),
},
(table) => ({
userIdIndex: index("user_id_idx").on(table.userId),
providerAccountIdIndex: index("provider_account_id_idx").on(
table.providerAccountId,
),
}),
);
export const sessions = mysqlTable(
"sessions",
{
id: nanoId("id").notNull().primaryKey(),
sessionToken: varchar("sessionToken", { length: 255 }).notNull(),
userId: nanoId("userId").notNull().$type<User.UserId>(),
expires: datetime("expires").notNull(),
created_at: timestamp("created_at").notNull().defaultNow(),
updated_at: timestamp("updated_at").notNull().defaultNow().onUpdateNow(),
},
(table) => ({
sessionTokenIndex: uniqueIndex("session_token_idx").on(table.sessionToken),
userIdIndex: index("user_id_idx").on(table.userId),
}),
);
export const verificationTokens = mysqlTable("verification_tokens", {
identifier: varchar("identifier", { length: 255 }).primaryKey().notNull(),
token: varchar("token", { length: 255 }).unique().notNull(),
expires: datetime("expires").notNull(),
created_at: timestamp("created_at").notNull().defaultNow(),
updated_at: timestamp("updated_at").notNull().defaultNow().onUpdateNow(),
});
export const organizations = mysqlTable(
"organizations",
{
id: nanoId("id")
.notNull()
.primaryKey()
.$type<Organisation.OrganisationId>(),
name: varchar("name", { length: 255 }).notNull(),
ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
metadata: json("metadata"),
tombstoneAt: timestamp("tombstoneAt"),
allowedEmailDomain: varchar("allowedEmailDomain", { length: 255 }),
defaultVideoVisibility: varchar("defaultVideoVisibility", {
length: 7,
}).$type<"private">(),
customDomain: varchar("customDomain", { length: 255 }),
domainVerified: timestamp("domainVerified"),
settings: json("settings").$type<{
disableSummary?: boolean;
disableCaptions?: boolean;
disableChapters?: boolean;
disableReactions?: boolean;
disableTranscript?: boolean;
disableComments?: boolean;
hideShareableLinkCapLogo?: boolean;
shareableLinkUseOrganizationIcon?: boolean;
aiGenerationLanguage?: AiGenerationLanguage;
defaultPlaybackSpeed?: number;
}>(),
iconUrl: varchar("iconUrl", {
length: 1024,
}).$type<ImageUpload.ImageUrlOrKey>(),
shareableLinkIconUrl: varchar("shareableLinkIconUrl", {
length: 1024,
}).$type<ImageUpload.ImageUrlOrKey>(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
workosOrganizationId: varchar("workosOrganizationId", { length: 255 }),
workosConnectionId: varchar("workosConnectionId", { length: 255 }),
},
(table) => ({
ownerIdTombstoneIndex: index("owner_id_tombstone_idx").on(
table.ownerId,
table.tombstoneAt,
),
customDomainIndex: index("custom_domain_idx").on(table.customDomain),
workosOrganizationIdIndex: uniqueIndex("workos_organization_id_idx").on(
table.workosOrganizationId,
),
}),
);
export const organizationSso = mysqlTable(
"organization_sso",
{
organizationId: nanoId("organizationId")
.notNull()
.primaryKey()
.$type<Organisation.OrganisationId>(),
purchasedByUserId: nanoId("purchasedByUserId")
.notNull()
.$type<User.UserId>(),
stripeCustomerId: varchar("stripeCustomerId", { length: 255 }),
stripeSubscriptionId: varchar("stripeSubscriptionId", { length: 255 }),
stripePriceId: varchar("stripePriceId", { length: 255 }),
status: varchar("status", { length: 32 }).notNull().default("unpaid"),
paidThrough: datetime("paidThrough"),
currentPeriodEnd: datetime("currentPeriodEnd"),
cancelAtPeriodEnd: boolean("cancelAtPeriodEnd").notNull().default(false),
checkoutAttemptId: varchar("checkoutAttemptId", { length: 36 }),
checkoutCurrency: varchar("checkoutCurrency", { length: 3 }),
checkoutPriceId: varchar("checkoutPriceId", { length: 255 }),
checkoutSessionId: varchar("checkoutSessionId", { length: 255 }),
checkoutStartedAt: datetime("checkoutStartedAt"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
},
(table) => ({
stripeSubscriptionIdIndex: uniqueIndex("sso_stripe_subscription_id_idx").on(
table.stripeSubscriptionId,
),
}),
);
export type OrganisationMemberRole = "owner" | "admin" | "member";
export const organizationMembers = mysqlTable(
"organization_members",
{
id: nanoId("id").notNull().primaryKey(),
userId: nanoId("userId").notNull().$type<User.UserId>(),
organizationId: nanoId("organizationId")
.notNull()
.$type<Organisation.OrganisationId>(),
role: varchar("role", { length: 255 })
.notNull()
.$type<OrganisationMemberRole>(),
hasProSeat: boolean("hasProSeat").default(false).notNull(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
},
(table) => ({
organizationIdIndex: index("organization_id_idx").on(table.organizationId),
userIdOrganizationIdIndex: index("user_id_organization_id_idx").on(
table.userId,
table.organizationId,
),
}),
);
export const integrationInstallations = mysqlTable(
"integration_installations",
{
id: nanoId("id").notNull().primaryKey(),
provider: varchar("provider", { length: 64 }).notNull(),
externalId: varchar("externalId", { length: 255 }).notNull(),
displayName: varchar("displayName", { length: 255 }).notNull(),
organizationId: nanoId("organizationId")
.notNull()
.$type<Organisation.OrganisationId>(),
installedByUserId: nanoId("installedByUserId")
.notNull()
.$type<User.UserId>(),
encryptedCredentials: encryptedText("encryptedCredentials").notNull(),
metadata: json("metadata").notNull().$type<Record<string, unknown>>(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
},
(table) => ({
providerExternalIdIndex: uniqueIndex("provider_external_id_idx").on(
table.provider,
table.externalId,
),
organizationProviderDisplayNameIndex: index(
"organization_provider_display_name_idx",
).on(table.organizationId, table.provider, table.displayName),
}),
);
export const organizationInvites = mysqlTable(
"organization_invites",
{
id: nanoId("id").notNull().primaryKey(),
organizationId: nanoId("organizationId")
.notNull()
.$type<Organisation.OrganisationId>(),
invitedEmail: varchar("invitedEmail", { length: 255 }).notNull(),
invitedByUserId: nanoId("invitedByUserId").notNull().$type<User.UserId>(),
role: varchar("role", { length: 255 })
.notNull()
.$type<OrganisationMemberRole>(),
status: varchar("status", { length: 255 }).notNull().default("pending"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
expiresAt: timestamp("expiresAt"),
},
(table) => ({
organizationIdIndex: index("organization_id_idx").on(table.organizationId),
invitedEmailIndex: index("invited_email_idx").on(table.invitedEmail),
invitedByUserIdIndex: index("invited_by_user_id_idx").on(
table.invitedByUserId,
),
statusIndex: index("status_idx").on(table.status),
}),
);
export const folders = mysqlTable(
"folders",
{
id: nanoId("id").notNull().primaryKey().$type<Folder.FolderId>(),
name: varchar("name", { length: 255 }).notNull(),
color: varchar("color", {
length: 16,
enum: ["normal", "blue", "red", "yellow"],
})
.notNull()
.default("normal"),
// Internet-facing public collection link (/c/[id]).
public: boolean("public").notNull().default(false),
settings: json("settings").$type<{
publicPage?: PublicCollection.PublicPageSettings;
}>(),
organizationId: nanoId("organizationId")
.notNull()
.$type<Organisation.OrganisationId>(),
createdById: nanoId("createdById").notNull().$type<User.UserId>(),
parentId: nanoIdNullable("parentId").$type<Folder.FolderId>(),
spaceId: nanoIdNullable("spaceId").$type<Space.SpaceIdOrOrganisationId>(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
},
(table) => ({
organizationIdIndex: index("organization_id_idx").on(table.organizationId),
createdByIdIndex: index("created_by_id_idx").on(table.createdById),
parentIdIndex: index("parent_id_idx").on(table.parentId),
spaceIdIndex: index("space_id_idx").on(table.spaceId),
publicParentIdIndex: index("public_parent_id_idx").on(
table.public,
table.parentId,
),
publicSpaceParentIdIndex: index("public_space_parent_id_idx").on(
table.public,
table.spaceId,
table.parentId,
),
}),
);
export const videos = mysqlTable(
"videos",
{
id: nanoId("id").notNull().primaryKey().$type<Video.VideoId>(),
ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
orgId: nanoIdRequired("orgId").$type<Organisation.OrganisationId>(),
name: varchar("name", { length: 255 }).notNull().default("My Video"),
bucket: nanoIdNullable("bucket").$type<S3Bucket.S3BucketId>(),
storageIntegrationId: nanoIdNullable("storageIntegrationId")
.references(() => storageIntegrations.id, { onDelete: "restrict" })
.$type<Storage.StorageIntegrationId>(),
// in seconds
duration: float("duration"),
width: int("width"),
height: int("height"),
fps: int("fps"),
metadata: json("metadata").$type<VideoMetadata>(),
public: boolean("public").notNull().default(true),
settings: json("settings").$type<{
disableSummary?: boolean;
disableCaptions?: boolean;
disableChapters?: boolean;
disableReactions?: boolean;
disableTranscript?: boolean;
disableComments?: boolean;
defaultPlaybackSpeed?: number;
callToAction?: VideoCallToAction;
}>(),
transcriptionStatus: varchar("transcriptionStatus", { length: 255 }).$type<
"PROCESSING" | "COMPLETE" | "ERROR" | "SKIPPED" | "NO_AUDIO"
>(),
source: json("source")
.$type<
| { type: "MediaConvert" }
| { type: "local" }
| {
type: "desktopMP4";
outputKey?: string;
audioLevelOutputKey?: string;
audioLevelSourceKey?: string;
thumbnailKey?: string;
previewKey?: string;
}
| { type: "desktopSegments" }
| {
type: "webMP4";
outputKey?: string;
thumbnailKey?: string;
previewKey?: string;
audioLevelOutputKey?: string;
audioLevelSourceKey?: string;
}
>()
.notNull()
.default({ type: "MediaConvert" }),
folderId: nanoIdNullable("folderId").$type<Folder.FolderId>(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
effectiveCreatedAt: datetime("effectiveCreatedAt").generatedAlwaysAs(
sql.raw(
"COALESCE(\n STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(`metadata`, '$.customCreatedAt')), '%Y-%m-%dT%H:%i:%s.%fZ'),\n STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(`metadata`, '$.customCreatedAt')), '%Y-%m-%dT%H:%i:%sZ'),\n `createdAt`\n )",
),
{ mode: "stored" },
),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
// PRIVATE
password: encryptedTextNullable("password"),
// LEGACY
xStreamInfo: text("xStreamInfo"),
firstViewEmailSentAt: timestamp("firstViewEmailSentAt"),
isScreenshot: boolean("isScreenshot").notNull().default(false),
// DEPRECATED
awsRegion: varchar("awsRegion", { length: 255 }),
awsBucket: varchar("awsBucket", { length: 255 }),
videoStartTime: varchar("videoStartTime", { length: 255 }),
audioStartTime: varchar("audioStartTime", { length: 255 }),
jobId: varchar("jobId", { length: 255 }),
jobStatus: varchar("jobStatus", { length: 255 }),
skipProcessing: boolean("skipProcessing").notNull().default(false),
},
(table) => [
index("owner_id_idx").on(table.ownerId),
index("owner_updated_id_idx").on(table.ownerId, table.updatedAt, table.id),
index("is_public_idx").on(table.public),
index("folder_id_idx").on(table.folderId),
index("storage_integration_id_idx").on(table.storageIntegrationId),
index("org_owner_folder_idx").on(
table.orgId,
table.ownerId,
table.folderId,
),
index("org_owner_folder_effective_idx").on(
table.orgId,
table.ownerId,
table.folderId,
table.effectiveCreatedAt,
),
index("org_effective_created_idx").on(
table.orgId,
table.effectiveCreatedAt,
),
index("screenshot_transcription_created_idx").on(
table.isScreenshot,
table.transcriptionStatus,
table.createdAt,
),
],
);
export const videoEdits = mysqlTable("video_edits", {
videoId: nanoId("videoId")
.notNull()
.primaryKey()
.$type<Video.VideoId>()
.references(() => videos.id, { onDelete: "cascade" }),
sourceKey: varchar("sourceKey", { length: 512 }).notNull(),
editSpec: json("editSpec").notNull().$type<VideoEditSpec>(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
});
export const sharedVideos = mysqlTable(
"shared_videos",
{
id: nanoId("id").notNull().primaryKey(),
videoId: nanoId("videoId").notNull().$type<Video.VideoId>(),
folderId: nanoIdNullable("folderId").$type<Folder.FolderId>(),
organizationId: nanoId("organizationId")
.notNull()
.$type<Organisation.OrganisationId>(),
sharedByUserId: nanoId("sharedByUserId").notNull().$type<User.UserId>(),
sharedAt: timestamp("sharedAt").notNull().defaultNow(),
},
(table) => ({
folderIdIndex: index("folder_id_idx").on(table.folderId),
organizationIdIndex: index("organization_id_idx").on(table.organizationId),
sharedByUserIdIndex: index("shared_by_user_id_idx").on(
table.sharedByUserId,
),
videoIdOrganizationIdIndex: index("video_id_organization_id_idx").on(
table.videoId,
table.organizationId,
),
videoIdFolderIdIndex: index("video_id_folder_id_idx").on(
table.videoId,
table.folderId,
),
}),
);
export const videoViewerGrants = mysqlTable(
"video_viewer_grants",
{
id: nanoId("id").notNull().primaryKey(),
videoId: nanoId("videoId")
.notNull()
.$type<Video.VideoId>()
.references(() => videos.id, { onDelete: "cascade" }),
email: varchar("email", { length: 255 }).notNull(),
invitedByUserId: nanoId("invitedByUserId").notNull().$type<User.UserId>(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
revokedAt: timestamp("revokedAt"),
},
(table) => ({
videoEmailUnique: uniqueIndex("video_email_idx").on(
table.videoId,
table.email,
),
}),
);
export const comments = mysqlTable(
"comments",
{
id: nanoId("id").notNull().primaryKey().$type<Comment.CommentId>(),
type: varchar("type", {
length: 6,
enum: ["emoji", "text", "video", "audio"],
}).notNull(),
content: text("content").notNull(),
timestamp: float("timestamp"),
authorId: nanoId("authorId").notNull().$type<User.UserId>(),
videoId: nanoId("videoId").notNull().$type<Video.VideoId>(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
parentCommentId:
nanoIdNullable("parentCommentId").$type<Comment.CommentId>(),
// Media comments ("video"/"audio" type): object key under the parent
// video's prefix (`${ownerId}/${videoId}/comments/${commentId}/...`),
// served via /api/storage/object which asserts that prefix.
mediaKey: varchar("mediaKey", { length: 512 }),
mediaDuration: float("mediaDuration"),
mediaMeta: json("mediaMeta").$type<Comment.MediaMeta>(),
},
(table) => ({
videoTypeCreatedIndex: index("video_type_created_idx").on(
table.videoId,
table.type,
table.createdAt,
table.id,
),
authorIdIndex: index("author_id_idx").on(table.authorId),
parentCommentIdIndex: index("parent_comment_id_idx").on(
table.parentCommentId,
),
}),
);
export const notifications = mysqlTable(
"notifications",
{
id: nanoId("id").notNull().primaryKey(),
orgId: nanoId("orgId").notNull().$type<Organisation.OrganisationId>(),
recipientId: nanoId("recipientId").notNull().$type<User.UserId>(),
type: varchar("type", { length: 16 })
.notNull()
.$type<
"view" | "comment" | "reply" | "reaction" | "anon_view" /*| "mention"*/
>(),
data: json("data")
.$type<{
videoId?: string;
authorId?: string;
comment?: {
id: string;
content: string;
};
anonName?: string;
location?: string | null;
}>()
.notNull(),
videoId: varchar("videoId", { length: 50 }),
dedupKey: varchar("dedupKey", { length: 128 }),
readAt: timestamp("readAt"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
},
(table) => ({
orgIdIndex: index("org_id_idx").on(table.orgId),
typeIndex: index("type_idx").on(table.type),
readAtIndex: index("read_at_idx").on(table.readAt),
createdAtIndex: index("created_at_idx").on(table.createdAt),
recipientReadIndex: index("recipient_read_idx").on(
table.recipientId,
table.readAt,
),
recipientCreatedIndex: index("recipient_created_idx").on(
table.recipientId,
table.createdAt,
),
dedupKeyUnique: uniqueIndex("dedup_key_idx").on(table.dedupKey),
typeRecipientCreatedIndex: index("type_recipient_created_idx").on(
table.type,
table.recipientId,
table.createdAt,
),
typeRecipientVideoCreatedIndex: index(
"type_recipient_video_created_idx",
).on(table.type, table.recipientId, table.videoId, table.createdAt),
videoIdIndex: index("video_id_idx").on(table.videoId),
}),
);
export type MessengerAgent = "Millie";
export type MessengerConversationMode = "agent" | "human";
export type MessengerMessageRole = "user" | "agent" | "admin";
export const messengerConversations = mysqlTable(
"messenger_conversations",
{
id: nanoId("id").notNull().primaryKey(),
agent: varchar("agent", {
length: 32,
enum: ["Millie"],
})
.notNull()
.$type<MessengerAgent>(),
mode: varchar("mode", {
length: 16,
enum: ["agent", "human"],
})
.notNull()
.default("agent")
.$type<MessengerConversationMode>(),
userId: nanoIdNullable("userId").$type<User.UserId>(),
anonymousId: varchar("anonymousId", { length: 64 }),
takeoverByUserId: nanoIdNullable("takeoverByUserId").$type<User.UserId>(),
takeoverAt: timestamp("takeoverAt"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
lastMessageAt: timestamp("lastMessageAt").notNull().defaultNow(),
},
(table) => ({
userLastMessageIndex: index("user_last_message_idx").on(
table.userId,
table.lastMessageAt,
),
anonymousLastMessageIndex: index("anonymous_last_message_idx").on(
table.anonymousId,
table.lastMessageAt,
),
modeLastMessageIndex: index("mode_last_message_idx").on(
table.mode,
table.lastMessageAt,
),
updatedAtIndex: index("updated_at_idx").on(table.updatedAt),
}),
);
export const messengerMessages = mysqlTable(
"messenger_messages",
{
id: nanoId("id").notNull().primaryKey(),
conversationId: nanoId("conversationId")
.notNull()
.references(() => messengerConversations.id, { onDelete: "cascade" }),
role: varchar("role", {
length: 16,
enum: ["user", "agent", "admin"],
})
.notNull()
.$type<MessengerMessageRole>(),
content: text("content").notNull(),
userId: nanoIdNullable("userId").$type<User.UserId>(),
anonymousId: varchar("anonymousId", { length: 64 }),
createdAt: timestamp("createdAt").notNull().defaultNow(),
},
(table) => ({
conversationCreatedAtIndex: index("conversation_created_at_idx").on(
table.conversationId,
table.createdAt,
),
roleCreatedAtIndex: index("role_created_at_idx").on(
table.role,
table.createdAt,
),
}),
);
export const messengerSupportEmails = mysqlTable(
"messenger_support_emails",
{
id: nanoId("id").notNull().primaryKey(),
conversationId: nanoId("conversationId").notNull(),
userId: nanoId("userId").notNull().$type<User.UserId>(),
userEmail: varchar("userEmail", { length: 255 }).notNull(),
subject: varchar("subject", { length: 255 }).notNull(),
message: text("message").notNull(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
},
(table) => ({
conversationForeignKey: foreignKey({
name: "support_email_conversation_fk",
columns: [table.conversationId],
foreignColumns: [messengerConversations.id],
}).onDelete("cascade"),
userCreatedAtIndex: index("support_email_user_created_at_idx").on(
table.userId,
table.createdAt,
),
conversationCreatedAtIndex: index(
"support_email_conversation_created_at_idx",
).on(table.conversationId, table.createdAt),
}),
);
export const signedBaas = mysqlTable(
"signed_baas",
{
id: nanoId("id").notNull().primaryKey(),
organizationId: nanoId("organizationId")
.notNull()
.$type<Organisation.OrganisationId>(),
userId: nanoId("userId").notNull().$type<User.UserId>(),
status: varchar("status", { length: 255 }).notNull().default("pending"),
stripeSubscriptionId: varchar("stripeSubscriptionId", { length: 255 }),
entityName: varchar("entityName", { length: 255 }).notNull(),
entityType: varchar("entityType", { length: 255 }).notNull(),
entityAddress: varchar("entityAddress", { length: 500 }).notNull(),
signerName: varchar("signerName", { length: 255 }).notNull(),
signerTitle: varchar("signerTitle", { length: 255 }).notNull(),
noticesEmail: varchar("noticesEmail", { length: 255 }).notNull(),
signatureData: longtext("signatureData").notNull(),
signedAt: timestamp("signedAt"),
emailSentAt: timestamp("emailSentAt"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
},
(table) => ({
organizationIdIndex: uniqueIndex("signed_baa_organization_id_idx").on(
table.organizationId,
),
stripeSubscriptionIdIndex: index("signed_baa_stripe_subscription_idx").on(
table.stripeSubscriptionId,
),
}),
);
export const s3Buckets = mysqlTable(
"s3_buckets",
{
id: nanoId("id").notNull().primaryKey().$type<S3Bucket.S3BucketId>(),
ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
organizationId:
nanoIdNullable("organizationId").$type<Organisation.OrganisationId>(),
region: encryptedText("region").notNull(),
endpoint: encryptedTextNullable("endpoint"),
bucketName: encryptedText("bucketName").notNull(),
accessKeyId: encryptedText("accessKeyId").notNull(),
secretAccessKey: encryptedText("secretAccessKey").notNull(),
provider: text("provider").notNull().default("aws"),
active: boolean("active").notNull().default(true),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
},
(table) => ({
ownerOrganizationIndex: index("owner_organization_idx").on(
table.ownerId,
table.organizationId,
),
organizationIdIndex: index("organization_id_idx").on(table.organizationId),
organizationActiveIndex: index("organization_active_idx").on(
table.organizationId,
table.active,
table.updatedAt,
),
}),
);
export const storageIntegrations = mysqlTable(
"storage_integrations",
{
id: nanoId("id")
.notNull()
.primaryKey()
.$type<Storage.StorageIntegrationId>(),
ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
organizationId:
nanoIdNullable("organizationId").$type<Organisation.OrganisationId>(),
provider: varchar("provider", { length: 64 })
.notNull()
.$type<Storage.StorageProvider>(),
displayName: varchar("displayName", { length: 255 }).notNull(),
status: varchar("status", { length: 32 })
.notNull()
.default("active")
.$type<Storage.StorageIntegrationStatus>(),
active: boolean("active").notNull().default(false),
encryptedConfig: encryptedText("encryptedConfig").notNull(),
googleDriveAccessToken: encryptedTextNullable("googleDriveAccessToken"),
googleDriveAccessTokenExpiresAt: timestamp(
"googleDriveAccessTokenExpiresAt",
),
googleDriveTokenRefreshLeaseId: varchar("googleDriveTokenRefreshLeaseId", {
length: 64,
}),
googleDriveTokenRefreshLeaseExpiresAt: timestamp(
"googleDriveTokenRefreshLeaseExpiresAt",
),
googleDriveStorageQuotaCache: json(
"googleDriveStorageQuotaCache",
).$type<GoogleDriveStorageQuotaCache>(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
},
(table) => ({
ownerProviderIndex: index("owner_provider_idx").on(
table.ownerId,
table.provider,
),
ownerActiveIndex: index("owner_active_idx").on(table.ownerId, table.active),
organizationProviderIndex: index("organization_provider_idx").on(
table.organizationId,
table.provider,
),
organizationActiveIndex: index("organization_active_idx").on(
table.organizationId,
table.active,
table.status,
),
}),
);
export const storageObjects = mysqlTable(
"storage_objects",
{
id: nanoId("id").notNull().primaryKey().$type<Storage.StorageObjectId>(),
integrationId: nanoId("integrationId")
.notNull()
.references(() => storageIntegrations.id, { onDelete: "restrict" })
.$type<Storage.StorageIntegrationId>(),
ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
videoId: nanoIdNullable("videoId").$type<Video.VideoId>(),
objectKey: text("objectKey").notNull(),
objectKeyHash: varchar("objectKeyHash", { length: 64 }).notNull(),
providerObjectId: varchar("providerObjectId", { length: 255 }).notNull(),
uploadSessionUrl: encryptedTextNullable("uploadSessionUrl"),
uploadStatus: varchar("uploadStatus", { length: 32 })
.notNull()
.default("pending")
.$type<"pending" | "complete" | "error">(),
contentType: varchar("contentType", { length: 255 }),
contentLength: bigint("contentLength", { mode: "number", unsigned: true }),
metadata: json("metadata").$type<Storage.StorageObjectMetadata>(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
},
(table) => ({
integrationKeyHashIndex: uniqueIndex("integration_key_hash_idx").on(
table.integrationId,
table.objectKeyHash,
),
integrationStatusIndex: index("integration_status_idx").on(
table.integrationId,
table.uploadStatus,
),
integrationObjectKeyPrefixIndex: index(
"integration_object_key_prefix_idx",
).on(table.integrationId, sql`${table.objectKey}(191)`),
videoIdIndex: index("video_id_idx").on(table.videoId),
ownerIdIndex: index("owner_id_idx").on(table.ownerId),
}),
);
export const notificationsRelations = relations(notifications, ({ one }) => ({
org: one(organizations, {
fields: [notifications.orgId],
references: [organizations.id],
}),
recipient: one(users, {
fields: [notifications.recipientId],
references: [users.id],
}),
}));
export const authApiKeys = mysqlTable(
"auth_api_keys",
{
id: varchar("id", { length: 36 }).notNull().primaryKey(),
userId: nanoId("userId").notNull().$type<User.UserId>(),
source: varchar("source", { length: 32 })
.notNull()
.default("unknown")
.$type<AuthApiKeySource>(),
createdAt: timestamp("createdAt").defaultNow().notNull(),
},
(table) => ({
userIdCreatedAtIndex: index("user_id_created_at_idx").on(
table.userId,
table.createdAt,
),
}),
);
export const agentApiKeys = mysqlTable(
"agent_api_keys",
{
id: nanoId("id").notNull().primaryKey(),
userId: nanoId("userId").notNull().$type<User.UserId>(),
tokenHash: varchar("tokenHash", { length: 64 }).notNull(),
name: varchar("name", { length: 100 }).notNull().default("Cap CLI"),
scopes: json("scopes").notNull().$type<Agent.AgentScope[]>(),
expiresAt: timestamp("expiresAt").notNull(),
revokedAt: timestamp("revokedAt"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
lastUsedAt: timestamp("lastUsedAt"),
},
(table) => [
uniqueIndex("token_hash_idx").on(table.tokenHash),
index("user_created_at_idx").on(table.userId, table.createdAt),
index("expires_at_idx").on(table.expiresAt),
],
);
export const agentApiIdempotency = mysqlTable(
"agent_api_idempotency",
{
id: nanoId("id").notNull().primaryKey(),
userId: nanoId("userId").notNull().$type<User.UserId>(),
operation: varchar("operation", { length: 64 }).notNull(),
keyHash: varchar("keyHash", { length: 64 }).notNull(),
requestHash: varchar("requestHash", { length: 64 }).notNull(),
state: varchar("state", {
length: 16,
enum: ["pending", "complete"],
})
.notNull()
.default("pending"),
statusCode: int("statusCode"),
response: json("response").$type<unknown>(),
expiresAt: timestamp("expiresAt").notNull(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
},
(table) => [
uniqueIndex("user_operation_key_idx").on(
table.userId,
table.operation,
table.keyHash,
),
index("expires_at_idx").on(table.expiresAt),
],
);
export const agentApiAuthorizationCodes = mysqlTable(
"agent_api_authorization_codes",
{
id: nanoId("id").notNull().primaryKey(),
userId: nanoId("userId").notNull().$type<User.UserId>(),
codeHash: varchar("codeHash", { length: 64 }).notNull(),
codeChallenge: varchar("codeChallenge", { length: 64 }).notNull(),
redirectUri: varchar("redirectUri", { length: 512 }).notNull(),
scopes: json("scopes").notNull().$type<Agent.AgentScope[]>(),
expiresAt: timestamp("expiresAt").notNull(),
consumedAt: timestamp("consumedAt"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
},
(table) => [
uniqueIndex("code_hash_idx").on(table.codeHash),
index("expires_at_idx").on(table.expiresAt),
index("user_created_at_idx").on(table.userId, table.createdAt),
],
);
export const mcpOAuthClients = mysqlTable(
"mcp_oauth_clients",
{
id: nanoId("id").notNull().primaryKey(),
clientId: varchar("clientId", { length: 128 }).notNull(),
clientName: varchar("clientName", { length: 100 }).notNull(),
redirectUris: json("redirectUris").notNull().$type<string[]>(),
activatedAt: timestamp("activatedAt"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
},
(table) => [
uniqueIndex("client_id_idx").on(table.clientId),
index("inactive_created_at_idx").on(table.activatedAt, table.createdAt),
],
);
export const mcpOAuthRegistrationQuotas = mysqlTable(
"mcp_oauth_registration_quotas",
{
windowId: varchar("windowId", { length: 10 }).notNull().primaryKey(),
registrations: int("registrations").notNull().default(0),
expiresAt: timestamp("expiresAt").notNull(),
},
(table) => [index("expires_at_idx").on(table.expiresAt)],
);
export const mcpOAuthCodes = mysqlTable(
"mcp_oauth_codes",
{
id: nanoId("id").notNull().primaryKey(),
userId: nanoId("userId").notNull().$type<User.UserId>(),
clientId: varchar("clientId", { length: 128 }).notNull(),
codeHash: varchar("codeHash", { length: 64 }).notNull(),
codeChallenge: varchar("codeChallenge", { length: 64 }).notNull(),
redirectUri: varchar("redirectUri", { length: 512 }).notNull(),
resource: varchar("resource", { length: 512 }).notNull(),
expiresAt: timestamp("expiresAt").notNull(),
consumedAt: timestamp("consumedAt"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
},
(table) => [
uniqueIndex("code_hash_idx").on(table.codeHash),
index("expires_at_idx").on(table.expiresAt),
],
);
export const mcpOAuthTokens = mysqlTable(
"mcp_oauth_tokens",
{
id: nanoId("id").notNull().primaryKey(),
userId: nanoId("userId").notNull().$type<User.UserId>(),
clientId: varchar("clientId", { length: 128 }).notNull(),
familyId: nanoId("familyId").notNull(),
resource: varchar("resource", { length: 512 }).notNull(),
accessHash: varchar("accessHash", { length: 64 }).notNull(),
refreshHash: varchar("refreshHash", { length: 64 }).notNull(),
accessExpiresAt: timestamp("accessExpiresAt").notNull(),
refreshExpiresAt: timestamp("refreshExpiresAt").notNull(),
revokedAt: timestamp("revokedAt"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
},
(table) => [
uniqueIndex("access_hash_idx").on(table.accessHash),
uniqueIndex("refresh_hash_idx").on(table.refreshHash),
index("family_id_idx").on(table.familyId),
index("client_active_idx").on(
table.clientId,
table.revokedAt,
table.refreshExpiresAt,
),
index("refresh_expires_at_idx").on(table.refreshExpiresAt),
],
);
export const agentApiOperations = mysqlTable(
"agent_api_operations",
{
id: nanoId("id").notNull().primaryKey(),
userId: nanoId("userId").notNull().$type<User.UserId>(),
kind: varchar("kind", {
length: 32,
enum: [
"duplicate_cap",
"delete_cap",
"import_loom",
"delete_organization",
"set_organization_domain",
"remove_organization_domain",
"verify_organization_domain",
"transfer_org_content",
],
}).notNull(),
resourceId: nanoId("resourceId").notNull(),
resultResourceId: nanoIdNullable("resultResourceId"),
state: varchar("state", {
length: 16,
enum: ["queued", "running", "succeeded", "failed"],
})
.notNull()
.default("queued"),
payload: json("payload").notNull().$type<unknown>(),
result: json("result").$type<unknown>(),
errorCode: varchar("errorCode", { length: 64 }),
errorMessage: text("errorMessage"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
completedAt: timestamp("completedAt"),
},
(table) => [
index("user_created_at_idx").on(table.userId, table.createdAt),
index("state_updated_at_idx").on(table.state, table.updatedAt),
index("resource_id_idx").on(table.resourceId),
],
);
export const commentsRelations = relations(comments, ({ one }) => ({
author: one(users, {
fields: [comments.authorId],
references: [users.id],
}),
video: one(videos, {
fields: [comments.videoId],
references: [videos.id],
}),
parentComment: one(comments, {
fields: [comments.parentCommentId],
references: [comments.id],
}),
}));
export const messengerConversationsRelations = relations(
messengerConversations,
({ one, many }) => ({
user: one(users, {
fields: [messengerConversations.userId],
references: [users.id],
}),
messages: many(messengerMessages),
supportEmails: many(messengerSupportEmails),
}),
);
export const messengerMessagesRelations = relations(
messengerMessages,
({ one }) => ({
conversation: one(messengerConversations, {
fields: [messengerMessages.conversationId],
references: [messengerConversations.id],
}),
user: one(users, {
fields: [messengerMessages.userId],
references: [users.id],
}),
}),
);
export const messengerSupportEmailsRelations = relations(
messengerSupportEmails,
({ one }) => ({
conversation: one(messengerConversations, {
fields: [messengerSupportEmails.conversationId],
references: [messengerConversations.id],
}),
user: one(users, {
fields: [messengerSupportEmails.userId],
references: [users.id],
}),
}),
);
// Define Relationships
export const usersRelations = relations(users, ({ many, one }) => ({
accounts: many(accounts),
sessions: many(sessions),
organizationMembers: many(organizationMembers),
videos: many(videos),
sharedVideos: many(sharedVideos),
customBucket: one(s3Buckets),
storageIntegrations: many(storageIntegrations),
spaces: many(spaces),
spaceMembers: many(spaceMembers),
messengerConversations: many(messengerConversations),
messengerMessages: many(messengerMessages),
messengerSupportEmails: many(messengerSupportEmails),
integrationInstallations: many(integrationInstallations),
}));
export const accountsRelations = relations(accounts, ({ one }) => ({
user: one(users, {
fields: [accounts.userId],
references: [users.id],
}),
}));
export const s3BucketsRelations = relations(s3Buckets, ({ one }) => ({
owner: one(users, {
fields: [s3Buckets.ownerId],
references: [users.id],
}),
organization: one(organizations, {
fields: [s3Buckets.organizationId],
references: [organizations.id],
}),
}));
export const storageIntegrationsRelations = relations(
storageIntegrations,
({ one, many }) => ({
owner: one(users, {
fields: [storageIntegrations.ownerId],
references: [users.id],
}),
organization: one(organizations, {
fields: [storageIntegrations.organizationId],
references: [organizations.id],
}),
objects: many(storageObjects),
}),
);
export const storageObjectsRelations = relations(storageObjects, ({ one }) => ({
integration: one(storageIntegrations, {
fields: [storageObjects.integrationId],
references: [storageIntegrations.id],
}),
video: one(videos, {
fields: [storageObjects.videoId],
references: [videos.id],
}),
}));
export const organizationsRelations = relations(
organizations,
({ one, many }) => ({
owner: one(users, {
fields: [organizations.ownerId],
references: [users.id],
}),
organizationMembers: many(organizationMembers),
sharedVideos: many(sharedVideos),
organizationInvites: many(organizationInvites),
spaces: many(spaces),
s3Buckets: many(s3Buckets),
storageIntegrations: many(storageIntegrations),
integrationInstallations: many(integrationInstallations),
}),
);
export const sessionsRelations = relations(sessions, ({ one }) => ({
user: one(users, {
fields: [sessions.userId],
references: [users.id],
}),
}));
export const verificationTokensRelations = relations(
verificationTokens,
() => ({
// No relations defined
}),
);
export const organizationMembersRelations = relations(
organizationMembers,
({ one }) => ({
user: one(users, {
fields: [organizationMembers.userId],
references: [users.id],
}),
organization: one(organizations, {
fields: [organizationMembers.organizationId],
references: [organizations.id],
}),
}),
);
export const integrationInstallationsRelations = relations(
integrationInstallations,
({ one }) => ({
organization: one(organizations, {
fields: [integrationInstallations.organizationId],
references: [organizations.id],
}),
installedByUser: one(users, {
fields: [integrationInstallations.installedByUserId],
references: [users.id],
}),
}),
);
export const organizationInvitesRelations = relations(
organizationInvites,
({ one }) => ({
organization: one(organizations, {
fields: [organizationInvites.organizationId],
references: [organizations.id],
}),
invitedByUser: one(users, {
fields: [organizationInvites.invitedByUserId],
references: [users.id],
}),
}),
);
export const videosRelations = relations(videos, ({ one, many }) => ({
owner: one(users, {
fields: [videos.ownerId],
references: [users.id],
}),
sharedVideos: many(sharedVideos),
spaceVideos: many(spaceVideos),
edit: one(videoEdits, {
fields: [videos.id],
references: [videoEdits.videoId],
}),
folder: one(folders, {
fields: [videos.folderId],
references: [folders.id],
}),
storageIntegration: one(storageIntegrations, {
fields: [videos.storageIntegrationId],
references: [storageIntegrations.id],
}),
}));
export const videoEditsRelations = relations(videoEdits, ({ one }) => ({
video: one(videos, {
fields: [videoEdits.videoId],
references: [videos.id],
}),
}));
export const sharedVideosRelations = relations(sharedVideos, ({ one }) => ({
video: one(videos, {
fields: [sharedVideos.videoId],
references: [videos.id],
}),
organization: one(organizations, {
fields: [sharedVideos.organizationId],
references: [organizations.id],
}),
sharedByUser: one(users, {
fields: [sharedVideos.sharedByUserId],
references: [users.id],
}),
}));
export const spaces = mysqlTable(
"spaces",
{
id: nanoId("id")
.notNull()
.primaryKey()
.$type<Space.SpaceIdOrOrganisationId>(),
primary: boolean("primary").notNull().default(false),
name: varchar("name", { length: 255 }).notNull(),
organizationId: nanoId("organizationId")
.notNull()
.$type<Organisation.OrganisationId>(),
createdById: nanoId("createdById").notNull().$type<User.UserId>(),
iconUrl: varchar("iconUrl", {
length: 255,
}).$type<ImageUpload.ImageUrlOrKey>(),
description: varchar("description", { length: 1000 }),
settings: json("settings").$type<{
disableSummary?: boolean;
disableCaptions?: boolean;
disableChapters?: boolean;
disableReactions?: boolean;
disableTranscript?: boolean;
disableComments?: boolean;
publicPage?: PublicCollection.PublicPageSettings;
}>(),
password: encryptedTextNullable("password"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
// Org-internal browsability: "Public" spaces are visible to all org
// members. Unrelated to the internet-facing `public` flag below.
privacy: varchar("privacy", { length: 255, enum: ["Public", "Private"] })
.notNull()
.default("Private"),
// Internet-facing public collection link (/c/[id]).
public: boolean("public").notNull().default(false),
},
(table) => ({
organizationIdIndex: index("organization_id_idx").on(table.organizationId),
createdByIdIndex: index("created_by_id_idx").on(table.createdById),
publicOrganizationIdIndex: index("public_organization_id_idx").on(
table.public,
table.organizationId,
),
}),
);
export const spaceMembers = mysqlTable(
"space_members",
{
id: nanoId("id").notNull().primaryKey(),
spaceId: nanoId("spaceId").notNull().$type<Space.SpaceIdOrOrganisationId>(),
userId: nanoId("userId").notNull().$type<User.UserId>(),
role: varchar("role", { length: 255 })
.notNull()
.default("member")
.$type<"admin" | "member">(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
},
(table) => ({
userIdIndex: index("user_id_idx").on(table.userId),
spaceIdUserIdUnique: unique("space_id_user_id_unique").on(
table.spaceId,
table.userId,
),
}),
);
export const spaceVideos = mysqlTable(
"space_videos",
{
id: nanoId("id").notNull().primaryKey(),
spaceId: nanoId("spaceId").notNull().$type<Space.SpaceIdOrOrganisationId>(),
folderId: nanoIdNullable("folderId").$type<Folder.FolderId>(),
videoId: nanoId("videoId").notNull().$type<Video.VideoId>(),
addedById: nanoId("addedById").notNull().$type<User.UserId>(),
addedAt: timestamp("addedAt").notNull().defaultNow(),
},
(table) => ({
folderIdIndex: index("folder_id_idx").on(table.folderId),
videoIdIndex: index("video_id_idx").on(table.videoId),
addedByIdIndex: index("added_by_id_idx").on(table.addedById),
spaceIdVideoIdIndex: index("space_id_video_id_idx").on(
table.spaceId,
table.videoId,
),
spaceIdFolderIdIndex: index("space_id_folder_id_idx").on(
table.spaceId,
table.folderId,
),
}),
);
export const spacesRelations = relations(spaces, ({ one, many }) => ({
organization: one(organizations, {
fields: [spaces.organizationId],
references: [organizations.id],
}),
createdBy: one(users, {
fields: [spaces.createdById],
references: [users.id],
}),
spaceMembers: many(spaceMembers),
spaceVideos: many(spaceVideos),
}));
export const spaceMembersRelations = relations(spaceMembers, ({ one }) => ({
space: one(spaces, {
fields: [spaceMembers.spaceId],
references: [spaces.id],
}),
user: one(users, {
fields: [spaceMembers.userId],
references: [users.id],
}),
}));
export const spaceVideosRelations = relations(spaceVideos, ({ one }) => ({
space: one(spaces, {
fields: [spaceVideos.spaceId],
references: [spaces.id],
}),
video: one(videos, {
fields: [spaceVideos.videoId],
references: [videos.id],
}),
addedBy: one(users, {
fields: [spaceVideos.addedById],
references: [users.id],
}),
}));
export const foldersRelations = relations(folders, ({ one, many }) => ({
organization: one(organizations, {
fields: [folders.organizationId],
references: [organizations.id],
}),
createdBy: one(users, {
fields: [folders.createdById],
references: [users.id],
}),
parentFolder: one(folders, {
fields: [folders.parentId],
references: [folders.id],
relationName: "parentChild",
}),
childFolders: many(folders, { relationName: "parentChild" }),
videos: many(videos),
}));
export const videoUploads = mysqlTable(
"video_uploads",
{
videoId: nanoId("video_id").primaryKey().notNull().$type<Video.VideoId>(),
uploaded: bigint("uploaded", { mode: "number", unsigned: true })
.notNull()
.$defaultFn(() => 0),
total: bigint("total", { mode: "number", unsigned: true })
.notNull()
.$defaultFn(() => 0),
startedAt: timestamp("started_at").notNull().defaultNow(),
updatedAt: timestamp("updated_at").notNull().defaultNow(),
mode: varchar("mode", { length: 255, enum: ["singlepart", "multipart"] }),
phase: varchar("phase", { length: 32 })
.$type<
| "uploading"
| "processing"
| "generating_thumbnail"
| "complete"
| "error"
>()
.notNull()
.default("uploading"),
processingProgress: int("processing_progress").notNull().default(0),
processingMessage: varchar("processing_message", { length: 255 }),
processingError: text("processing_error"),
rawFileKey: varchar("raw_file_key", { length: 512 }),
},
(table) => [
index("phase_updated_at_video_id_idx").on(
table.phase,
table.updatedAt,
table.videoId,
),
index("started_at_idx").on(table.startedAt),
],
);
export const videoProcessingJobs = mysqlTable(
"video_processing_jobs",
{
videoId: nanoId("video_id").primaryKey().notNull().$type<Video.VideoId>(),
ownerId: nanoId("owner_id").notNull().$type<User.UserId>(),
generation: varchar("generation", { length: 64 }).notNull(),
manifestSha256: varchar("manifest_sha256", { length: 64 }),
state: varchar("state", { length: 32 })
.$type<
| "committing"
| "queued"
| "processing"
| "retry"
| "verified"
| "source-blocked"
>()
.notNull()
.default("committing"),
attemptId: varchar("attempt_id", { length: 64 }),
attemptCount: int("attempt_count").notNull().default(0),
leaseExpiresAt: datetime("lease_expires_at", { fsp: 3 }),
nextRetryAt: datetime("next_retry_at", { fsp: 3 }).notNull(),
workflowRunId: varchar("workflow_run_id", { length: 255 }),
remoteJobId: varchar("remote_job_id", { length: 255 }),
source: json("source").$type<unknown>(),
verification: json("verification").$type<unknown>(),
output: json("output").$type<unknown>(),
errorCode: varchar("error_code", { length: 64 }),
errorMessage: text("error_message"),
createdAt: datetime("created_at", { fsp: 3 })
.notNull()
.$defaultFn(() => new Date()),
updatedAt: datetime("updated_at", { fsp: 3 })
.notNull()
.$defaultFn(() => new Date()),
},
(table) => [
index("processing_state_retry_video_idx").on(
table.state,
table.nextRetryAt,
table.videoId,
),
index("processing_state_lease_video_idx").on(
table.state,
table.leaseExpiresAt,
table.videoId,
),
],
);
export const mediaProcessingBudgets = mysqlTable(
"media_processing_budgets",
{
id: varchar("id", { length: 64 }).primaryKey().notNull(),
reservedBytes: bigint("reserved_bytes", { mode: "number", unsigned: true })
.notNull()
.default(0),
limitBytes: bigint("limit_bytes", {
mode: "number",
unsigned: true,
}).notNull(),
expiresAt: datetime("expires_at", { fsp: 3 }).notNull(),
},
(table) => [index("media_budget_expiry_idx").on(table.expiresAt)],
);
export const importedVideos = mysqlTable(
"imported_videos",
{
id: nanoId("id").notNull(),
orgId: nanoIdNullable("orgId")
.notNull()
.$type<Organisation.OrganisationId>(),
source: varchar("source", { length: 255, enum: ["loom"] }).notNull(),
sourceId: varchar("source_id", { length: 255 }).notNull(),
},
(table) => [
primaryKey({ columns: [table.orgId, table.source, table.sourceId] }),
index("id_idx").on(table.id),
],
);
export const loomMigrationRequests = mysqlTable(
"loom_migration_requests",
{
id: nanoId("id").notNull().primaryKey(),
organizationId: nanoId("organizationId")
.notNull()
.$type<Organisation.OrganisationId>(),
activeOrganizationId: nanoIdNullable(
"activeOrganizationId",
).$type<Organisation.OrganisationId>(),
requestedByUserId: nanoId("requestedByUserId")
.notNull()
.$type<User.UserId>(),
workspaceName: varchar("workspaceName", { length: 255 }),
customerNote: text("customerNote"),
customerReply: text("customerReply"),
invitedAt: datetime("invitedAt", { mode: "date" }),
status: varchar("status", {
length: 32,
enum: ["pending", "in_progress", "needs_information", "completed"],
})
.notNull()
.default("pending"),
capMessage: text("capMessage"),
expectedVideoCount: int("expectedVideoCount"),
importedVideoCount: int("importedVideoCount").notNull().default(0),
queuedVideoCount: int("queuedVideoCount").notNull().default(0),
activeImportCount: int("activeImportCount").notNull().default(0),
activeImportLeaseToken: varchar("activeImportLeaseToken", { length: 36 }),
activeImportLeaseUntil: datetime("activeImportLeaseUntil", {
mode: "date",
}),
lastOperatorUserId:
nanoIdNullable("lastOperatorUserId").$type<User.UserId>(),
lastOperatorAt: datetime("lastOperatorAt", { mode: "date" }),
completedAt: datetime("completedAt", { mode: "date" }),
createdAt: datetime("createdAt", { mode: "date" })
.notNull()
.$defaultFn(() => new Date()),
updatedAt: datetime("updatedAt", { mode: "date" })
.notNull()
.$defaultFn(() => new Date()),
},
(table) => [
uniqueIndex("loom_migration_active_org_idx").on(table.activeOrganizationId),
index("loom_migration_org_created_idx").on(
table.organizationId,
table.createdAt,
),
index("loom_migration_status_created_idx").on(
table.status,
table.createdAt,
),
],
);
export const loomMigrationImports = mysqlTable(
"loom_migration_imports",
{
videoId: nanoId("videoId").notNull().primaryKey().$type<Video.VideoId>(),
requestId: nanoId("requestId").notNull(),
createdAt: datetime("createdAt", { mode: "date" })
.notNull()
.$defaultFn(() => new Date()),
},
(table) => [index("loom_migration_imports_request_idx").on(table.requestId)],
);
export const developerApps = mysqlTable(
"developer_apps",
{
id: nanoId("id").notNull().primaryKey(),
ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
name: varchar("name", { length: 255 }).notNull(),
environment: varchar("environment", {
length: 16,
enum: ["development", "production"],
}).notNull(),
logoUrl: varchar("logoUrl", { length: 1024 }),
deletedAt: timestamp("deletedAt"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
},
(table) => [index("owner_deleted_idx").on(table.ownerId, table.deletedAt)],
);
export const developerAppDomains = mysqlTable(
"developer_app_domains",
{
id: nanoId("id").notNull().primaryKey(),
appId: nanoId("appId")
.notNull()
.references(() => developerApps.id),
domain: varchar("domain", { length: 253 }).notNull(),
createdAt: timestamp("createdAt").notNull().defaultNow(),
},
(table) => [unique("app_domain_unique").on(table.appId, table.domain)],
);
export const developerApiKeys = mysqlTable(
"developer_api_keys",
{
id: nanoId("id").notNull().primaryKey(),
appId: nanoId("appId")
.notNull()
.references(() => developerApps.id),
keyType: varchar("keyType", {
length: 8,
enum: ["public", "secret"],
}).notNull(),
keyPrefix: varchar("keyPrefix", { length: 12 }).notNull(),
keyHash: varchar("keyHash", { length: 64 }).notNull(),
encryptedKey: encryptedText("encryptedKey").notNull(),
lastUsedAt: timestamp("lastUsedAt"),
revokedAt: timestamp("revokedAt"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
},
(table) => [
uniqueIndex("key_hash_idx").on(table.keyHash),
index("app_key_type_idx").on(table.appId, table.keyType),
],
);
export const developerVideos = mysqlTable(
"developer_videos",
{
id: nanoId("id").notNull().primaryKey(),
appId: nanoId("appId")
.notNull()
.references(() => developerApps.id),
externalUserId: varchar("externalUserId", { length: 255 }),
name: varchar("name", { length: 255 }).notNull().default("Untitled"),
duration: float("duration"),
width: int("width"),
height: int("height"),
fps: int("fps"),
s3Key: varchar("s3Key", { length: 512 }),
transcriptionStatus: varchar("transcriptionStatus", {
length: 16,
enum: ["PROCESSING", "COMPLETE", "ERROR", "SKIPPED", "NO_AUDIO"],
}),
metadata: json("metadata"),
deletedAt: timestamp("deletedAt"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
},
(table) => [
index("app_created_idx").on(table.appId, table.createdAt),
index("app_user_idx").on(table.appId, table.externalUserId),
index("app_deleted_idx").on(table.appId, table.deletedAt),
],
);
export const developerCreditAccounts = mysqlTable(
"developer_credit_accounts",
{
id: nanoId("id").notNull().primaryKey(),
appId: nanoId("appId")
.notNull()
.references(() => developerApps.id),
ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
balanceMicroCredits: bigint("balanceMicroCredits", {
mode: "number",
unsigned: true,
})
.notNull()
.default(0),
stripeCustomerId: varchar("stripeCustomerId", { length: 255 }),
stripePaymentMethodId: varchar("stripePaymentMethodId", { length: 255 }),
autoTopUpEnabled: boolean("autoTopUpEnabled").notNull().default(false),
autoTopUpThresholdMicroCredits: bigint("autoTopUpThresholdMicroCredits", {
mode: "number",
unsigned: true,
})
.notNull()
.default(0),
autoTopUpAmountCents: int("autoTopUpAmountCents").notNull().default(0),
createdAt: timestamp("createdAt").notNull().defaultNow(),
updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
},
(table) => [uniqueIndex("app_id_unique").on(table.appId)],
);
export type DeveloperCreditTransactionType =
| "topup"
| "video_create"
| "storage_daily"
| "refund"
| "adjustment";
export type DeveloperCreditReferenceType =
| "developer_video"
| "stripe_payment_intent"
| "manual";
export const developerCreditTransactions = mysqlTable(
"developer_credit_transactions",
{
id: nanoId("id").notNull().primaryKey(),
accountId: nanoId("accountId").notNull(),
type: varchar("type", {
length: 16,
enum: ["topup", "video_create", "storage_daily", "refund", "adjustment"],
})
.notNull()
.$type<DeveloperCreditTransactionType>(),
amountMicroCredits: bigint("amountMicroCredits", {
mode: "number",
}).notNull(),
balanceAfterMicroCredits: bigint("balanceAfterMicroCredits", {
mode: "number",
unsigned: true,
}).notNull(),
referenceId: varchar("referenceId", { length: 255 }),
referenceType: varchar("referenceType", {
length: 32,
enum: ["developer_video", "stripe_payment_intent", "manual"],
}).$type<DeveloperCreditReferenceType>(),
metadata: json("metadata"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
},
(table) => [
foreignKey({
name: "dev_credit_txn_account_fk",
columns: [table.accountId],
foreignColumns: [developerCreditAccounts.id],
}),
index("account_type_created_idx").on(
table.accountId,
table.type,
table.createdAt,
),
index("account_ref_dedup_idx").on(
table.accountId,
table.referenceId,
table.referenceType,
),
],
);
export const developerDailyStorageSnapshots = mysqlTable(
"developer_daily_storage_snapshots",
{
id: nanoId("id").notNull().primaryKey(),
appId: nanoId("appId")
.notNull()
.references(() => developerApps.id),
snapshotDate: varchar("snapshotDate", { length: 10 }).notNull(),
totalDurationMinutes: float("totalDurationMinutes").notNull().default(0),
videoCount: int("videoCount").notNull().default(0),
microCreditsCharged: bigint("microCreditsCharged", {
mode: "number",
unsigned: true,
})
.notNull()
.default(0),
processedAt: timestamp("processedAt"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
},
(table) => [unique("app_date_unique").on(table.appId, table.snapshotDate)],
);
export const developerAppsRelations = relations(
developerApps,
({ one, many }) => ({
owner: one(users, {
fields: [developerApps.ownerId],
references: [users.id],
}),
domains: many(developerAppDomains),
apiKeys: many(developerApiKeys),
videos: many(developerVideos),
creditAccount: one(developerCreditAccounts, {
fields: [developerApps.id],
references: [developerCreditAccounts.appId],
}),
storageSnapshots: many(developerDailyStorageSnapshots),
}),
);
export const developerAppDomainsRelations = relations(
developerAppDomains,
({ one }) => ({
app: one(developerApps, {
fields: [developerAppDomains.appId],
references: [developerApps.id],
}),
}),
);
export const developerApiKeysRelations = relations(
developerApiKeys,
({ one }) => ({
app: one(developerApps, {
fields: [developerApiKeys.appId],
references: [developerApps.id],
}),
}),
);
export const developerVideosRelations = relations(
developerVideos,
({ one }) => ({
app: one(developerApps, {
fields: [developerVideos.appId],
references: [developerApps.id],
}),
}),
);
export const developerCreditAccountsRelations = relations(
developerCreditAccounts,
({ one, many }) => ({
app: one(developerApps, {
fields: [developerCreditAccounts.appId],
references: [developerApps.id],
}),
owner: one(users, {
fields: [developerCreditAccounts.ownerId],
references: [users.id],
}),
transactions: many(developerCreditTransactions),
}),
);
export const developerCreditTransactionsRelations = relations(
developerCreditTransactions,
({ one }) => ({
account: one(developerCreditAccounts, {
fields: [developerCreditTransactions.accountId],
references: [developerCreditAccounts.id],
}),
}),
);
export const developerDailyStorageSnapshotsRelations = relations(
developerDailyStorageSnapshots,
({ one }) => ({
app: one(developerApps, {
fields: [developerDailyStorageSnapshots.appId],
references: [developerApps.id],
}),
}),
);