⭐️⭐️⭐️ Open-source, self-hosted alternative to Linear and Plane. Project management and issue tracking where teams and AI agents work side by side to plan and ship products.
TypeScript agenticagileai-agentsbug-reportingbug-trackerganttissue-trackerjirajira-alternativekanbanlinearlinear-alternativeopen-sourceproduct-managementproject-managementroadmapself-hostedsprint-planningteam-collaborationtrello-alternative
import { relations } from "drizzle-orm";
import {
pgTable,
text,
timestamp,
boolean,
integer,
jsonb,
index,
uniqueIndex,
} from "drizzle-orm/pg-core";
export const user = pgTable("user", {
id: text("id").primaryKey(),
name: text("name").notNull(),
email: text("email").notNull().unique(),
emailVerified: boolean("email_verified").default(false).notNull(),
image: text("image"),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at")
.defaultNow()
.$onUpdate(() => /* @__PURE__ */ new Date())
.notNull(),
username: text("username").unique(),
displayUsername: text("display_username"),
role: text("role").default("user"),
active: boolean("active").default(true),
lastSeenAt: timestamp("last_seen_at"),
});
export const session = pgTable(
"session",
{
id: text("id").primaryKey(),
expiresAt: timestamp("expires_at").notNull(),
token: text("token").notNull().unique(),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at")
.$onUpdate(() => /* @__PURE__ */ new Date())
.notNull(),
ipAddress: text("ip_address"),
userAgent: text("user_agent"),
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
},
(table) => [index("session_userId_idx").on(table.userId)],
);
export const account = pgTable(
"account",
{
id: text("id").primaryKey(),
accountId: text("account_id").notNull(),
providerId: text("provider_id").notNull(),
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
accessToken: text("access_token"),
refreshToken: text("refresh_token"),
idToken: text("id_token"),
accessTokenExpiresAt: timestamp("access_token_expires_at"),
refreshTokenExpiresAt: timestamp("refresh_token_expires_at"),
scope: text("scope"),
password: text("password"),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at")
.$onUpdate(() => /* @__PURE__ */ new Date())
.notNull(),
},
(table) => [index("account_userId_idx").on(table.userId)],
);
export const verification = pgTable(
"verification",
{
id: text("id").primaryKey(),
identifier: text("identifier").notNull(),
value: text("value").notNull(),
expiresAt: timestamp("expires_at").notNull(),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at")
.defaultNow()
.$onUpdate(() => /* @__PURE__ */ new Date())
.notNull(),
},
(table) => [index("verification_identifier_idx").on(table.identifier)],
);
export const passkey = pgTable(
"passkey",
{
id: text("id").primaryKey(),
name: text("name"),
publicKey: text("public_key").notNull(),
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
credentialID: text("credential_id").notNull(),
counter: integer("counter").notNull(),
deviceType: text("device_type").notNull(),
backedUp: boolean("backed_up").notNull(),
transports: text("transports"),
createdAt: timestamp("created_at"),
aaguid: text("aaguid"),
},
(table) => [
index("passkey_userId_idx").on(table.userId),
index("passkey_credentialID_idx").on(table.credentialID),
],
);
export const apikey = pgTable(
"apikey",
{
id: text("id").primaryKey(),
configId: text("config_id").default("default").notNull(),
name: text("name"),
start: text("start"),
referenceId: text("reference_id").notNull(),
prefix: text("prefix"),
key: text("key").notNull(),
refillInterval: integer("refill_interval"),
refillAmount: integer("refill_amount"),
lastRefillAt: timestamp("last_refill_at"),
enabled: boolean("enabled").default(true),
rateLimitEnabled: boolean("rate_limit_enabled").default(true),
rateLimitTimeWindow: integer("rate_limit_time_window").default(1000),
rateLimitMax: integer("rate_limit_max").default(100),
requestCount: integer("request_count").default(0),
remaining: integer("remaining"),
lastRequest: timestamp("last_request"),
expiresAt: timestamp("expires_at"),
createdAt: timestamp("created_at").notNull(),
updatedAt: timestamp("updated_at").notNull(),
permissions: text("permissions"),
metadata: text("metadata"),
},
(table) => [
index("apikey_configId_idx").on(table.configId),
index("apikey_referenceId_idx").on(table.referenceId),
index("apikey_key_idx").on(table.key),
],
);
export const jwks = pgTable("jwks", {
id: text("id").primaryKey(),
publicKey: text("public_key").notNull(),
privateKey: text("private_key").notNull(),
createdAt: timestamp("created_at").notNull(),
expiresAt: timestamp("expires_at"),
alg: text("alg"),
crv: text("crv"),
});
export const oauthClient = pgTable(
"oauth_client",
{
id: text("id").primaryKey(),
clientId: text("client_id").notNull().unique(),
clientSecret: text("client_secret"),
clientDiscoveryId: text("client_discovery_id"),
disabled: boolean("disabled").default(false),
skipConsent: boolean("skip_consent"),
enableEndSession: boolean("enable_end_session"),
subjectType: text("subject_type"),
scopes: text("scopes").array(),
clientCredentialsScopes: text("client_credentials_scopes")
.array()
.default([]),
userId: text("user_id").references(() => user.id, { onDelete: "cascade" }),
createdAt: timestamp("created_at"),
updatedAt: timestamp("updated_at"),
name: text("name"),
uri: text("uri"),
icon: text("icon"),
contacts: text("contacts").array(),
tos: text("tos"),
policy: text("policy"),
softwareId: text("software_id"),
softwareVersion: text("software_version"),
softwareStatement: text("software_statement"),
redirectUris: text("redirect_uris").array().notNull(),
postLogoutRedirectUris: text("post_logout_redirect_uris").array(),
backchannelLogoutUri: text("backchannel_logout_uri"),
backchannelLogoutSessionRequired: boolean(
"backchannel_logout_session_required",
),
tokenEndpointAuthMethod: text("token_endpoint_auth_method"),
applicationType: text("application_type"),
jwks: text("jwks"),
jwksUri: text("jwks_uri"),
grantTypes: text("grant_types").array(),
responseTypes: text("response_types").array(),
requirePKCE: boolean("require_pkce"),
dpopBoundAccessTokens: boolean("dpop_bound_access_tokens").default(false),
referenceId: text("reference_id"),
metadata: jsonb("metadata"),
},
(table) => [index("oauthClient_userId_idx").on(table.userId)],
);
export const oauthResource = pgTable("oauth_resource", {
id: text("id").primaryKey(),
identifier: text("identifier").notNull().unique(),
name: text("name").notNull(),
accessTokenTtl: integer("access_token_ttl"),
refreshTokenTtl: integer("refresh_token_ttl"),
signingAlgorithm: text("signing_algorithm"),
signingKeyId: text("signing_key_id"),
allowedScopes: text("allowed_scopes").array(),
customClaims: jsonb("custom_claims"),
dpopBoundAccessTokensRequired: boolean(
"dpop_bound_access_tokens_required",
).default(false),
disabled: boolean("disabled").default(false),
createdAt: timestamp("created_at"),
updatedAt: timestamp("updated_at"),
policyVersion: integer("policy_version").default(1),
metadata: jsonb("metadata"),
});
export const oauthClientResource = pgTable(
"oauth_client_resource",
{
id: text("id").primaryKey(),
clientId: text("client_id")
.notNull()
.references(() => oauthClient.clientId, { onDelete: "cascade" }),
resourceId: text("resource_id")
.notNull()
.references(() => oauthResource.identifier, { onDelete: "cascade" }),
metadata: jsonb("metadata"),
createdAt: timestamp("created_at"),
},
(table) => [
uniqueIndex("oauthClientResource_clientId_resourceId_uidx").on(
table.clientId,
table.resourceId,
),
index("oauthClientResource_clientId_idx").on(table.clientId),
index("oauthClientResource_resourceId_idx").on(table.resourceId),
],
);
export const oauthRefreshToken = pgTable(
"oauth_refresh_token",
{
id: text("id").primaryKey(),
token: text("token").notNull().unique(),
clientId: text("client_id")
.notNull()
.references(() => oauthClient.clientId, { onDelete: "cascade" }),
sessionId: text("session_id").references(() => session.id, {
onDelete: "set null",
}),
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
referenceId: text("reference_id"),
authorizationCodeId: text("authorization_code_id"),
resources: text("resources").array(),
requestedUserInfoClaims: text("requested_user_info_claims").array(),
expiresAt: timestamp("expires_at").notNull(),
createdAt: timestamp("created_at").notNull(),
revoked: timestamp("revoked"),
rotatedAt: timestamp("rotated_at"),
rotationReplayResponse: text("rotation_replay_response"),
rotationReplayExpiresAt: timestamp("rotation_replay_expires_at"),
authTime: timestamp("auth_time"),
confirmation: jsonb("confirmation"),
scopes: text("scopes").array().notNull(),
},
(table) => [
index("oauthRefreshToken_clientId_idx").on(table.clientId),
index("oauthRefreshToken_sessionId_idx").on(table.sessionId),
index("oauthRefreshToken_userId_idx").on(table.userId),
index("oauthRefreshToken_authorizationCodeId_idx").on(
table.authorizationCodeId,
),
],
);
export const oauthAccessToken = pgTable(
"oauth_access_token",
{
id: text("id").primaryKey(),
token: text("token").notNull().unique(),
clientId: text("client_id")
.notNull()
.references(() => oauthClient.clientId, { onDelete: "cascade" }),
sessionId: text("session_id").references(() => session.id, {
onDelete: "set null",
}),
userId: text("user_id").references(() => user.id, { onDelete: "cascade" }),
referenceId: text("reference_id"),
authorizationCodeId: text("authorization_code_id"),
resources: text("resources").array(),
requestedUserInfoClaims: text("requested_user_info_claims").array(),
refreshId: text("refresh_id").references(() => oauthRefreshToken.id, {
onDelete: "cascade",
}),
expiresAt: timestamp("expires_at").notNull(),
createdAt: timestamp("created_at").notNull(),
revoked: timestamp("revoked"),
confirmation: jsonb("confirmation"),
scopes: text("scopes").array().notNull(),
},
(table) => [
index("oauthAccessToken_clientId_idx").on(table.clientId),
index("oauthAccessToken_sessionId_idx").on(table.sessionId),
index("oauthAccessToken_userId_idx").on(table.userId),
index("oauthAccessToken_authorizationCodeId_idx").on(
table.authorizationCodeId,
),
index("oauthAccessToken_refreshId_idx").on(table.refreshId),
],
);
export const oauthConsent = pgTable(
"oauth_consent",
{
id: text("id").primaryKey(),
clientId: text("client_id")
.notNull()
.references(() => oauthClient.clientId, { onDelete: "cascade" }),
userId: text("user_id").references(() => user.id, { onDelete: "cascade" }),
referenceId: text("reference_id"),
resources: text("resources").array(),
requestedUserInfoClaims: text("requested_user_info_claims").array(),
scopes: text("scopes").array().notNull(),
createdAt: timestamp("created_at").notNull(),
updatedAt: timestamp("updated_at").notNull(),
},
(table) => [
index("oauthConsent_clientId_idx").on(table.clientId),
index("oauthConsent_userId_idx").on(table.userId),
],
);
export const oauthClientAssertion = pgTable("oauth_client_assertion", {
id: text("id").primaryKey(),
expiresAt: timestamp("expires_at").notNull(),
});
export const userRelations = relations(user, ({ many }) => ({
sessions: many(session),
accounts: many(account),
passkeys: many(passkey),
oauthClients: many(oauthClient),
oauthRefreshTokens: many(oauthRefreshToken),
oauthAccessTokens: many(oauthAccessToken),
oauthConsents: many(oauthConsent),
}));
export const sessionRelations = relations(session, ({ one, many }) => ({
user: one(user, {
fields: [session.userId],
references: [user.id],
}),
oauthRefreshTokens: many(oauthRefreshToken),
oauthAccessTokens: many(oauthAccessToken),
}));
export const accountRelations = relations(account, ({ one }) => ({
user: one(user, {
fields: [account.userId],
references: [user.id],
}),
}));
export const passkeyRelations = relations(passkey, ({ one }) => ({
user: one(user, {
fields: [passkey.userId],
references: [user.id],
}),
}));
export const oauthClientRelations = relations(oauthClient, ({ one, many }) => ({
user: one(user, {
fields: [oauthClient.userId],
references: [user.id],
}),
oauthClientResources: many(oauthClientResource),
oauthRefreshTokens: many(oauthRefreshToken),
oauthAccessTokens: many(oauthAccessToken),
oauthConsents: many(oauthConsent),
}));
export const oauthResourceRelations = relations(oauthResource, ({ many }) => ({
oauthClientResources: many(oauthClientResource),
}));
export const oauthClientResourceRelations = relations(
oauthClientResource,
({ one }) => ({
oauthClient: one(oauthClient, {
fields: [oauthClientResource.clientId],
references: [oauthClient.clientId],
}),
oauthResource: one(oauthResource, {
fields: [oauthClientResource.resourceId],
references: [oauthResource.identifier],
}),
}),
);
export const oauthRefreshTokenRelations = relations(
oauthRefreshToken,
({ one, many }) => ({
oauthClient: one(oauthClient, {
fields: [oauthRefreshToken.clientId],
references: [oauthClient.clientId],
}),
session: one(session, {
fields: [oauthRefreshToken.sessionId],
references: [session.id],
}),
user: one(user, {
fields: [oauthRefreshToken.userId],
references: [user.id],
}),
oauthAccessTokens: many(oauthAccessToken),
}),
);
export const oauthAccessTokenRelations = relations(
oauthAccessToken,
({ one }) => ({
oauthClient: one(oauthClient, {
fields: [oauthAccessToken.clientId],
references: [oauthClient.clientId],
}),
session: one(session, {
fields: [oauthAccessToken.sessionId],
references: [session.id],
}),
user: one(user, {
fields: [oauthAccessToken.userId],
references: [user.id],
}),
oauthRefreshToken: one(oauthRefreshToken, {
fields: [oauthAccessToken.refreshId],
references: [oauthRefreshToken.id],
}),
}),
);
export const oauthConsentRelations = relations(oauthConsent, ({ one }) => ({
oauthClient: one(oauthClient, {
fields: [oauthConsent.clientId],
references: [oauthClient.clientId],
}),
user: one(user, {
fields: [oauthConsent.userId],
references: [user.id],
}),
}));