Merge pull request #3628 from aithal007/perf/add-fk-indexes

perf: add secondary FK indexes to core OSS schemas
2026-09-01 00:41:55 +02:00 · 2026-08-31 17:05:04 -04:00 · 2026-08-31 17:04:01 -04:00 · 2026-08-31 17:02:53 -04:00 · 2026-08-31 17:02:03 -04:00 · 2026-08-31 16:57:36 -04:00
6 changed files with 434 additions and 347 deletions
@@ -262,16 +262,20 @@ export const resourceAiModels = pgTable(
    (t) => [primaryKey({ columns: [t.resourceId, t.modelId] })]
 );

-export const labels = pgTable("labels", {
-    labelId: serial("labelId").primaryKey(),
-    name: varchar("name").notNull(),
-    color: varchar("color").notNull(),
-    orgId: varchar("orgId")
-        .references(() => orgs.orgId, {
-            onDelete: "cascade"
-        })
-        .notNull()
-});
+export const labels = pgTable(
+    "labels",
+    {
+        labelId: serial("labelId").primaryKey(),
+        name: varchar("name").notNull(),
+        color: varchar("color").notNull(),
+        orgId: varchar("orgId")
+            .references(() => orgs.orgId, {
+                onDelete: "cascade"
+            })
+            .notNull()
+    },
+    (t) => [index("idx_labels_orgid").on(t.orgId)]
+);

 export const launcherViews = pgTable("launcherViews", {
    viewId: serial("viewId").primaryKey(),
@@ -693,15 +697,19 @@ export const twoFactorBackupCodes = pgTable("twoFactorBackupCodes", {
    codeHash: varchar("codeHash").notNull()
 });

-export const sessions = pgTable("session", {
-    sessionId: varchar("id").primaryKey(),
-    userId: varchar("userId")
-        .notNull()
-        .references(() => users.userId, { onDelete: "cascade" }),
-    expiresAt: bigint("expiresAt", { mode: "number" }).notNull(),
-    issuedAt: bigint("issuedAt", { mode: "number" }),
-    deviceAuthUsed: boolean("deviceAuthUsed").notNull().default(false)
-});
+export const sessions = pgTable(
+    "session",
+    {
+        sessionId: varchar("id").primaryKey(),
+        userId: varchar("userId")
+            .notNull()
+            .references(() => users.userId, { onDelete: "cascade" }),
+        expiresAt: bigint("expiresAt", { mode: "number" }).notNull(),
+        issuedAt: bigint("issuedAt", { mode: "number" }),
+        deviceAuthUsed: boolean("deviceAuthUsed").notNull().default(false)
+    },
+    (t) => [index("idx_sessions_userid").on(t.userId)]
+);

 export const newtSessions = pgTable("newtSession", {
    sessionId: varchar("id").primaryKey(),
@@ -711,19 +719,26 @@ export const newtSessions = pgTable("newtSession", {
    expiresAt: bigint("expiresAt", { mode: "number" }).notNull()
 });

-export const userOrgs = pgTable("userOrgs", {
-    userId: varchar("userId")
-        .notNull()
-        .references(() => users.userId, { onDelete: "cascade" }),
-    orgId: varchar("orgId")
-        .references(() => orgs.orgId, {
-            onDelete: "cascade"
-        })
-        .notNull(),
-    isOwner: boolean("isOwner").notNull().default(false),
-    autoProvisioned: boolean("autoProvisioned").default(false),
-    pamUsername: varchar("pamUsername") // cleaned username for ssh and such
-});
+export const userOrgs = pgTable(
+    "userOrgs",
+    {
+        userId: varchar("userId")
+            .notNull()
+            .references(() => users.userId, { onDelete: "cascade" }),
+        orgId: varchar("orgId")
+            .references(() => orgs.orgId, {
+                onDelete: "cascade"
+            })
+            .notNull(),
+        isOwner: boolean("isOwner").notNull().default(false),
+        autoProvisioned: boolean("autoProvisioned").default(false),
+        pamUsername: varchar("pamUsername") // cleaned username for ssh and such
+    },
+    (t) => [
+        index("idx_userOrgs_userid").on(t.userId),
+        index("idx_userOrgs_orgid").on(t.orgId)
+    ]
+);

 export const emailVerificationCodes = pgTable("emailVerificationCodes", {
    codeId: serial("id").primaryKey(),
@@ -751,22 +766,26 @@ export const actions = pgTable("actions", {
    description: varchar("description")
 });

-export const roles = pgTable("roles", {
-    roleId: serial("roleId").primaryKey(),
-    orgId: varchar("orgId")
-        .references(() => orgs.orgId, {
-            onDelete: "cascade"
-        })
-        .notNull(),
-    isAdmin: boolean("isAdmin"),
-    name: varchar("name").notNull(),
-    description: varchar("description"),
-    requireDeviceApproval: boolean("requireDeviceApproval").default(false),
-    sshSudoMode: varchar("sshSudoMode", { length: 32 }).default("full"), // "none" | "full" | "commands"
-    sshSudoCommands: text("sshSudoCommands").default("[]"),
-    sshCreateHomeDir: boolean("sshCreateHomeDir").default(true),
-    sshUnixGroups: text("sshUnixGroups").default("[]")
-});
+export const roles = pgTable(
+    "roles",
+    {
+        roleId: serial("roleId").primaryKey(),
+        orgId: varchar("orgId")
+            .references(() => orgs.orgId, {
+                onDelete: "cascade"
+            })
+            .notNull(),
+        isAdmin: boolean("isAdmin"),
+        name: varchar("name").notNull(),
+        description: varchar("description"),
+        requireDeviceApproval: boolean("requireDeviceApproval").default(false),
+        sshSudoMode: varchar("sshSudoMode", { length: 32 }).default("full"), // "none" | "full" | "commands"
+        sshSudoCommands: text("sshSudoCommands").default("[]"),
+        sshCreateHomeDir: boolean("sshCreateHomeDir").default(true),
+        sshUnixGroups: text("sshUnixGroups").default("[]")
+    },
+    (t) => [index("idx_roles_orgid").on(t.orgId)]
+);

 export const userOrgRoles = pgTable(
    "userOrgRoles",
@@ -1409,7 +1428,10 @@ export const olms = pgTable(
        }),
        archived: boolean("archived").notNull().default(false)
    },
-    (t) => [index("idx_olms_clientid").on(t.clientId)]
+    (t) => [
+        index("idx_olms_clientid").on(t.clientId),
+        index("idx_olms_userid").on(t.userId)
+    ]
 );

 export const currentFingerprint = pgTable("currentFingerprint", {
@@ -99,131 +99,147 @@ export const orgDomains = sqliteTable("orgDomains", {
        .references(() => domains.domainId, { onDelete: "cascade" })
 });

-export const sites = sqliteTable("sites", {
-    siteId: integer("siteId").primaryKey({ autoIncrement: true }),
-    orgId: text("orgId")
-        .references(() => orgs.orgId, {
-            onDelete: "cascade"
-        })
-        .notNull(),
-    niceId: text("niceId").notNull(),
-    exitNodeId: integer("exitNode").references(() => exitNodes.exitNodeId, {
-        onDelete: "set null"
-    }),
-    networkId: integer("networkId").references(() => networks.networkId, {
-        onDelete: "set null"
-    }),
-    name: text("name").notNull(),
-    pubKey: text("pubKey"),
-    exitNodeSubnet: text("exitNodeSubnet"),
-    megabytesIn: integer("bytesIn").default(0),
-    megabytesOut: integer("bytesOut").default(0),
-    lastBandwidthUpdate: text("lastBandwidthUpdate"),
-    type: text("type").notNull(), // "newt" or "wireguard"
-    online: integer("online", { mode: "boolean" }).notNull().default(false),
-    lastPing: integer("lastPing"),
+export const sites = sqliteTable(
+    "sites",
+    {
+        siteId: integer("siteId").primaryKey({ autoIncrement: true }),
+        orgId: text("orgId")
+            .references(() => orgs.orgId, {
+                onDelete: "cascade"
+            })
+            .notNull(),
+        niceId: text("niceId").notNull(),
+        exitNodeId: integer("exitNode").references(() => exitNodes.exitNodeId, {
+            onDelete: "set null"
+        }),
+        networkId: integer("networkId").references(() => networks.networkId, {
+            onDelete: "set null"
+        }),
+        name: text("name").notNull(),
+        pubKey: text("pubKey"),
+        exitNodeSubnet: text("exitNodeSubnet"),
+        megabytesIn: integer("bytesIn").default(0),
+        megabytesOut: integer("bytesOut").default(0),
+        lastBandwidthUpdate: text("lastBandwidthUpdate"),
+        type: text("type").notNull(), // "newt" or "wireguard"
+        online: integer("online", { mode: "boolean" }).notNull().default(false),
+        lastPing: integer("lastPing"),

-    // exit node stuff that is how to connect to the site when it has a wg server
-    address: text("address"), // this is the address of the wireguard interface in newt
-    endpoint: text("endpoint"), // this is how to reach gerbil externally - gets put into the wireguard config
-    localEndpoints: text("localEndpoints"), // JSON encoded list of string ips on the local machine to try to connect to
-    publicKey: text("publicKey"), // TODO: Fix typo in publicKey
-    lastHolePunch: integer("lastHolePunch"),
-    listenPort: integer("listenPort"),
-    dockerSocketEnabled: integer("dockerSocketEnabled", { mode: "boolean" })
-        .notNull()
-        .default(true),
-    autoUpdateEnabled: integer("autoUpdateEnabled", { mode: "boolean" })
-        .notNull()
-        .default(false),
-    autoUpdateOverrideOrg: integer("autoUpdateOverrideOrg", {
-        mode: "boolean"
-    })
-        .notNull()
-        .default(false),
-    status: text("status").$type<"pending" | "approved">().default("approved")
-});
-
-export const resources = sqliteTable("resources", {
-    resourceId: integer("resourceId").primaryKey({ autoIncrement: true }),
-    resourcePolicyId: integer("resourcePolicyId").references(
-        () => resourcePolicies.resourcePolicyId,
-        { onDelete: "set null" }
-    ),
-    defaultResourcePolicyId: integer("defaultResourcePolicyId").references(
-        () => resourcePolicies.resourcePolicyId,
-        {
-            onDelete: "restrict"
-        }
-    ),
-    resourceGuid: text("resourceGuid", { length: 36 })
-        .unique()
-        .notNull()
-        .$defaultFn(() => randomUUID()),
-    orgId: text("orgId")
-        .references(() => orgs.orgId, {
-            onDelete: "cascade"
+        // exit node stuff that is how to connect to the site when it has a wg server
+        address: text("address"), // this is the address of the wireguard interface in newt
+        endpoint: text("endpoint"), // this is how to reach gerbil externally - gets put into the wireguard config
+        localEndpoints: text("localEndpoints"), // JSON encoded list of string ips on the local machine to try to connect to
+        publicKey: text("publicKey"), // TODO: Fix typo in publicKey
+        lastHolePunch: integer("lastHolePunch"),
+        listenPort: integer("listenPort"),
+        dockerSocketEnabled: integer("dockerSocketEnabled", { mode: "boolean" })
+            .notNull()
+            .default(true),
+        autoUpdateEnabled: integer("autoUpdateEnabled", { mode: "boolean" })
+            .notNull()
+            .default(false),
+        autoUpdateOverrideOrg: integer("autoUpdateOverrideOrg", {
+            mode: "boolean"
        })
-        .notNull(),
-    niceId: text("niceId").notNull(),
-    name: text("name").notNull(),
-    subdomain: text("subdomain"),
-    fullDomain: text("fullDomain"),
-    domainId: text("domainId").references(() => domains.domainId, {
-        onDelete: "set null"
-    }),
-    ssl: integer("ssl", { mode: "boolean" }).notNull().default(false),
-    blockAccess: integer("blockAccess", { mode: "boolean" })
-        .notNull()
-        .default(false),
-    proxyPort: integer("proxyPort"),
-    sso: integer("sso", { mode: "boolean" }),
-    emailWhitelistEnabled: integer("emailWhitelistEnabled", {
-        mode: "boolean"
-    }),
-    applyRules: integer("applyRules", { mode: "boolean" }),
-    enabled: integer("enabled", { mode: "boolean" }).notNull().default(true),
-    stickySession: integer("stickySession", { mode: "boolean" })
-        .notNull()
-        .default(false),
-    tlsServerName: text("tlsServerName"),
-    setHostHeader: text("setHostHeader"),
-    enableProxy: integer("enableProxy", { mode: "boolean" }).default(true),
-    skipToIdpId: integer("skipToIdpId").references(() => idp.idpId, {
-        onDelete: "set null"
-    }),
-    headers: text("headers"), // comma-separated list of headers to add to the request
-    proxyProtocol: integer("proxyProtocol", { mode: "boolean" })
-        .notNull()
-        .default(false),
-    proxyProtocolVersion: integer("proxyProtocolVersion").default(1),
-    maintenanceModeEnabled: integer("maintenanceModeEnabled", {
-        mode: "boolean"
-    })
-        .notNull()
-        .default(false),
-    maintenanceModeType: text("maintenanceModeType", {
-        enum: ["forced", "automatic"]
-    }).default("forced"), // "forced" = always show, "automatic" = only when down
-    maintenanceTitle: text("maintenanceTitle"),
-    maintenanceMessage: text("maintenanceMessage"),
-    maintenanceEstimatedTime: text("maintenanceEstimatedTime"),
-    postAuthPath: text("postAuthPath"),
-    health: text("health").default("unknown"), // "healthy", "unhealthy", "unknown"
-    wildcard: integer("wildcard", { mode: "boolean" }).notNull().default(false),
-    mode: text("mode")
-        .default("http")
-        .$type<"rdp" | "ssh" | "http" | "vnc" | "inference" | "tcp" | "udp">()
-        .notNull(), // rdp, ssh, http, vnc, inference
-    pamMode: text("pamMode")
-        .$type<"passthrough" | "push">()
-        .default("passthrough"),
-    authDaemonMode: text("authDaemonMode")
-        .$type<"site" | "remote" | "native">()
-        .default("site"),
-    authDaemonPort: integer("authDaemonPort").default(22123),
-    status: text("status").$type<"pending" | "approved">().default("approved")
-});
+            .notNull()
+            .default(false),
+        status: text("status")
+            .$type<"pending" | "approved">()
+            .default("approved")
+    },
+    (table) => [
+        index("idx_sites_orgId").on(table.orgId)
+    ]
+);
+
+export const resources = sqliteTable(
+    "resources",
+    {
+        resourceId: integer("resourceId").primaryKey({ autoIncrement: true }),
+        resourcePolicyId: integer("resourcePolicyId").references(
+            () => resourcePolicies.resourcePolicyId,
+            { onDelete: "set null" }
+        ),
+        defaultResourcePolicyId: integer("defaultResourcePolicyId").references(
+            () => resourcePolicies.resourcePolicyId,
+            {
+                onDelete: "restrict"
+            }
+        ),
+        resourceGuid: text("resourceGuid", { length: 36 })
+            .unique()
+            .notNull()
+            .$defaultFn(() => randomUUID()),
+        orgId: text("orgId")
+            .references(() => orgs.orgId, {
+                onDelete: "cascade"
+            })
+            .notNull(),
+        niceId: text("niceId").notNull(),
+        name: text("name").notNull(),
+        subdomain: text("subdomain"),
+        fullDomain: text("fullDomain"),
+        domainId: text("domainId").references(() => domains.domainId, {
+            onDelete: "set null"
+        }),
+        ssl: integer("ssl", { mode: "boolean" }).notNull().default(false),
+        blockAccess: integer("blockAccess", { mode: "boolean" })
+            .notNull()
+            .default(false),
+        proxyPort: integer("proxyPort"),
+        sso: integer("sso", { mode: "boolean" }),
+        emailWhitelistEnabled: integer("emailWhitelistEnabled", {
+            mode: "boolean"
+        }),
+        applyRules: integer("applyRules", { mode: "boolean" }),
+        enabled: integer("enabled", { mode: "boolean" }).notNull().default(true),
+        stickySession: integer("stickySession", { mode: "boolean" })
+            .notNull()
+            .default(false),
+        tlsServerName: text("tlsServerName"),
+        setHostHeader: text("setHostHeader"),
+        enableProxy: integer("enableProxy", { mode: "boolean" }).default(true),
+        skipToIdpId: integer("skipToIdpId").references(() => idp.idpId, {
+            onDelete: "set null"
+        }),
+        headers: text("headers"), // comma-separated list of headers to add to the request
+        proxyProtocol: integer("proxyProtocol", { mode: "boolean" })
+            .notNull()
+            .default(false),
+        proxyProtocolVersion: integer("proxyProtocolVersion").default(1),
+        maintenanceModeEnabled: integer("maintenanceModeEnabled", {
+            mode: "boolean"
+        })
+            .notNull()
+            .default(false),
+        maintenanceModeType: text("maintenanceModeType", {
+            enum: ["forced", "automatic"]
+        }).default("forced"), // "forced" = always show, "automatic" = only when down
+        maintenanceTitle: text("maintenanceTitle"),
+        maintenanceMessage: text("maintenanceMessage"),
+        maintenanceEstimatedTime: text("maintenanceEstimatedTime"),
+        postAuthPath: text("postAuthPath"),
+        health: text("health").default("unknown"), // "healthy", "unhealthy", "unknown"
+        wildcard: integer("wildcard", { mode: "boolean" }).notNull().default(false),
+        mode: text("mode")
+            .default("http")
+            .$type<"rdp" | "ssh" | "http" | "vnc" | "inference" | "tcp" | "udp">()
+            .notNull(), // rdp, ssh, http, vnc, inference
+        pamMode: text("pamMode")
+            .$type<"passthrough" | "push">()
+            .default("passthrough"),
+        authDaemonMode: text("authDaemonMode")
+            .$type<"site" | "remote" | "native">()
+            .default("site"),
+        authDaemonPort: integer("authDaemonPort").default(22123),
+        status: text("status")
+            .$type<"pending" | "approved">()
+            .default("approved")
+    },
+    (table) => [
+        index("idx_resources_orgId").on(table.orgId)
+    ]
+);

 export const resourceAiProviders = sqliteTable(
    "resourceAiProviders",
@@ -260,16 +276,22 @@ export const resourceAiModels = sqliteTable(
    (t) => [primaryKey({ columns: [t.resourceId, t.modelId] })]
 );

-export const labels = sqliteTable("labels", {
-    labelId: integer("labelId").primaryKey({ autoIncrement: true }),
-    name: text("name").notNull(),
-    color: text("color").notNull(),
-    orgId: text("orgId")
-        .references(() => orgs.orgId, {
-            onDelete: "cascade"
-        })
-        .notNull()
-});
+export const labels = sqliteTable(
+    "labels",
+    {
+        labelId: integer("labelId").primaryKey({ autoIncrement: true }),
+        name: text("name").notNull(),
+        color: text("color").notNull(),
+        orgId: text("orgId")
+            .references(() => orgs.orgId, {
+                onDelete: "cascade"
+            })
+            .notNull()
+    },
+    (table) => [
+        index("idx_labels_orgId").on(table.orgId)
+    ]
+);

 export const launcherViews = sqliteTable("launcherViews", {
    viewId: integer("viewId").primaryKey({ autoIncrement: true }),
@@ -366,35 +388,44 @@ export const clientLabels = sqliteTable(
    (t) => [unique("client_label_uniq").on(t.clientId, t.labelId)]
 );

-export const targets = sqliteTable("targets", {
-    targetId: integer("targetId").primaryKey({ autoIncrement: true }),
-    resourceId: integer("resourceId").references(() => resources.resourceId, {
-        onDelete: "cascade"
-    }),
-    providerId: integer("providerId").references(() => aiProviders.providerId, {
-        onDelete: "cascade"
-    }),
-    siteId: integer("siteId")
-        .references(() => sites.siteId, {
-            onDelete: "cascade"
-        })
-        .notNull(),
-    ip: text("ip").notNull(),
-    method: text("method"),
-    port: integer("port").notNull(),
-    internalPort: integer("internalPort"),
-    enabled: integer("enabled", { mode: "boolean" }).notNull().default(true),
-    path: text("path"),
-    pathMatchType: text("pathMatchType"), // exact, prefix, regex
-    rewritePath: text("rewritePath"), // if set, rewrites the path to this value before sending to the target
-    rewritePathType: text("rewritePathType"), // exact, prefix, regex, stripPrefix
-    priority: integer("priority").notNull().default(100),
-    mode: text("mode")
-        .$type<"http" | "tcp" | "udp" | "ssh" | "rdp" | "vnc">()
-        .notNull()
-        .default("http"),
-    authToken: text("authToken")
-});
+export const targets = sqliteTable(
+    "targets",
+    {
+        targetId: integer("targetId").primaryKey({ autoIncrement: true }),
+        resourceId: integer("resourceId").references(
+            () => resources.resourceId,
+            { onDelete: "cascade" }
+        ),
+        providerId: integer("providerId").references(
+            () => aiProviders.providerId,
+            { onDelete: "cascade" }
+        ),
+        siteId: integer("siteId")
+            .references(() => sites.siteId, {
+                onDelete: "cascade"
+            })
+            .notNull(),
+        ip: text("ip").notNull(),
+        method: text("method"),
+        port: integer("port").notNull(),
+        internalPort: integer("internalPort"),
+        enabled: integer("enabled", { mode: "boolean" }).notNull().default(true),
+        path: text("path"),
+        pathMatchType: text("pathMatchType"), // exact, prefix, regex
+        rewritePath: text("rewritePath"), // if set, rewrites the path to this value before sending to the target
+        rewritePathType: text("rewritePathType"), // exact, prefix, regex, stripPrefix
+        priority: integer("priority").notNull().default(100),
+        mode: text("mode")
+            .$type<"http" | "tcp" | "udp" | "ssh" | "rdp" | "vnc">()
+            .notNull()
+            .default("http"),
+        authToken: text("authToken")
+    },
+    (table) => [
+        index("idx_targets_resourceId").on(table.resourceId),
+        index("idx_targets_siteId").on(table.siteId)
+    ]
+);

 export const targetHealthCheck = sqliteTable("targetHealthCheck", {
    targetHealthCheckId: integer("targetHealthCheckId").primaryKey({
@@ -663,50 +694,63 @@ export const setupTokens = sqliteTable("setupTokens", {
    dateUsed: text("dateUsed")
 });

-export const newts = sqliteTable("newt", {
-    newtId: text("id").primaryKey(),
-    secretHash: text("secretHash").notNull(),
-    dateCreated: text("dateCreated").notNull(),
-    version: text("version"),
-    siteId: integer("siteId").references(() => sites.siteId, {
-        onDelete: "cascade"
-    })
-});
-
-export const clients = sqliteTable("clients", {
-    clientId: integer("clientId").primaryKey({ autoIncrement: true }),
-    orgId: text("orgId")
-        .references(() => orgs.orgId, {
+export const newts = sqliteTable(
+    "newt",
+    {
+        newtId: text("id").primaryKey(),
+        secretHash: text("secretHash").notNull(),
+        dateCreated: text("dateCreated").notNull(),
+        version: text("version"),
+        siteId: integer("siteId").references(() => sites.siteId, {
            onDelete: "cascade"
        })
-        .notNull(),
-    exitNodeId: integer("exitNode").references(() => exitNodes.exitNodeId, {
-        onDelete: "set null"
-    }),
-    userId: text("userId").references(() => users.userId, {
-        // optionally tied to a user and in this case delete when the user deletes
-        onDelete: "cascade"
-    }),
-    niceId: text("niceId").notNull(),
-    name: text("name").notNull(),
-    pubKey: text("pubKey"),
-    olmId: text("olmId"), // to lock it to a specific olm optionally
-    subnet: text("subnet").notNull(),
-    exitNodeSubnet: text("exitNodeSubnet"), // this is the subnet when connecting to an exit node
-    megabytesIn: integer("bytesIn"),
-    megabytesOut: integer("bytesOut"),
-    lastBandwidthUpdate: text("lastBandwidthUpdate"),
-    lastPing: integer("lastPing"),
-    type: text("type").notNull(), // "olm"
-    online: integer("online", { mode: "boolean" }).notNull().default(false),
-    // endpoint: text("endpoint"),
-    lastHolePunch: integer("lastHolePunch"),
-    archived: integer("archived", { mode: "boolean" }).notNull().default(false),
-    blocked: integer("blocked", { mode: "boolean" }).notNull().default(false),
-    approvalState: text("approvalState").$type<
-        "pending" | "approved" | "denied"
-    >()
-});
+    },
+    (table) => [
+        index("idx_newts_siteId").on(table.siteId)
+    ]
+);
+
+export const clients = sqliteTable(
+    "clients",
+    {
+        clientId: integer("clientId").primaryKey({ autoIncrement: true }),
+        orgId: text("orgId")
+            .references(() => orgs.orgId, {
+                onDelete: "cascade"
+            })
+            .notNull(),
+        exitNodeId: integer("exitNode").references(() => exitNodes.exitNodeId, {
+            onDelete: "set null"
+        }),
+        userId: text("userId").references(() => users.userId, {
+            // optionally tied to a user and in this case delete when the user deletes
+            onDelete: "cascade"
+        }),
+        niceId: text("niceId").notNull(),
+        name: text("name").notNull(),
+        pubKey: text("pubKey"),
+        olmId: text("olmId"), // to lock it to a specific olm optionally
+        subnet: text("subnet").notNull(),
+        exitNodeSubnet: text("exitNodeSubnet"), // this is the subnet when connecting to an exit node
+        megabytesIn: integer("bytesIn"),
+        megabytesOut: integer("bytesOut"),
+        lastBandwidthUpdate: text("lastBandwidthUpdate"),
+        lastPing: integer("lastPing"),
+        type: text("type").notNull(), // "olm"
+        online: integer("online", { mode: "boolean" }).notNull().default(false),
+        // endpoint: text("endpoint"),
+        lastHolePunch: integer("lastHolePunch"),
+        archived: integer("archived", { mode: "boolean" }).notNull().default(false),
+        blocked: integer("blocked", { mode: "boolean" }).notNull().default(false),
+        approvalState: text("approvalState").$type<
+            "pending" | "approved" | "denied"
+        >()
+    },
+    (table) => [
+        index("idx_clients_orgId").on(table.orgId),
+        index("idx_clients_userId").on(table.userId)
+    ]
+);

 export const clientSitesAssociationsCache = sqliteTable(
    "clientSitesAssociationsCache",
@@ -734,23 +778,29 @@ export const clientSiteResourcesAssociationsCache = sqliteTable(
    }
 );

-export const olms = sqliteTable("olms", {
-    olmId: text("id").primaryKey(),
-    secretHash: text("secretHash").notNull(),
-    dateCreated: text("dateCreated").notNull(),
-    version: text("version"),
-    agent: text("agent"),
-    name: text("name"),
-    clientId: integer("clientId").references(() => clients.clientId, {
-        // we will switch this depending on the current org it wants to connect to
-        onDelete: "set null"
-    }),
-    userId: text("userId").references(() => users.userId, {
-        // optionally tied to a user and in this case delete when the user deletes
-        onDelete: "cascade"
-    }),
-    archived: integer("archived", { mode: "boolean" }).notNull().default(false)
-});
+export const olms = sqliteTable(
+    "olms",
+    {
+        olmId: text("id").primaryKey(),
+        secretHash: text("secretHash").notNull(),
+        dateCreated: text("dateCreated").notNull(),
+        version: text("version"),
+        agent: text("agent"),
+        name: text("name"),
+        clientId: integer("clientId").references(() => clients.clientId, {
+            // we will switch this depending on the current org it wants to connect to
+            onDelete: "set null"
+        }),
+        userId: text("userId").references(() => users.userId, {
+            // optionally tied to a user and in this case delete when the user deletes
+            onDelete: "cascade"
+        }),
+        archived: integer("archived", { mode: "boolean" }).notNull().default(false)
+    },
+    (table) => [
+        index("idx_olms_userId").on(table.userId)
+    ]
+);

 export const currentFingerprint = sqliteTable("currentFingerprint", {
    fingerprintId: integer("id").primaryKey({ autoIncrement: true }),
@@ -912,17 +962,23 @@ export const twoFactorBackupCodes = sqliteTable("twoFactorBackupCodes", {
    codeHash: text("codeHash").notNull()
 });

-export const sessions = sqliteTable("session", {
-    sessionId: text("id").primaryKey(),
-    userId: text("userId")
-        .notNull()
-        .references(() => users.userId, { onDelete: "cascade" }),
-    expiresAt: integer("expiresAt").notNull(),
-    issuedAt: integer("issuedAt"),
-    deviceAuthUsed: integer("deviceAuthUsed", { mode: "boolean" })
-        .notNull()
-        .default(false)
-});
+export const sessions = sqliteTable(
+    "session",
+    {
+        sessionId: text("id").primaryKey(),
+        userId: text("userId")
+            .notNull()
+            .references(() => users.userId, { onDelete: "cascade" }),
+        expiresAt: integer("expiresAt").notNull(),
+        issuedAt: integer("issuedAt"),
+        deviceAuthUsed: integer("deviceAuthUsed", { mode: "boolean" })
+            .notNull()
+            .default(false)
+    },
+    (table) => [
+        index("idx_sessions_userId").on(table.userId)
+    ]
+);

 export const newtSessions = sqliteTable("newtSession", {
    sessionId: text("id").primaryKey(),
@@ -940,21 +996,28 @@ export const olmSessions = sqliteTable("clientSession", {
    expiresAt: integer("expiresAt").notNull()
 });

-export const userOrgs = sqliteTable("userOrgs", {
-    userId: text("userId")
-        .notNull()
-        .references(() => users.userId, { onDelete: "cascade" }),
-    orgId: text("orgId")
-        .references(() => orgs.orgId, {
-            onDelete: "cascade"
-        })
-        .notNull(),
-    isOwner: integer("isOwner", { mode: "boolean" }).notNull().default(false),
-    autoProvisioned: integer("autoProvisioned", {
-        mode: "boolean"
-    }).default(false),
-    pamUsername: text("pamUsername") // cleaned username for ssh and such
-});
+export const userOrgs = sqliteTable(
+    "userOrgs",
+    {
+        userId: text("userId")
+            .notNull()
+            .references(() => users.userId, { onDelete: "cascade" }),
+        orgId: text("orgId")
+            .references(() => orgs.orgId, {
+                onDelete: "cascade"
+            })
+            .notNull(),
+        isOwner: integer("isOwner", { mode: "boolean" }).notNull().default(false),
+        autoProvisioned: integer("autoProvisioned", {
+            mode: "boolean"
+        }).default(false),
+        pamUsername: text("pamUsername") // cleaned username for ssh and such
+    },
+    (table) => [
+        index("idx_userOrgs_userId").on(table.userId),
+        index("idx_userOrgs_orgId").on(table.orgId)
+    ]
+);

 export const emailVerificationCodes = sqliteTable("emailVerificationCodes", {
    codeId: integer("id").primaryKey({ autoIncrement: true }),
@@ -982,26 +1045,32 @@ export const actions = sqliteTable("actions", {
    description: text("description")
 });

-export const roles = sqliteTable("roles", {
-    roleId: integer("roleId").primaryKey({ autoIncrement: true }),
-    orgId: text("orgId")
-        .references(() => orgs.orgId, {
-            onDelete: "cascade"
-        })
-        .notNull(),
-    isAdmin: integer("isAdmin", { mode: "boolean" }),
-    name: text("name").notNull(),
-    description: text("description"),
-    requireDeviceApproval: integer("requireDeviceApproval", {
-        mode: "boolean"
-    }).default(false),
-    sshSudoMode: text("sshSudoMode").default("full"), // "none" | "full" | "commands"
-    sshSudoCommands: text("sshSudoCommands").default("[]"),
-    sshCreateHomeDir: integer("sshCreateHomeDir", { mode: "boolean" }).default(
-        true
-    ),
-    sshUnixGroups: text("sshUnixGroups").default("[]")
-});
+export const roles = sqliteTable(
+    "roles",
+    {
+        roleId: integer("roleId").primaryKey({ autoIncrement: true }),
+        orgId: text("orgId")
+            .references(() => orgs.orgId, {
+                onDelete: "cascade"
+            })
+            .notNull(),
+        isAdmin: integer("isAdmin", { mode: "boolean" }),
+        name: text("name").notNull(),
+        description: text("description"),
+        requireDeviceApproval: integer("requireDeviceApproval", {
+            mode: "boolean"
+        }).default(false),
+        sshSudoMode: text("sshSudoMode").default("full"), // "none" | "full" | "commands"
+        sshSudoCommands: text("sshSudoCommands").default("[]"),
+        sshCreateHomeDir: integer("sshCreateHomeDir", { mode: "boolean" }).default(
+            true
+        ),
+        sshUnixGroups: text("sshUnixGroups").default("[]")
+    },
+    (table) => [
+        index("idx_roles_orgId").on(table.orgId)
+    ]
+);

 export const userOrgRoles = sqliteTable(
    "userOrgRoles",
@@ -101,7 +101,7 @@ export const AuthSchema = z.object({
 export const RuleSchema = z
    .object({
        action: z.enum(["allow", "deny", "pass"]),
-        match: z.enum(["cidr", "path", "ip", "country", "asn", "region"]),
+        match: z.enum(["cidr", "path", "ip", "country", "country_is_not", "asn", "region"]),
        value: z.coerce.string(),
        priority: z.int().optional(),
        enabled: z.boolean().optional().default(true)
@@ -136,7 +136,7 @@ export const RuleSchema = z
    )
    .refine(
        (rule) => {
-            if (rule.match === "country") {
+            if (rule.match === "country" || rule.match === "country_is_not") {
                if (!hasMaxmindCountryDb) {
                    return false;
                }
@@ -31,7 +31,6 @@ export async function validateAndConstructDomain(
    subdomain?: string | null
 ): Promise<DomainValidationResult> {
    try {
-        // Query domain with organization access check
        const [domainRes] = await db
            .select()
            .from(domains)
@@ -42,6 +41,10 @@ export async function validateAndConstructDomain(
                    eq(orgDomains.orgId, orgId),
                    eq(orgDomains.domainId, domainId)
                )
+            )
+            .leftJoin(
+                domainNamespaces,
+                eq(domainNamespaces.domainId, domainId)
            );

        // Check if domain exists
@@ -52,7 +55,7 @@ export async function validateAndConstructDomain(
            };
        }

-        if (!domainRes.orgDomains) {
+        if (!domainRes.orgDomains && !domainRes.domainNamespaces) {
            return {
                success: false,
                error: `Organization does not have access to domain with ID ${domainId}`
@@ -83,19 +86,11 @@ export async function validateAndConstructDomain(
        }

        // Wildcard subdomains are not allowed on namespace (provided/free) domains
-        if (isWildcard) {
-            const [namespaceDomain] = await db
-                .select()
-                .from(domainNamespaces)
-                .where(eq(domainNamespaces.domainId, domainId))
-                .limit(1);
-
-            if (namespaceDomain) {
-                return {
-                    success: false,
-                    error: "Wildcard subdomains are not supported for provided or free domains. Use a specific subdomain instead."
-                };
-            }
+        if (isWildcard && domainRes.domainNamespaces) {
+            return {
+                success: false,
+                error: "Wildcard subdomains are not supported for provided or free domains. Use a specific subdomain instead."
+            };
        }

        if (
@@ -68,6 +68,11 @@ export async function verifyApiKeyAccessTokenAccess(
            );
        }

+        if (apiKey.isRoot) {
+            // Root keys can access any access token in any org
+            return next();
+        }
+
        if (!resource.orgId) {
            return next(
                createHttpError(
@@ -149,12 +149,8 @@ LQIDAQAB
        }

        // Count used sites and users for license comparison
-        const [siteCountRes] = await db
-            .select({ value: count() })
-            .from(sites);
-        const [userCountRes] = await db
-            .select({ value: count() })
-            .from(users);
+        const [siteCountRes] = await db.select({ value: count() }).from(sites);
+        const [userCountRes] = await db.select({ value: count() }).from(users);

        const status: LicenseStatus = {
            hostId: this.hostMeta.hostMetaId,
@@ -276,10 +272,13 @@ LQIDAQAB
                        logger.error(
                            `Allowing failure. Will retry one more time at next run interval.`
                        );
-                        // return last known good status
-                        return this.statusCache.get(
+                        // Fall back to last known good status if we have
+                        // one cached; otherwise return the freshly built
+                        // status (with defaults) rather than undefined.
+                        const lastKnownStatus = this.statusCache.get(
                            this.statusKey
-                        ) as LicenseStatus;
+                        ) as LicenseStatus | undefined;
+                        return lastKnownStatus ?? status;
                    } else {
                        // Subsequent failures: fail abruptly
                        throw e;
@@ -368,10 +367,7 @@ LQIDAQAB
                }

                // Only consider quantity if defined and >= 0 (quantity = users, quantity_2 = sites)
-                if (
-                    cached.quantity_2 !== undefined &&
-                    cached.quantity_2 >= 0
-                ) {
+                if (cached.quantity_2 !== undefined && cached.quantity_2 >= 0) {
                    status.maxSites =
                        (status.maxSites ?? 0) + cached.quantity_2;
                }
@@ -561,7 +557,7 @@ LQIDAQAB
                    // Calculate exponential backoff delay
                    const retryDelay = Math.floor(
                        initialRetryDelay *
-                        Math.pow(exponentialFactor, attempt - 1)
+                            Math.pow(exponentialFactor, attempt - 1)
                    );

                    logger.debug(
Author	SHA1	Message	Date
Owen Schwartz	d6d923e972	Merge pull request #3628 from aithal007/perf/add-fk-indexes perf: add secondary FK indexes to core OSS schemas	2026-08-31 17:05:04 -04:00
Parikshith	41139f2fd0	perf: add secondary FK indexes to core OSS schemas Add 13 secondary indexes across 10 tables in the SQLite schema and 5 missing indexes to the PostgreSQL schema. All list endpoints (listSites, listResources, listClients, listRoles, listTargets, listUserOrgs) filter and join on these FK columns. In the SQLite schema, no secondary indexes existed at all on these foreign keys, forcing full sequential scans on every paginated request and session lookup. SQLite changes (13 new indexes): - sites.orgId - resources.orgId - targets.resourceId, targets.siteId - newt.siteId - clients.orgId, clients.userId - labels.orgId - olms.userId - session.userId - userOrgs.userId, userOrgs.orgId - roles.orgId PG changes (5 new indexes, rest already present): - labels.orgId - session.userId - userOrgs.userId, userOrgs.orgId - roles.orgId - olms.userId	2026-08-31 17:04:01 -04:00
Owen Schwartz	ebcdeab414	Merge pull request #3597 from shleeable/patch-11 Fix: License.check can fail silently	2026-08-31 17:02:53 -04:00
Owen	2cc7d03ace	Update to fall back but still use cache	2026-08-31 17:02:03 -04:00
Shlee	34671c6b13	Update license.ts	2026-08-31 16:57:36 -04:00
Owen Schwartz	8dfc95347f	Merge pull request #3655 from moverq1337/fix/access-token-delete-root-key Fix access token deletion for root API keys	2026-08-31 16:54:18 -04:00
Owen Schwartz	0dece5fef1	Merge pull request #3665 from fosrl/dev Fix domain namespaces and country is not in blueprints	2026-08-31 11:03:06 -04:00
Owen	e7f38c089f	Merge branch 'main' into dev	2026-08-31 11:02:01 -04:00
miloschwartz	dd0a5a359a	check for namespace domain before blocking org check	2026-08-31 10:59:50 -04:00
moverq1337	1650ece0c3	Fix root API key access in verifyApiKeyAccessTokenAccess	2026-08-28 22:45:31 +03:00
Owen Schwartz	bc56a2bed0	Merge pull request #3647 from argueta-xyz/country-is-not-rule-via-blueprint Allow COUNTRY_IS_NOT rules to be created via Blueprints	2026-08-28 10:52:24 -04:00
Alejandro Argueta	7cda28d685	Update RuleSchema to allow COUNTRY_IS_NOT rules to be created via blueprints as well.	2026-08-27 14:18:48 -07:00