51 files changed, 1447 insertions, 889 deletions
diff --git a/server/models/abuse/abuse-message.ts b/server/models/abuse/abuse-message.ts
index 20008768b..14a5bffa2 100644
--- a/server/models/abuse/abuse-message.ts
+++ b/server/models/abuse/abuse-message.ts
@@ -5,7 +5,7 @@ import { MAbuseMessage, MAbuseMessageFormattable } from '@server/types/models'
 import { AbuseMessage } from '@shared/models'
 import { AttributesOnly } from '@shared/typescript-utils'
 import { AccountModel, ScopeNames as AccountScopeNames } from '../account/account'
-import { getSort, throwIfNotValid } from '../utils'
+import { getSort, throwIfNotValid } from '../shared'
 import { AbuseModel } from './abuse'
 @Table({
diff --git a/server/models/abuse/abuse.ts b/server/models/abuse/abuse.ts
index 4c6a96a86..4ce40bf2f 100644
--- a/server/models/abuse/abuse.ts
+++ b/server/models/abuse/abuse.ts
@@ -34,13 +34,13 @@ import { AttributesOnly } from '@shared/typescript-utils'
 import { ABUSE_STATES, CONSTRAINTS_FIELDS } from '../../initializers/constants'
 import { MAbuseAdminFormattable, MAbuseAP, MAbuseFull, MAbuseReporter, MAbuseUserFormattable, MUserAccountId } from '../../types/models'
 import { AccountModel, ScopeNames as AccountScopeNames, SummaryOptions as AccountSummaryOptions } from '../account/account'
-import { getSort, throwIfNotValid } from '../utils'
+import { getSort, throwIfNotValid } from '../shared'
 import { ThumbnailModel } from '../video/thumbnail'
 import { ScopeNames as VideoScopeNames, VideoModel } from '../video/video'
 import { VideoBlacklistModel } from '../video/video-blacklist'
 import { ScopeNames as VideoChannelScopeNames, SummaryOptions as ChannelSummaryOptions, VideoChannelModel } from '../video/video-channel'
 import { ScopeNames as CommentScopeNames, VideoCommentModel } from '../video/video-comment'
-import { buildAbuseListQuery, BuildAbusesQueryOptions } from './abuse-query-builder'
+import { buildAbuseListQuery, BuildAbusesQueryOptions } from './sql/abuse-query-builder'
 import { VideoAbuseModel } from './video-abuse'
 import { VideoCommentAbuseModel } from './video-comment-abuse'
diff --git a/server/models/abuse/abuse-query-builder.ts b/server/models/abuse/sql/abuse-query-builder.ts
index 74f4542e5..282d4541a 100644
--- a/server/models/abuse/abuse-query-builder.ts
+++ b/server/models/abuse/sql/abuse-query-builder.ts
@@ -2,7 +2,7 @@
 import { exists } from '@server/helpers/custom-validators/misc'
 import { forceNumber } from '@shared/core-utils'
 import { AbuseFilter, AbuseState, AbuseVideoIs } from '@shared/models'
-import { buildBlockedAccountSQL, buildDirectionAndField } from '../utils'
+import { buildBlockedAccountSQL, buildSortDirectionAndField } from '../../shared'
 export type BuildAbusesQueryOptions = {
  start: number
@@ -157,7 +157,7 @@ function buildAbuseListQuery (options: BuildAbusesQueryOptions, type: 'count' |
 }
 function buildAbuseOrder (value: string) {
-  const { direction, field } = buildDirectionAndField(value)
+  const { direction, field } = buildSortDirectionAndField(value)
  return `ORDER BY "abuse"."${field}" ${direction}`
 }
diff --git a/server/models/account/account-blocklist.ts b/server/models/account/account-blocklist.ts
index 377249b38..f6212ff6e 100644
--- a/server/models/account/account-blocklist.ts
+++ b/server/models/account/account-blocklist.ts
@@ -6,7 +6,7 @@ import { AttributesOnly } from '@shared/typescript-utils'
 import { AccountBlock } from '../../../shared/models'
 import { ActorModel } from '../actor/actor'
 import { ServerModel } from '../server/server'
-import { createSafeIn, getSort, searchAttribute } from '../utils'
+import { createSafeIn, getSort, searchAttribute } from '../shared'
 import { AccountModel } from './account'
 @Table({
diff --git a/server/models/account/account-video-rate.ts b/server/models/account/account-video-rate.ts
index 7afc907da..9e7ef4394 100644
--- a/server/models/account/account-video-rate.ts
+++ b/server/models/account/account-video-rate.ts
@@ -11,7 +11,7 @@ import { AttributesOnly } from '@shared/typescript-utils'
 import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc'
 import { CONSTRAINTS_FIELDS, VIDEO_RATE_TYPES } from '../../initializers/constants'
 import { ActorModel } from '../actor/actor'
-import { getSort, throwIfNotValid } from '../utils'
+import { getSort, throwIfNotValid } from '../shared'
 import { VideoModel } from '../video/video'
 import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from '../video/video-channel'
 import { AccountModel } from './account'
diff --git a/server/models/account/account.ts b/server/models/account/account.ts
index 8a7dfba94..dc989417b 100644
--- a/server/models/account/account.ts
+++ b/server/models/account/account.ts
@@ -16,7 +16,7 @@ import {
  Table,
  UpdatedAt
 } from 'sequelize-typescript'
-import { ModelCache } from '@server/models/model-cache'
+import { ModelCache } from '@server/models/shared/model-cache'
 import { AttributesOnly } from '@shared/typescript-utils'
 import { Account, AccountSummary } from '../../../shared/models/actors'
 import { isAccountDescriptionValid } from '../../helpers/custom-validators/accounts'
@@ -38,7 +38,7 @@ import { ApplicationModel } from '../application/application'
 import { ServerModel } from '../server/server'
 import { ServerBlocklistModel } from '../server/server-blocklist'
 import { UserModel } from '../user/user'
-import { getSort, throwIfNotValid } from '../utils'
+import { buildSQLAttributes, getSort, throwIfNotValid } from '../shared'
 import { VideoModel } from '../video/video'
 import { VideoChannelModel } from '../video/video-channel'
 import { VideoCommentModel } from '../video/video-comment'
@@ -251,6 +251,18 @@ export class AccountModel extends Model<Partial<AttributesOnly<AccountModel>>> {
    return undefined
  }
+  // ---------------------------------------------------------------------------
+  static getSQLAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix
+    })
+  }
+  // ---------------------------------------------------------------------------
  static load (id: number, transaction?: Transaction): Promise<MAccountDefault> {
    return AccountModel.findByPk(id, { transaction })
  }
diff --git a/server/models/actor/actor-follow.ts b/server/models/actor/actor-follow.ts
index 9615229dd..32e5d78b0 100644
--- a/server/models/actor/actor-follow.ts
+++ b/server/models/actor/actor-follow.ts
@@ -38,7 +38,7 @@ import { ACTOR_FOLLOW_SCORE, CONSTRAINTS_FIELDS, FOLLOW_STATES, SERVER_ACTOR_NAM
 import { AccountModel } from '../account/account'
 import { ServerModel } from '../server/server'
 import { doesExist } from '../shared/query'
-import { createSafeIn, getSort, searchAttribute, throwIfNotValid } from '../utils'
+import { buildSQLAttributes, createSafeIn, getSort, searchAttribute, throwIfNotValid } from '../shared'
 import { VideoChannelModel } from '../video/video-channel'
 import { ActorModel, unusedActorAttributesForAPI } from './actor'
 import { InstanceListFollowersQueryBuilder, ListFollowersOptions } from './sql/instance-list-followers-query-builder'
@@ -140,6 +140,18 @@ export class ActorFollowModel extends Model<Partial<AttributesOnly<ActorFollowMo
    })
  }
+  // ---------------------------------------------------------------------------
+  static getSQLAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix
+    })
+  }
+  // ---------------------------------------------------------------------------
  /*
   * @deprecated Use `findOrCreateCustom` instead
  */
@@ -213,7 +225,7 @@ export class ActorFollowModel extends Model<Partial<AttributesOnly<ActorFollowMo
      `WHERE "actorId" = $followerActorId AND "targetActorId" = $actorId AND "state" = 'accepted' ` +
      `LIMIT 1`
-    return doesExist(query, { actorId, followerActorId })
+    return doesExist(this.sequelize, query, { actorId, followerActorId })
  }
  static loadByActorAndTarget (actorId: number, targetActorId: number, t?: Transaction): Promise<MActorFollowActorsDefault> {
diff --git a/server/models/actor/actor-image.ts b/server/models/actor/actor-image.ts
index f2b3b2f4b..9c34a0101 100644
--- a/server/models/actor/actor-image.ts
+++ b/server/models/actor/actor-image.ts
@@ -22,7 +22,7 @@ import { isActivityPubUrlValid } from '../../helpers/custom-validators/activityp
 import { logger } from '../../helpers/logger'
 import { CONFIG } from '../../initializers/config'
 import { LAZY_STATIC_PATHS, MIMETYPES, WEBSERVER } from '../../initializers/constants'
-import { throwIfNotValid } from '../utils'
+import { buildSQLAttributes, throwIfNotValid } from '../shared'
 import { ActorModel } from './actor'
 @Table({
@@ -94,6 +94,18 @@ export class ActorImageModel extends Model<Partial<AttributesOnly<ActorImageMode
      .catch(err => logger.error('Cannot remove actor image file %s.', instance.filename, { err }))
  }
+  // ---------------------------------------------------------------------------
+  static getSQLAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix
+    })
+  }
+  // ---------------------------------------------------------------------------
  static loadByName (filename: string) {
    const query = {
      where: {
diff --git a/server/models/actor/actor.ts b/server/models/actor/actor.ts
index d7afa727d..1432e8757 100644
--- a/server/models/actor/actor.ts
+++ b/server/models/actor/actor.ts
@@ -17,7 +17,7 @@ import {
 } from 'sequelize-typescript'
 import { activityPubContextify } from '@server/lib/activitypub/context'
 import { getBiggestActorImage } from '@server/lib/actor-image'
-import { ModelCache } from '@server/models/model-cache'
+import { ModelCache } from '@server/models/shared/model-cache'
 import { forceNumber, getLowercaseExtension } from '@shared/core-utils'
 import { ActivityIconObject, ActivityPubActorType, ActorImageType } from '@shared/models'
 import { AttributesOnly } from '@shared/typescript-utils'
@@ -55,7 +55,7 @@ import {
 import { AccountModel } from '../account/account'
 import { getServerActor } from '../application/application'
 import { ServerModel } from '../server/server'
-import { isOutdated, throwIfNotValid } from '../utils'
+import { buildSQLAttributes, isOutdated, throwIfNotValid } from '../shared'
 import { VideoModel } from '../video/video'
 import { VideoChannelModel } from '../video/video-channel'
 import { ActorFollowModel } from './actor-follow'
@@ -65,7 +65,7 @@ enum ScopeNames {
  FULL = 'FULL'
 }
-export const unusedActorAttributesForAPI = [
+export const unusedActorAttributesForAPI: (keyof AttributesOnly<ActorModel>)[] = [
  'publicKey',
  'privateKey',
  'inboxUrl',
@@ -306,6 +306,27 @@ export class ActorModel extends Model<Partial<AttributesOnly<ActorModel>>> {
  })
  VideoChannel: VideoChannelModel
+  // ---------------------------------------------------------------------------
+  static getSQLAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix
+    })
+  }
+  static getSQLAPIAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix,
+      excludeAttributes: unusedActorAttributesForAPI
+    })
+  }
+  // ---------------------------------------------------------------------------
  static async load (id: number): Promise<MActor> {
    const actorServer = await getServerActor()
    if (id === actorServer.id) return actorServer
diff --git a/server/models/actor/sql/instance-list-followers-query-builder.ts b/server/models/actor/sql/instance-list-followers-query-builder.ts
index 4a17a8f11..34ce29b5d 100644
--- a/server/models/actor/sql/instance-list-followers-query-builder.ts
+++ b/server/models/actor/sql/instance-list-followers-query-builder.ts
@@ -1,8 +1,8 @@
 import { Sequelize } from 'sequelize'
 import { ModelBuilder } from '@server/models/shared'
-import { parseRowCountResult } from '@server/models/utils'
 import { MActorFollowActorsDefault } from '@server/types/models'
 import { ActivityPubActorType, FollowState } from '@shared/models'
+import { parseRowCountResult } from '../../shared'
 import { InstanceListFollowsQueryBuilder } from './shared/instance-list-follows-query-builder'
 export interface ListFollowersOptions {
diff --git a/server/models/actor/sql/instance-list-following-query-builder.ts b/server/models/actor/sql/instance-list-following-query-builder.ts
index 880170b85..77b4e3dce 100644
--- a/server/models/actor/sql/instance-list-following-query-builder.ts
+++ b/server/models/actor/sql/instance-list-following-query-builder.ts
@@ -1,8 +1,8 @@
 import { Sequelize } from 'sequelize'
 import { ModelBuilder } from '@server/models/shared'
-import { parseRowCountResult } from '@server/models/utils'
 import { MActorFollowActorsDefault } from '@server/types/models'
 import { ActivityPubActorType, FollowState } from '@shared/models'
+import { parseRowCountResult } from '../../shared'
 import { InstanceListFollowsQueryBuilder } from './shared/instance-list-follows-query-builder'
 export interface ListFollowingOptions {
diff --git a/server/models/actor/sql/shared/actor-follow-table-attributes.ts b/server/models/actor/sql/shared/actor-follow-table-attributes.ts
index 156b37d44..7dd908ece 100644
--- a/server/models/actor/sql/shared/actor-follow-table-attributes.ts
+++ b/server/models/actor/sql/shared/actor-follow-table-attributes.ts
@@ -1,62 +1,31 @@
+import { logger } from '@server/helpers/logger'
+import { Memoize } from '@server/helpers/memoize'
+import { ServerModel } from '@server/models/server/server'
+import { ActorModel } from '../../actor'
+import { ActorFollowModel } from '../../actor-follow'
+import { ActorImageModel } from '../../actor-image'
 export class ActorFollowTableAttributes {
+  @Memoize()
  getFollowAttributes () {
-    return [
+    logger.error('coucou')
-      '"ActorFollowModel"."id"',
-      '"ActorFollowModel"."state"',
+    return ActorFollowModel.getSQLAttributes('ActorFollowModel').join(', ')
-      '"ActorFollowModel"."score"',
-      '"ActorFollowModel"."url"',
-      '"ActorFollowModel"."actorId"',
-      '"ActorFollowModel"."targetActorId"',
-      '"ActorFollowModel"."createdAt"',
-      '"ActorFollowModel"."updatedAt"'
-    ].join(', ')
  }
+  @Memoize()
  getActorAttributes (actorTableName: string) {
-    return [
+    return ActorModel.getSQLAttributes(actorTableName, `${actorTableName}.`).join(', ')
-      `"${actorTableName}"."id" AS "${actorTableName}.id"`,
-      `"${actorTableName}"."type" AS "${actorTableName}.type"`,
-      `"${actorTableName}"."preferredUsername" AS "${actorTableName}.preferredUsername"`,
-      `"${actorTableName}"."url" AS "${actorTableName}.url"`,
-      `"${actorTableName}"."publicKey" AS "${actorTableName}.publicKey"`,
-      `"${actorTableName}"."privateKey" AS "${actorTableName}.privateKey"`,
-      `"${actorTableName}"."followersCount" AS "${actorTableName}.followersCount"`,
-      `"${actorTableName}"."followingCount" AS "${actorTableName}.followingCount"`,
-      `"${actorTableName}"."inboxUrl" AS "${actorTableName}.inboxUrl"`,
-      `"${actorTableName}"."outboxUrl" AS "${actorTableName}.outboxUrl"`,
-      `"${actorTableName}"."sharedInboxUrl" AS "${actorTableName}.sharedInboxUrl"`,
-      `"${actorTableName}"."followersUrl" AS "${actorTableName}.followersUrl"`,
-      `"${actorTableName}"."followingUrl" AS "${actorTableName}.followingUrl"`,
-      `"${actorTableName}"."remoteCreatedAt" AS "${actorTableName}.remoteCreatedAt"`,
-      `"${actorTableName}"."serverId" AS "${actorTableName}.serverId"`,
-      `"${actorTableName}"."createdAt" AS "${actorTableName}.createdAt"`,
-      `"${actorTableName}"."updatedAt" AS "${actorTableName}.updatedAt"`
-    ].join(', ')
  }
+  @Memoize()
  getServerAttributes (actorTableName: string) {
-    return [
+    return ServerModel.getSQLAttributes(`${actorTableName}->Server`, `${actorTableName}.Server.`).join(', ')
-      `"${actorTableName}->Server"."id" AS "${actorTableName}.Server.id"`,
-      `"${actorTableName}->Server"."host" AS "${actorTableName}.Server.host"`,
-      `"${actorTableName}->Server"."redundancyAllowed" AS "${actorTableName}.Server.redundancyAllowed"`,
-      `"${actorTableName}->Server"."createdAt" AS "${actorTableName}.Server.createdAt"`,
-      `"${actorTableName}->Server"."updatedAt" AS "${actorTableName}.Server.updatedAt"`
-    ].join(', ')
  }
+  @Memoize()
  getAvatarAttributes (actorTableName: string) {
-    return [
+    return ActorImageModel.getSQLAttributes(`${actorTableName}->Avatars`, `${actorTableName}.Avatars.`).join(', ')
-      `"${actorTableName}->Avatars"."id" AS "${actorTableName}.Avatars.id"`,
-      `"${actorTableName}->Avatars"."filename" AS "${actorTableName}.Avatars.filename"`,
-      `"${actorTableName}->Avatars"."height" AS "${actorTableName}.Avatars.height"`,
-      `"${actorTableName}->Avatars"."width" AS "${actorTableName}.Avatars.width"`,
-      `"${actorTableName}->Avatars"."fileUrl" AS "${actorTableName}.Avatars.fileUrl"`,
-      `"${actorTableName}->Avatars"."onDisk" AS "${actorTableName}.Avatars.onDisk"`,
-      `"${actorTableName}->Avatars"."type" AS "${actorTableName}.Avatars.type"`,
-      `"${actorTableName}->Avatars"."actorId" AS "${actorTableName}.Avatars.actorId"`,
-      `"${actorTableName}->Avatars"."createdAt" AS "${actorTableName}.Avatars.createdAt"`,
-      `"${actorTableName}->Avatars"."updatedAt" AS "${actorTableName}.Avatars.updatedAt"`
-    ].join(', ')
  }
 }
diff --git a/server/models/actor/sql/shared/instance-list-follows-query-builder.ts b/server/models/actor/sql/shared/instance-list-follows-query-builder.ts
index 1d70fbe70..d9593e48b 100644
--- a/server/models/actor/sql/shared/instance-list-follows-query-builder.ts
+++ b/server/models/actor/sql/shared/instance-list-follows-query-builder.ts
@@ -1,7 +1,7 @@
 import { Sequelize } from 'sequelize'
 import { AbstractRunQuery } from '@server/models/shared'
-import { getInstanceFollowsSort } from '@server/models/utils'
 import { ActorImageType } from '@shared/models'
+import { getInstanceFollowsSort } from '../../../shared'
 import { ActorFollowTableAttributes } from './actor-follow-table-attributes'
 type BaseOptions = {
diff --git a/server/models/redundancy/video-redundancy.ts b/server/models/redundancy/video-redundancy.ts
index 15909d5f3..c2a72b71f 100644
--- a/server/models/redundancy/video-redundancy.ts
+++ b/server/models/redundancy/video-redundancy.ts
@@ -34,7 +34,7 @@ import { CONFIG } from '../../initializers/config'
 import { CONSTRAINTS_FIELDS, MIMETYPES } from '../../initializers/constants'
 import { ActorModel } from '../actor/actor'
 import { ServerModel } from '../server/server'
-import { getSort, getVideoSort, parseAggregateResult, throwIfNotValid } from '../utils'
+import { getSort, getVideoSort, parseAggregateResult, throwIfNotValid } from '../shared'
 import { ScheduleVideoUpdateModel } from '../video/schedule-video-update'
 import { VideoModel } from '../video/video'
 import { VideoChannelModel } from '../video/video-channel'
diff --git a/server/models/server/plugin.ts b/server/models/server/plugin.ts
index 71c205ffa..9948c9f7a 100644
--- a/server/models/server/plugin.ts
+++ b/server/models/server/plugin.ts
@@ -11,7 +11,7 @@ import {
  isPluginStableVersionValid,
  isPluginTypeValid
 } from '../../helpers/custom-validators/plugins'
-import { getSort, throwIfNotValid } from '../utils'
+import { getSort, throwIfNotValid } from '../shared'
 @DefaultScope(() => ({
  attributes: {
diff --git a/server/models/server/server-blocklist.ts b/server/models/server/server-blocklist.ts
index 9752dfbc3..3d755fe4a 100644
--- a/server/models/server/server-blocklist.ts
+++ b/server/models/server/server-blocklist.ts
@@ -4,7 +4,7 @@ import { MServerBlocklist, MServerBlocklistAccountServer, MServerBlocklistFormat
 import { ServerBlock } from '@shared/models'
 import { AttributesOnly } from '@shared/typescript-utils'
 import { AccountModel } from '../account/account'
-import { createSafeIn, getSort, searchAttribute } from '../utils'
+import { createSafeIn, getSort, searchAttribute } from '../shared'
 import { ServerModel } from './server'
 enum ScopeNames {
diff --git a/server/models/server/server.ts b/server/models/server/server.ts
index ef42de090..a5e05f460 100644
--- a/server/models/server/server.ts
+++ b/server/models/server/server.ts
@@ -4,7 +4,7 @@ import { MServer, MServerFormattable } from '@server/types/models/server'
 import { AttributesOnly } from '@shared/typescript-utils'
 import { isHostValid } from '../../helpers/custom-validators/servers'
 import { ActorModel } from '../actor/actor'
-import { throwIfNotValid } from '../utils'
+import { buildSQLAttributes, throwIfNotValid } from '../shared'
 import { ServerBlocklistModel } from './server-blocklist'
 @Table({
@@ -52,6 +52,18 @@ export class ServerModel extends Model<Partial<AttributesOnly<ServerModel>>> {
  })
  BlockedBy: ServerBlocklistModel[]
+  // ---------------------------------------------------------------------------
+  static getSQLAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix
+    })
+  }
+  // ---------------------------------------------------------------------------
  static load (id: number, transaction?: Transaction): Promise<MServer> {
    const query = {
      where: {
diff --git a/server/models/shared/index.ts b/server/models/shared/index.ts
index 04528929c..5a7621e4d 100644
--- a/server/models/shared/index.ts
+++ b/server/models/shared/index.ts
@@ -1,4 +1,8 @@
 export * from './abstract-run-query'
 export * from './model-builder'
+export * from './model-cache'
 export * from './query'
+export * from './sequelize-helpers'
+export * from './sort'
+export * from './sql'
 export * from './update'
diff --git a/server/models/shared/model-builder.ts b/server/models/shared/model-builder.ts
index c015ca4f5..07f7c4038 100644
--- a/server/models/shared/model-builder.ts
+++ b/server/models/shared/model-builder.ts
@@ -1,7 +1,24 @@
 import { isPlainObject } from 'lodash'
-import { Model as SequelizeModel, Sequelize } from 'sequelize'
+import { Model as SequelizeModel, ModelStatic, Sequelize } from 'sequelize'
 import { logger } from '@server/helpers/logger'
+/**
+ *
+ * Build Sequelize models from sequelize raw query (that must use { nest: true } options)
+ *
+ * In order to sequelize to correctly build the JSON this class will ingest,
+ * the columns selected in the raw query should be in the following form:
+ *   * All tables must be Pascal Cased (for example "VideoChannel")
+ *   * Root table must end with `Model` (for example "VideoCommentModel")
+ *   * Joined tables must contain the origin table name + '->JoinedTable'. For example:
+ *     * "Actor" is joined to "Account": "Actor" table must be renamed "Account->Actor"
+ *     * "Account->Actor" is joined to "Server": "Server" table must be renamed to "Account->Actor->Server"
+ *   * Selected columns must be renamed to contain the JSON path:
+ *     * "videoComment"."id": "VideoCommentModel"."id"
+ *     * "Account"."Actor"."Server"."id": "Account.Actor.Server.id"
+ *   * All tables must contain the row id
+ */
 export class ModelBuilder <T extends SequelizeModel> {
  private readonly modelRegistry = new Map<string, T>()
@@ -72,18 +89,18 @@ export class ModelBuilder <T extends SequelizeModel> {
        'Cannot build model %s that does not exist', this.buildSequelizeModelName(modelName),
        { existing: this.sequelize.modelManager.all.map(m => m.name) }
      )
-      return undefined
+      return { created: false, model: null }
    }
-    // FIXME: typings
+    const model = Model.build(json, { raw: true, isNewRecord: false })
-    const model = new (Model as any)(json)
    this.modelRegistry.set(registryKey, model)
    return { created: true, model }
  }
  private findModelBuilder (modelName: string) {
-    return this.sequelize.modelManager.getModel(this.buildSequelizeModelName(modelName))
+    return this.sequelize.modelManager.getModel(this.buildSequelizeModelName(modelName)) as ModelStatic<T>
  }
  private buildSequelizeModelName (modelName: string) {
diff --git a/server/models/model-cache.ts b/server/models/shared/model-cache.ts
index 3651267e7..3651267e7 100644
--- a/server/models/model-cache.ts
+++ b/server/models/shared/model-cache.ts
diff --git a/server/models/shared/query.ts b/server/models/shared/query.ts
index 036cc13c6..934acc21f 100644
--- a/server/models/shared/query.ts
+++ b/server/models/shared/query.ts
@@ -1,17 +1,82 @@
-import { BindOrReplacements, QueryTypes } from 'sequelize'
+import { BindOrReplacements, Op, QueryTypes, Sequelize } from 'sequelize'
-import { sequelizeTypescript } from '@server/initializers/database'
+import validator from 'validator'
+import { forceNumber } from '@shared/core-utils'
-function doesExist (query: string, bind?: BindOrReplacements) {
+function doesExist (sequelize: Sequelize, query: string, bind?: BindOrReplacements) {
  const options = {
    type: QueryTypes.SELECT as QueryTypes.SELECT,
    bind,
    raw: true
  }
-  return sequelizeTypescript.query(query, options)
+  return sequelize.query(query, options)
            .then(results => results.length === 1)
 }
+function createSimilarityAttribute (col: string, value: string) {
+  return Sequelize.fn(
+    'similarity',
+    searchTrigramNormalizeCol(col),
+    searchTrigramNormalizeValue(value)
+  )
+}
+function buildWhereIdOrUUID (id: number | string) {
+  return validator.isInt('' + id) ? { id } : { uuid: id }
+}
+function parseAggregateResult (result: any) {
+  if (!result) return 0
+  const total = forceNumber(result)
+  if (isNaN(total)) return 0
+  return total
+}
+function parseRowCountResult (result: any) {
+  if (result.length !== 0) return result[0].total
+  return 0
+}
+function createSafeIn (sequelize: Sequelize, toEscape: (string | number)[], additionalUnescaped: string[] = []) {
+  return toEscape.map(t => {
+    return t === null
+      ? null
+      : sequelize.escape('' + t)
+  }).concat(additionalUnescaped).join(', ')
+}
+function searchAttribute (sourceField?: string, targetField?: string) {
+  if (!sourceField) return {}
+  return {
+    [targetField]: {
+      // FIXME: ts error
+      [Op.iLike as any]: `%${sourceField}%`
+    }
+  }
+}
 export {
-  doesExist
+  doesExist,
+  createSimilarityAttribute,
+  buildWhereIdOrUUID,
+  parseAggregateResult,
+  parseRowCountResult,
+  createSafeIn,
+  searchAttribute
+}
+// ---------------------------------------------------------------------------
+function searchTrigramNormalizeValue (value: string) {
+  return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value))
+}
+function searchTrigramNormalizeCol (col: string) {
+  return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
 }
diff --git a/server/models/shared/sequelize-helpers.ts b/server/models/shared/sequelize-helpers.ts
new file mode 100644
index 000000000..7af8471dc
--- /dev/null
+++ b/server/models/shared/sequelize-helpers.ts
@@ -0,0 +1,39 @@
+import { Sequelize } from 'sequelize'
+function isOutdated (model: { createdAt: Date, updatedAt: Date }, refreshInterval: number) {
+  if (!model.createdAt || !model.updatedAt) {
+    throw new Error('Miss createdAt & updatedAt attributes to model')
+  }
+  const now = Date.now()
+  const createdAtTime = model.createdAt.getTime()
+  const updatedAtTime = model.updatedAt.getTime()
+  return (now - createdAtTime) > refreshInterval && (now - updatedAtTime) > refreshInterval
+}
+function throwIfNotValid (value: any, validator: (value: any) => boolean, fieldName = 'value', nullable = false) {
+  if (nullable && (value === null || value === undefined)) return
+  if (validator(value) === false) {
+    throw new Error(`"${value}" is not a valid ${fieldName}.`)
+  }
+}
+function buildTrigramSearchIndex (indexName: string, attribute: string) {
+  return {
+    name: indexName,
+    // FIXME: gin_trgm_ops is not taken into account in Sequelize 6, so adding it ourselves in the literal function
+    fields: [ Sequelize.literal('lower(immutable_unaccent(' + attribute + ')) gin_trgm_ops') as any ],
+    using: 'gin',
+    operator: 'gin_trgm_ops'
+  }
+}
+// ---------------------------------------------------------------------------
+export {
+  throwIfNotValid,
+  buildTrigramSearchIndex,
+  isOutdated
+}
diff --git a/server/models/shared/sort.ts b/server/models/shared/sort.ts
new file mode 100644
index 000000000..d923072f2
--- /dev/null
+++ b/server/models/shared/sort.ts
@@ -0,0 +1,146 @@
+import { literal, OrderItem, Sequelize } from 'sequelize'
+// Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ]
+function getSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
+  const { direction, field } = buildSortDirectionAndField(value)
+  let finalField: string | ReturnType<typeof Sequelize.col>
+  if (field.toLowerCase() === 'match') { // Search
+    finalField = Sequelize.col('similarity')
+  } else {
+    finalField = field
+  }
+  return [ [ finalField, direction ], lastSort ]
+}
+function getAdminUsersSort (value: string): OrderItem[] {
+  const { direction, field } = buildSortDirectionAndField(value)
+  let finalField: string | ReturnType<typeof Sequelize.col>
+  if (field === 'videoQuotaUsed') { // Users list
+    finalField = Sequelize.col('videoQuotaUsed')
+  } else {
+    finalField = field
+  }
+  const nullPolicy = direction === 'ASC'
+    ? 'NULLS FIRST'
+    : 'NULLS LAST'
+  // FIXME: typings
+  return [ [ finalField as any, direction, nullPolicy ], [ 'id', 'ASC' ] ]
+}
+function getPlaylistSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
+  const { direction, field } = buildSortDirectionAndField(value)
+  if (field.toLowerCase() === 'name') {
+    return [ [ 'displayName', direction ], lastSort ]
+  }
+  return getSort(value, lastSort)
+}
+function getVideoSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
+  const { direction, field } = buildSortDirectionAndField(value)
+  if (field.toLowerCase() === 'trending') { // Sort by aggregation
+    return [
+      [ Sequelize.fn('COALESCE', Sequelize.fn('SUM', Sequelize.col('VideoViews.views')), '0'), direction ],
+      [ Sequelize.col('VideoModel.views'), direction ],
+      lastSort
+    ]
+  } else if (field === 'publishedAt') {
+    return [
+      [ 'ScheduleVideoUpdate', 'updateAt', direction + ' NULLS LAST' ],
+      [ Sequelize.col('VideoModel.publishedAt'), direction ],
+      lastSort
+    ]
+  }
+  let finalField: string | ReturnType<typeof Sequelize.col>
+  // Alias
+  if (field.toLowerCase() === 'match') { // Search
+    finalField = Sequelize.col('similarity')
+  } else {
+    finalField = field
+  }
+  const firstSort: OrderItem = typeof finalField === 'string'
+    ? finalField.split('.').concat([ direction ]) as OrderItem
+    : [ finalField, direction ]
+  return [ firstSort, lastSort ]
+}
+function getBlacklistSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
+  const { direction, field } = buildSortDirectionAndField(value)
+  const videoFields = new Set([ 'name', 'duration', 'views', 'likes', 'dislikes', 'uuid' ])
+  if (videoFields.has(field)) {
+    return [
+      [ literal(`"Video.${field}" ${direction}`) ],
+      lastSort
+    ] as OrderItem[]
+  }
+  return getSort(value, lastSort)
+}
+function getInstanceFollowsSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
+  const { direction, field } = buildSortDirectionAndField(value)
+  if (field === 'redundancyAllowed') {
+    return [
+      [ 'ActorFollowing.Server.redundancyAllowed', direction ],
+      lastSort
+    ]
+  }
+  return getSort(value, lastSort)
+}
+function getChannelSyncSort (value: string): OrderItem[] {
+  const { direction, field } = buildSortDirectionAndField(value)
+  if (field.toLowerCase() === 'videochannel') {
+    return [
+      [ literal('"VideoChannel.name"'), direction ]
+    ]
+  }
+  return [ [ field, direction ] ]
+}
+function buildSortDirectionAndField (value: string) {
+  let field: string
+  let direction: 'ASC' | 'DESC'
+  if (value.substring(0, 1) === '-') {
+    direction = 'DESC'
+    field = value.substring(1)
+  } else {
+    direction = 'ASC'
+    field = value
+  }
+  return { direction, field }
+}
+export {
+  buildSortDirectionAndField,
+  getPlaylistSort,
+  getSort,
+  getAdminUsersSort,
+  getVideoSort,
+  getBlacklistSort,
+  getChannelSyncSort,
+  getInstanceFollowsSort
+}
diff --git a/server/models/shared/sql.ts b/server/models/shared/sql.ts
new file mode 100644
index 000000000..5aaeb49f0
--- /dev/null
+++ b/server/models/shared/sql.ts
@@ -0,0 +1,68 @@
+import { literal, Model, ModelStatic } from 'sequelize'
+import { forceNumber } from '@shared/core-utils'
+import { AttributesOnly } from '@shared/typescript-utils'
+function buildLocalAccountIdsIn () {
+  return literal(
+    '(SELECT "account"."id" FROM "account" INNER JOIN "actor" ON "actor"."id" = "account"."actorId" AND "actor"."serverId" IS NULL)'
+  )
+}
+function buildLocalActorIdsIn () {
+  return literal(
+    '(SELECT "actor"."id" FROM "actor" WHERE "actor"."serverId" IS NULL)'
+  )
+}
+function buildBlockedAccountSQL (blockerIds: number[]) {
+  const blockerIdsString = blockerIds.join(', ')
+  return 'SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (' + blockerIdsString + ')' +
+    ' UNION ' +
+    'SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id ' +
+    'INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ' +
+    'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')'
+}
+function buildServerIdsFollowedBy (actorId: any) {
+  const actorIdNumber = forceNumber(actorId)
+  return '(' +
+    'SELECT "actor"."serverId" FROM "actorFollow" ' +
+    'INNER JOIN "actor" ON actor.id = "actorFollow"."targetActorId" ' +
+    'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
+    ')'
+}
+function buildSQLAttributes<M extends Model> (options: {
+  model: ModelStatic<M>
+  tableName: string
+  excludeAttributes?: Exclude<keyof AttributesOnly<M>, symbol>[]
+  aliasPrefix?: string
+}) {
+  const { model, tableName, aliasPrefix, excludeAttributes } = options
+  const attributes = Object.keys(model.getAttributes()) as Exclude<keyof AttributesOnly<M>, symbol>[]
+  return attributes
+    .filter(a => {
+      if (!excludeAttributes) return true
+      if (excludeAttributes.includes(a)) return false
+      return true
+    })
+    .map(a => {
+      return `"${tableName}"."${a}" AS "${aliasPrefix || ''}${a}"`
+    })
+}
+// ---------------------------------------------------------------------------
+export {
+  buildSQLAttributes,
+  buildBlockedAccountSQL,
+  buildServerIdsFollowedBy,
+  buildLocalAccountIdsIn,
+  buildLocalActorIdsIn
+}
diff --git a/server/models/shared/update.ts b/server/models/shared/update.ts
index d338211e3..d02c4535d 100644
--- a/server/models/shared/update.ts
+++ b/server/models/shared/update.ts
@@ -1,9 +1,15 @@
-import { QueryTypes, Transaction } from 'sequelize'
+import { QueryTypes, Sequelize, Transaction } from 'sequelize'
-import { sequelizeTypescript } from '@server/initializers/database'
 // Sequelize always skip the update if we only update updatedAt field
-function setAsUpdated (table: string, id: number, transaction?: Transaction) {
+function setAsUpdated (options: {
-  return sequelizeTypescript.query(
+  sequelize: Sequelize
+  table: string
+  id: number
+  transaction?: Transaction
+}) {
+  const { sequelize, table, id, transaction } = options
+  return sequelize.query(
    `UPDATE "${table}" SET "updatedAt" = :updatedAt WHERE id = :id`,
    {
      replacements: { table, id, updatedAt: new Date() },
diff --git a/server/models/user/sql/user-notitication-list-query-builder.ts b/server/models/user/sql/user-notitication-list-query-builder.ts
index 31b4932bf..7b29807a3 100644
--- a/server/models/user/sql/user-notitication-list-query-builder.ts
+++ b/server/models/user/sql/user-notitication-list-query-builder.ts
@@ -1,8 +1,8 @@
 import { Sequelize } from 'sequelize'
 import { AbstractRunQuery, ModelBuilder } from '@server/models/shared'
-import { getSort } from '@server/models/utils'
 import { UserNotificationModelForApi } from '@server/types/models'
 import { ActorImageType } from '@shared/models'
+import { getSort } from '../../shared'
 export interface ListNotificationsOptions {
  userId: number
@@ -180,7 +180,9 @@ export class UserNotificationListQueryBuilder extends AbstractRunQuery {
      "Account->Actor->Avatars"."type" AS "Account.Actor.Avatars.type",
      "Account->Actor->Avatars"."filename" AS "Account.Actor.Avatars.filename",
      "Account->Actor->Server"."id" AS "Account.Actor.Server.id",
-      "Account->Actor->Server"."host" AS "Account.Actor.Server.host"`
+      "Account->Actor->Server"."host" AS "Account.Actor.Server.host",
+      "UserRegistration"."id" AS "UserRegistration.id",
+      "UserRegistration"."username" AS "UserRegistration.username"`
  }
  private getJoins () {
@@ -196,74 +198,76 @@ export class UserNotificationListQueryBuilder extends AbstractRunQuery {
        ON "Video->VideoChannel->Actor"."serverId" = "Video->VideoChannel->Actor->Server"."id"
    ) ON "UserNotificationModel"."videoId" = "Video"."id"
-  LEFT JOIN (
+    LEFT JOIN (
-    "videoComment" AS "VideoComment"
+      "videoComment" AS "VideoComment"
-    INNER JOIN "account" AS "VideoComment->Account" ON "VideoComment"."accountId" = "VideoComment->Account"."id"
+      INNER JOIN "account" AS "VideoComment->Account" ON "VideoComment"."accountId" = "VideoComment->Account"."id"
-    INNER JOIN "actor" AS "VideoComment->Account->Actor" ON "VideoComment->Account"."actorId" = "VideoComment->Account->Actor"."id"
+      INNER JOIN "actor" AS "VideoComment->Account->Actor" ON "VideoComment->Account"."actorId" = "VideoComment->Account->Actor"."id"
-    LEFT JOIN "actorImage" AS "VideoComment->Account->Actor->Avatars"
+      LEFT JOIN "actorImage" AS "VideoComment->Account->Actor->Avatars"
-      ON "VideoComment->Account->Actor"."id" = "VideoComment->Account->Actor->Avatars"."actorId"
+        ON "VideoComment->Account->Actor"."id" = "VideoComment->Account->Actor->Avatars"."actorId"
-      AND "VideoComment->Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
+        AND "VideoComment->Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
-    LEFT JOIN "server" AS "VideoComment->Account->Actor->Server"
+      LEFT JOIN "server" AS "VideoComment->Account->Actor->Server"
-      ON "VideoComment->Account->Actor"."serverId" = "VideoComment->Account->Actor->Server"."id"
+        ON "VideoComment->Account->Actor"."serverId" = "VideoComment->Account->Actor->Server"."id"
-    INNER JOIN "video" AS "VideoComment->Video" ON "VideoComment"."videoId" = "VideoComment->Video"."id"
+      INNER JOIN "video" AS "VideoComment->Video" ON "VideoComment"."videoId" = "VideoComment->Video"."id"
-  ) ON "UserNotificationModel"."commentId" = "VideoComment"."id"
+    ) ON "UserNotificationModel"."commentId" = "VideoComment"."id"
+    LEFT JOIN "abuse" AS "Abuse" ON "UserNotificationModel"."abuseId" = "Abuse"."id"
+    LEFT JOIN "videoAbuse" AS "Abuse->VideoAbuse" ON "Abuse"."id" = "Abuse->VideoAbuse"."abuseId"
+    LEFT JOIN "video" AS "Abuse->VideoAbuse->Video" ON "Abuse->VideoAbuse"."videoId" = "Abuse->VideoAbuse->Video"."id"
+    LEFT JOIN "commentAbuse" AS "Abuse->VideoCommentAbuse" ON "Abuse"."id" = "Abuse->VideoCommentAbuse"."abuseId"
+    LEFT JOIN "videoComment" AS "Abuse->VideoCommentAbuse->VideoComment"
+      ON "Abuse->VideoCommentAbuse"."videoCommentId" = "Abuse->VideoCommentAbuse->VideoComment"."id"
+    LEFT JOIN "video" AS "Abuse->VideoCommentAbuse->VideoComment->Video"
+      ON "Abuse->VideoCommentAbuse->VideoComment"."videoId" = "Abuse->VideoCommentAbuse->VideoComment->Video"."id"
+    LEFT JOIN (
+      "account" AS "Abuse->FlaggedAccount"
+      INNER JOIN "actor" AS "Abuse->FlaggedAccount->Actor" ON "Abuse->FlaggedAccount"."actorId" = "Abuse->FlaggedAccount->Actor"."id"
+      LEFT JOIN "actorImage" AS "Abuse->FlaggedAccount->Actor->Avatars"
+        ON "Abuse->FlaggedAccount->Actor"."id" = "Abuse->FlaggedAccount->Actor->Avatars"."actorId"
+        AND "Abuse->FlaggedAccount->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
+      LEFT JOIN "server" AS "Abuse->FlaggedAccount->Actor->Server"
+        ON "Abuse->FlaggedAccount->Actor"."serverId" = "Abuse->FlaggedAccount->Actor->Server"."id"
+    ) ON "Abuse"."flaggedAccountId" = "Abuse->FlaggedAccount"."id"
-  LEFT JOIN "abuse" AS "Abuse" ON "UserNotificationModel"."abuseId" = "Abuse"."id"
+    LEFT JOIN (
-  LEFT JOIN "videoAbuse" AS "Abuse->VideoAbuse" ON "Abuse"."id" = "Abuse->VideoAbuse"."abuseId"
+      "videoBlacklist" AS "VideoBlacklist"
-  LEFT JOIN "video" AS "Abuse->VideoAbuse->Video" ON "Abuse->VideoAbuse"."videoId" = "Abuse->VideoAbuse->Video"."id"
+      INNER JOIN "video" AS "VideoBlacklist->Video" ON "VideoBlacklist"."videoId" = "VideoBlacklist->Video"."id"
-  LEFT JOIN "commentAbuse" AS "Abuse->VideoCommentAbuse" ON "Abuse"."id" = "Abuse->VideoCommentAbuse"."abuseId"
+    ) ON "UserNotificationModel"."videoBlacklistId" = "VideoBlacklist"."id"
-  LEFT JOIN "videoComment" AS "Abuse->VideoCommentAbuse->VideoComment"
-    ON "Abuse->VideoCommentAbuse"."videoCommentId" = "Abuse->VideoCommentAbuse->VideoComment"."id"
-  LEFT JOIN "video" AS "Abuse->VideoCommentAbuse->VideoComment->Video"
-    ON "Abuse->VideoCommentAbuse->VideoComment"."videoId" = "Abuse->VideoCommentAbuse->VideoComment->Video"."id"
-  LEFT JOIN (
-    "account" AS "Abuse->FlaggedAccount"
-    INNER JOIN "actor" AS "Abuse->FlaggedAccount->Actor" ON "Abuse->FlaggedAccount"."actorId" = "Abuse->FlaggedAccount->Actor"."id"
-    LEFT JOIN "actorImage" AS "Abuse->FlaggedAccount->Actor->Avatars"
-      ON "Abuse->FlaggedAccount->Actor"."id" = "Abuse->FlaggedAccount->Actor->Avatars"."actorId"
-      AND "Abuse->FlaggedAccount->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
-    LEFT JOIN "server" AS "Abuse->FlaggedAccount->Actor->Server"
-      ON "Abuse->FlaggedAccount->Actor"."serverId" = "Abuse->FlaggedAccount->Actor->Server"."id"
-  ) ON "Abuse"."flaggedAccountId" = "Abuse->FlaggedAccount"."id"
-  LEFT JOIN (
+    LEFT JOIN "videoImport" AS "VideoImport" ON "UserNotificationModel"."videoImportId" = "VideoImport"."id"
-    "videoBlacklist" AS "VideoBlacklist"
+    LEFT JOIN "video" AS "VideoImport->Video" ON "VideoImport"."videoId" = "VideoImport->Video"."id"
-    INNER JOIN "video" AS "VideoBlacklist->Video" ON "VideoBlacklist"."videoId" = "VideoBlacklist->Video"."id"
-  ) ON "UserNotificationModel"."videoBlacklistId" = "VideoBlacklist"."id"
-  LEFT JOIN "videoImport" AS "VideoImport" ON "UserNotificationModel"."videoImportId" = "VideoImport"."id"
+    LEFT JOIN "plugin" AS "Plugin" ON "UserNotificationModel"."pluginId" = "Plugin"."id"
-  LEFT JOIN "video" AS "VideoImport->Video" ON "VideoImport"."videoId" = "VideoImport->Video"."id"
-  LEFT JOIN "plugin" AS "Plugin" ON "UserNotificationModel"."pluginId" = "Plugin"."id"
+    LEFT JOIN "application" AS "Application" ON "UserNotificationModel"."applicationId" = "Application"."id"
-  LEFT JOIN "application" AS "Application" ON "UserNotificationModel"."applicationId" = "Application"."id"
+    LEFT JOIN (
+      "actorFollow" AS "ActorFollow"
+      INNER JOIN "actor" AS "ActorFollow->ActorFollower" ON "ActorFollow"."actorId" = "ActorFollow->ActorFollower"."id"
+      INNER JOIN "account" AS "ActorFollow->ActorFollower->Account"
+        ON "ActorFollow->ActorFollower"."id" = "ActorFollow->ActorFollower->Account"."actorId"
+      LEFT JOIN "actorImage" AS "ActorFollow->ActorFollower->Avatars"
+        ON "ActorFollow->ActorFollower"."id" = "ActorFollow->ActorFollower->Avatars"."actorId"
+        AND "ActorFollow->ActorFollower->Avatars"."type" = ${ActorImageType.AVATAR}
+      LEFT JOIN "server" AS "ActorFollow->ActorFollower->Server"
+        ON "ActorFollow->ActorFollower"."serverId" = "ActorFollow->ActorFollower->Server"."id"
+      INNER JOIN "actor" AS "ActorFollow->ActorFollowing" ON "ActorFollow"."targetActorId" = "ActorFollow->ActorFollowing"."id"
+      LEFT JOIN "videoChannel" AS "ActorFollow->ActorFollowing->VideoChannel"
+        ON "ActorFollow->ActorFollowing"."id" = "ActorFollow->ActorFollowing->VideoChannel"."actorId"
+      LEFT JOIN "account" AS "ActorFollow->ActorFollowing->Account"
+        ON "ActorFollow->ActorFollowing"."id" = "ActorFollow->ActorFollowing->Account"."actorId"
+      LEFT JOIN "server" AS "ActorFollow->ActorFollowing->Server"
+        ON "ActorFollow->ActorFollowing"."serverId" = "ActorFollow->ActorFollowing->Server"."id"
+    ) ON "UserNotificationModel"."actorFollowId" = "ActorFollow"."id"
-  LEFT JOIN (
+    LEFT JOIN (
-    "actorFollow" AS "ActorFollow"
+      "account" AS "Account"
-    INNER JOIN "actor" AS "ActorFollow->ActorFollower" ON "ActorFollow"."actorId" = "ActorFollow->ActorFollower"."id"
+      INNER JOIN "actor" AS "Account->Actor" ON "Account"."actorId" = "Account->Actor"."id"
-    INNER JOIN "account" AS "ActorFollow->ActorFollower->Account"
+      LEFT JOIN "actorImage" AS "Account->Actor->Avatars"
-      ON "ActorFollow->ActorFollower"."id" = "ActorFollow->ActorFollower->Account"."actorId"
+        ON "Account->Actor"."id" = "Account->Actor->Avatars"."actorId"
-    LEFT JOIN "actorImage" AS "ActorFollow->ActorFollower->Avatars"
+        AND "Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
-      ON "ActorFollow->ActorFollower"."id" = "ActorFollow->ActorFollower->Avatars"."actorId"
+      LEFT JOIN "server" AS "Account->Actor->Server" ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"
-      AND "ActorFollow->ActorFollower->Avatars"."type" = ${ActorImageType.AVATAR}
+    ) ON "UserNotificationModel"."accountId" = "Account"."id"
-    LEFT JOIN "server" AS "ActorFollow->ActorFollower->Server"
-      ON "ActorFollow->ActorFollower"."serverId" = "ActorFollow->ActorFollower->Server"."id"
-    INNER JOIN "actor" AS "ActorFollow->ActorFollowing" ON "ActorFollow"."targetActorId" = "ActorFollow->ActorFollowing"."id"
-    LEFT JOIN "videoChannel" AS "ActorFollow->ActorFollowing->VideoChannel"
-      ON "ActorFollow->ActorFollowing"."id" = "ActorFollow->ActorFollowing->VideoChannel"."actorId"
-    LEFT JOIN "account" AS "ActorFollow->ActorFollowing->Account"
-      ON "ActorFollow->ActorFollowing"."id" = "ActorFollow->ActorFollowing->Account"."actorId"
-    LEFT JOIN "server" AS "ActorFollow->ActorFollowing->Server"
-      ON "ActorFollow->ActorFollowing"."serverId" = "ActorFollow->ActorFollowing->Server"."id"
-  ) ON "UserNotificationModel"."actorFollowId" = "ActorFollow"."id"
-  LEFT JOIN (
+    LEFT JOIN "userRegistration" as "UserRegistration" ON "UserNotificationModel"."userRegistrationId" = "UserRegistration"."id"`
-    "account" AS "Account"
-    INNER JOIN "actor" AS "Account->Actor" ON "Account"."actorId" = "Account->Actor"."id"
-    LEFT JOIN "actorImage" AS "Account->Actor->Avatars"
-      ON "Account->Actor"."id" = "Account->Actor->Avatars"."actorId"
-      AND "Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
-    LEFT JOIN "server" AS "Account->Actor->Server" ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"
-  ) ON "UserNotificationModel"."accountId" = "Account"."id"`
  }
 }
diff --git a/server/models/user/user-notification-setting.ts b/server/models/user/user-notification-setting.ts
index 66e1d85b3..394494c0c 100644
--- a/server/models/user/user-notification-setting.ts
+++ b/server/models/user/user-notification-setting.ts
@@ -17,7 +17,7 @@ import { MNotificationSettingFormattable } from '@server/types/models'
 import { AttributesOnly } from '@shared/typescript-utils'
 import { UserNotificationSetting, UserNotificationSettingValue } from '../../../shared/models/users/user-notification-setting.model'
 import { isUserNotificationSettingValid } from '../../helpers/custom-validators/user-notifications'
-import { throwIfNotValid } from '../utils'
+import { throwIfNotValid } from '../shared'
 import { UserModel } from './user'
 @Table({
diff --git a/server/models/user/user-notification.ts b/server/models/user/user-notification.ts
index d37fa5dc7..667ee7f5f 100644
--- a/server/models/user/user-notification.ts
+++ b/server/models/user/user-notification.ts
@@ -13,13 +13,14 @@ import { AccountModel } from '../account/account'
 import { ActorFollowModel } from '../actor/actor-follow'
 import { ApplicationModel } from '../application/application'
 import { PluginModel } from '../server/plugin'
-import { throwIfNotValid } from '../utils'
+import { throwIfNotValid } from '../shared'
 import { VideoModel } from '../video/video'
 import { VideoBlacklistModel } from '../video/video-blacklist'
 import { VideoCommentModel } from '../video/video-comment'
 import { VideoImportModel } from '../video/video-import'
 import { UserNotificationListQueryBuilder } from './sql/user-notitication-list-query-builder'
 import { UserModel } from './user'
+import { UserRegistrationModel } from './user-registration'
 @Table({
  tableName: 'userNotification',
@@ -98,6 +99,14 @@ import { UserModel } from './user'
          [Op.ne]: null
        }
      }
+    },
+    {
+      fields: [ 'userRegistrationId' ],
+      where: {
+        userRegistrationId: {
+          [Op.ne]: null
+        }
+      }
    }
  ] as (ModelIndexesOptions & { where?: WhereOptions })[]
 })
@@ -241,6 +250,18 @@ export class UserNotificationModel extends Model<Partial<AttributesOnly<UserNoti
  })
  Application: ApplicationModel
+  @ForeignKey(() => UserRegistrationModel)
+  @Column
+  userRegistrationId: number
+  @BelongsTo(() => UserRegistrationModel, {
+    foreignKey: {
+      allowNull: true
+    },
+    onDelete: 'cascade'
+  })
+  UserRegistration: UserRegistrationModel
  static listForApi (userId: number, start: number, count: number, sort: string, unread?: boolean) {
    const where = { userId }
@@ -416,6 +437,10 @@ export class UserNotificationModel extends Model<Partial<AttributesOnly<UserNoti
      ? { latestVersion: this.Application.latestPeerTubeVersion }
      : undefined
+    const registration = this.UserRegistration
+      ? { id: this.UserRegistration.id, username: this.UserRegistration.username }
+      : undefined
    return {
      id: this.id,
      type: this.type,
@@ -429,6 +454,7 @@ export class UserNotificationModel extends Model<Partial<AttributesOnly<UserNoti
      actorFollow,
      plugin,
      peertube,
+      registration,
      createdAt: this.createdAt.toISOString(),
      updatedAt: this.updatedAt.toISOString()
    }
diff --git a/server/models/user/user-registration.ts b/server/models/user/user-registration.ts
new file mode 100644
index 000000000..adda3cc7e
--- /dev/null
+++ b/server/models/user/user-registration.ts
@@ -0,0 +1,259 @@
+import { FindOptions, Op, WhereOptions } from 'sequelize'
+import {
+  AllowNull,
+  BeforeCreate,
+  BelongsTo,
+  Column,
+  CreatedAt,
+  DataType,
+  ForeignKey,
+  Is,
+  IsEmail,
+  Model,
+  Table,
+  UpdatedAt
+} from 'sequelize-typescript'
+import {
+  isRegistrationModerationResponseValid,
+  isRegistrationReasonValid,
+  isRegistrationStateValid
+} from '@server/helpers/custom-validators/user-registration'
+import { isVideoChannelDisplayNameValid } from '@server/helpers/custom-validators/video-channels'
+import { cryptPassword } from '@server/helpers/peertube-crypto'
+import { USER_REGISTRATION_STATES } from '@server/initializers/constants'
+import { MRegistration, MRegistrationFormattable } from '@server/types/models'
+import { UserRegistration, UserRegistrationState } from '@shared/models'
+import { AttributesOnly } from '@shared/typescript-utils'
+import { isUserDisplayNameValid, isUserEmailVerifiedValid, isUserPasswordValid } from '../../helpers/custom-validators/users'
+import { getSort, throwIfNotValid } from '../shared'
+import { UserModel } from './user'
+@Table({
+  tableName: 'userRegistration',
+  indexes: [
+    {
+      fields: [ 'username' ],
+      unique: true
+    },
+    {
+      fields: [ 'email' ],
+      unique: true
+    },
+    {
+      fields: [ 'channelHandle' ],
+      unique: true
+    },
+    {
+      fields: [ 'userId' ],
+      unique: true
+    }
+  ]
+})
+export class UserRegistrationModel extends Model<Partial<AttributesOnly<UserRegistrationModel>>> {
+  @AllowNull(false)
+  @Is('RegistrationState', value => throwIfNotValid(value, isRegistrationStateValid, 'state'))
+  @Column
+  state: UserRegistrationState
+  @AllowNull(false)
+  @Is('RegistrationReason', value => throwIfNotValid(value, isRegistrationReasonValid, 'registration reason'))
+  @Column(DataType.TEXT)
+  registrationReason: string
+  @AllowNull(true)
+  @Is('RegistrationModerationResponse', value => throwIfNotValid(value, isRegistrationModerationResponseValid, 'moderation response', true))
+  @Column(DataType.TEXT)
+  moderationResponse: string
+  @AllowNull(true)
+  @Is('RegistrationPassword', value => throwIfNotValid(value, isUserPasswordValid, 'registration password', true))
+  @Column
+  password: string
+  @AllowNull(false)
+  @Column
+  username: string
+  @AllowNull(false)
+  @IsEmail
+  @Column(DataType.STRING(400))
+  email: string
+  @AllowNull(true)
+  @Is('RegistrationEmailVerified', value => throwIfNotValid(value, isUserEmailVerifiedValid, 'email verified boolean', true))
+  @Column
+  emailVerified: boolean
+  @AllowNull(true)
+  @Is('RegistrationAccountDisplayName', value => throwIfNotValid(value, isUserDisplayNameValid, 'account display name', true))
+  @Column
+  accountDisplayName: string
+  @AllowNull(true)
+  @Is('ChannelHandle', value => throwIfNotValid(value, isVideoChannelDisplayNameValid, 'channel handle', true))
+  @Column
+  channelHandle: string
+  @AllowNull(true)
+  @Is('ChannelDisplayName', value => throwIfNotValid(value, isVideoChannelDisplayNameValid, 'channel display name', true))
+  @Column
+  channelDisplayName: string
+  @CreatedAt
+  createdAt: Date
+  @UpdatedAt
+  updatedAt: Date
+  @ForeignKey(() => UserModel)
+  @Column
+  userId: number
+  @BelongsTo(() => UserModel, {
+    foreignKey: {
+      allowNull: true
+    },
+    onDelete: 'SET NULL'
+  })
+  User: UserModel
+  @BeforeCreate
+  static async cryptPasswordIfNeeded (instance: UserRegistrationModel) {
+    instance.password = await cryptPassword(instance.password)
+  }
+  static load (id: number): Promise<MRegistration> {
+    return UserRegistrationModel.findByPk(id)
+  }
+  static loadByEmail (email: string): Promise<MRegistration> {
+    const query = {
+      where: { email }
+    }
+    return UserRegistrationModel.findOne(query)
+  }
+  static loadByEmailOrUsername (emailOrUsername: string): Promise<MRegistration> {
+    const query = {
+      where: {
+        [Op.or]: [
+          { email: emailOrUsername },
+          { username: emailOrUsername }
+        ]
+      }
+    }
+    return UserRegistrationModel.findOne(query)
+  }
+  static loadByEmailOrHandle (options: {
+    email: string
+    username: string
+    channelHandle?: string
+  }): Promise<MRegistration> {
+    const { email, username, channelHandle } = options
+    let or: WhereOptions = [
+      { email },
+      { channelHandle: username },
+      { username }
+    ]
+    if (channelHandle) {
+      or = or.concat([
+        { username: channelHandle },
+        { channelHandle }
+      ])
+    }
+    const query = {
+      where: {
+        [Op.or]: or
+      }
+    }
+    return UserRegistrationModel.findOne(query)
+  }
+  // ---------------------------------------------------------------------------
+  static listForApi (options: {
+    start: number
+    count: number
+    sort: string
+    search?: string
+  }) {
+    const { start, count, sort, search } = options
+    const where: WhereOptions = {}
+    if (search) {
+      Object.assign(where, {
+        [Op.or]: [
+          {
+            email: {
+              [Op.iLike]: '%' + search + '%'
+            }
+          },
+          {
+            username: {
+              [Op.iLike]: '%' + search + '%'
+            }
+          }
+        ]
+      })
+    }
+    const query: FindOptions = {
+      offset: start,
+      limit: count,
+      order: getSort(sort),
+      where,
+      include: [
+        {
+          model: UserModel.unscoped(),
+          required: false
+        }
+      ]
+    }
+    return Promise.all([
+      UserRegistrationModel.count(query),
+      UserRegistrationModel.findAll<MRegistrationFormattable>(query)
+    ]).then(([ total, data ]) => ({ total, data }))
+  }
+  // ---------------------------------------------------------------------------
+  toFormattedJSON (this: MRegistrationFormattable): UserRegistration {
+    return {
+      id: this.id,
+      state: {
+        id: this.state,
+        label: USER_REGISTRATION_STATES[this.state]
+      },
+      registrationReason: this.registrationReason,
+      moderationResponse: this.moderationResponse,
+      username: this.username,
+      email: this.email,
+      emailVerified: this.emailVerified,
+      accountDisplayName: this.accountDisplayName,
+      channelHandle: this.channelHandle,
+      channelDisplayName: this.channelDisplayName,
+      createdAt: this.createdAt,
+      updatedAt: this.updatedAt,
+      user: this.User
+        ? { id: this.User.id }
+        : null
+    }
+  }
+}
diff --git a/server/models/user/user.ts b/server/models/user/user.ts
index 3fd808edc..bfc9b3049 100644
--- a/server/models/user/user.ts
+++ b/server/models/user/user.ts
@@ -30,6 +30,7 @@ import {
  MUserNotifSettingChannelDefault,
  MUserWithNotificationSetting
 } from '@server/types/models'
+import { forceNumber } from '@shared/core-utils'
 import { AttributesOnly } from '@shared/typescript-utils'
 import { hasUserRight, USER_ROLE_LABELS } from '../../../shared/core-utils/users'
 import { AbuseState, MyUser, UserRight, VideoPlaylistType } from '../../../shared/models'
@@ -63,14 +64,13 @@ import { ActorModel } from '../actor/actor'
 import { ActorFollowModel } from '../actor/actor-follow'
 import { ActorImageModel } from '../actor/actor-image'
 import { OAuthTokenModel } from '../oauth/oauth-token'
-import { getAdminUsersSort, throwIfNotValid } from '../utils'
+import { getAdminUsersSort, throwIfNotValid } from '../shared'
 import { VideoModel } from '../video/video'
 import { VideoChannelModel } from '../video/video-channel'
 import { VideoImportModel } from '../video/video-import'
 import { VideoLiveModel } from '../video/video-live'
 import { VideoPlaylistModel } from '../video/video-playlist'
 import { UserNotificationSettingModel } from './user-notification-setting'
-import { forceNumber } from '@shared/core-utils'
 enum ScopeNames {
  FOR_ME_API = 'FOR_ME_API',
@@ -441,16 +441,17 @@ export class UserModel extends Model<Partial<AttributesOnly<UserModel>>> {
  })
  OAuthTokens: OAuthTokenModel[]
+  // Used if we already set an encrypted password in user model
+  skipPasswordEncryption = false
  @BeforeCreate
  @BeforeUpdate
-  static cryptPasswordIfNeeded (instance: UserModel) {
+  static async cryptPasswordIfNeeded (instance: UserModel) {
-    if (instance.changed('password') && instance.password) {
+    if (instance.skipPasswordEncryption) return
-      return cryptPassword(instance.password)
+    if (!instance.changed('password')) return
-        .then(hash => {
+    if (!instance.password) return
-          instance.password = hash
-          return undefined
+    instance.password = await cryptPassword(instance.password)
-        })
-    }
  }
  @AfterUpdate
diff --git a/server/models/utils.ts b/server/models/utils.ts
deleted file mode 100644
index 3476799ce..000000000
--- a/server/models/utils.ts
+++ /dev/null
@@ -1,317 +0,0 @@
-import { literal, Op, OrderItem, Sequelize } from 'sequelize'
-import validator from 'validator'
-import { forceNumber } from '@shared/core-utils'
-type SortType = { sortModel: string, sortValue: string }
-// Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ]
-function getSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
-  const { direction, field } = buildDirectionAndField(value)
-  let finalField: string | ReturnType<typeof Sequelize.col>
-  if (field.toLowerCase() === 'match') { // Search
-    finalField = Sequelize.col('similarity')
-  } else {
-    finalField = field
-  }
-  return [ [ finalField, direction ], lastSort ]
-}
-function getAdminUsersSort (value: string): OrderItem[] {
-  const { direction, field } = buildDirectionAndField(value)
-  let finalField: string | ReturnType<typeof Sequelize.col>
-  if (field === 'videoQuotaUsed') { // Users list
-    finalField = Sequelize.col('videoQuotaUsed')
-  } else {
-    finalField = field
-  }
-  const nullPolicy = direction === 'ASC'
-    ? 'NULLS FIRST'
-    : 'NULLS LAST'
-  // FIXME: typings
-  return [ [ finalField as any, direction, nullPolicy ], [ 'id', 'ASC' ] ]
-}
-function getPlaylistSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
-  const { direction, field } = buildDirectionAndField(value)
-  if (field.toLowerCase() === 'name') {
-    return [ [ 'displayName', direction ], lastSort ]
-  }
-  return getSort(value, lastSort)
-}
-function getCommentSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
-  const { direction, field } = buildDirectionAndField(value)
-  if (field === 'totalReplies') {
-    return [
-      [ Sequelize.literal('"totalReplies"'), direction ],
-      lastSort
-    ]
-  }
-  return getSort(value, lastSort)
-}
-function getVideoSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
-  const { direction, field } = buildDirectionAndField(value)
-  if (field.toLowerCase() === 'trending') { // Sort by aggregation
-    return [
-      [ Sequelize.fn('COALESCE', Sequelize.fn('SUM', Sequelize.col('VideoViews.views')), '0'), direction ],
-      [ Sequelize.col('VideoModel.views'), direction ],
-      lastSort
-    ]
-  } else if (field === 'publishedAt') {
-    return [
-      [ 'ScheduleVideoUpdate', 'updateAt', direction + ' NULLS LAST' ],
-      [ Sequelize.col('VideoModel.publishedAt'), direction ],
-      lastSort
-    ]
-  }
-  let finalField: string | ReturnType<typeof Sequelize.col>
-  // Alias
-  if (field.toLowerCase() === 'match') { // Search
-    finalField = Sequelize.col('similarity')
-  } else {
-    finalField = field
-  }
-  const firstSort: OrderItem = typeof finalField === 'string'
-    ? finalField.split('.').concat([ direction ]) as OrderItem
-    : [ finalField, direction ]
-  return [ firstSort, lastSort ]
-}
-function getBlacklistSort (model: any, value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
-  const [ firstSort ] = getSort(value)
-  if (model) return [ [ literal(`"${model}.${firstSort[0]}" ${firstSort[1]}`) ], lastSort ] as OrderItem[]
-  return [ firstSort, lastSort ]
-}
-function getInstanceFollowsSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
-  const { direction, field } = buildDirectionAndField(value)
-  if (field === 'redundancyAllowed') {
-    return [
-      [ 'ActorFollowing.Server.redundancyAllowed', direction ],
-      lastSort
-    ]
-  }
-  return getSort(value, lastSort)
-}
-function getChannelSyncSort (value: string): OrderItem[] {
-  const { direction, field } = buildDirectionAndField(value)
-  if (field.toLowerCase() === 'videochannel') {
-    return [
-      [ literal('"VideoChannel.name"'), direction ]
-    ]
-  }
-  return [ [ field, direction ] ]
-}
-function isOutdated (model: { createdAt: Date, updatedAt: Date }, refreshInterval: number) {
-  if (!model.createdAt || !model.updatedAt) {
-    throw new Error('Miss createdAt & updatedAt attributes to model')
-  }
-  const now = Date.now()
-  const createdAtTime = model.createdAt.getTime()
-  const updatedAtTime = model.updatedAt.getTime()
-  return (now - createdAtTime) > refreshInterval && (now - updatedAtTime) > refreshInterval
-}
-function throwIfNotValid (value: any, validator: (value: any) => boolean, fieldName = 'value', nullable = false) {
-  if (nullable && (value === null || value === undefined)) return
-  if (validator(value) === false) {
-    throw new Error(`"${value}" is not a valid ${fieldName}.`)
-  }
-}
-function buildTrigramSearchIndex (indexName: string, attribute: string) {
-  return {
-    name: indexName,
-    // FIXME: gin_trgm_ops is not taken into account in Sequelize 6, so adding it ourselves in the literal function
-    fields: [ Sequelize.literal('lower(immutable_unaccent(' + attribute + ')) gin_trgm_ops') as any ],
-    using: 'gin',
-    operator: 'gin_trgm_ops'
-  }
-}
-function createSimilarityAttribute (col: string, value: string) {
-  return Sequelize.fn(
-    'similarity',
-    searchTrigramNormalizeCol(col),
-    searchTrigramNormalizeValue(value)
-  )
-}
-function buildBlockedAccountSQL (blockerIds: number[]) {
-  const blockerIdsString = blockerIds.join(', ')
-  return 'SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (' + blockerIdsString + ')' +
-    ' UNION ' +
-    'SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id ' +
-    'INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ' +
-    'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')'
-}
-function buildBlockedAccountSQLOptimized (columnNameJoin: string, blockerIds: number[]) {
-  const blockerIdsString = blockerIds.join(', ')
-  return [
-    literal(
-      `NOT EXISTS (` +
-      `  SELECT 1 FROM "accountBlocklist" ` +
-      `  WHERE "targetAccountId" = ${columnNameJoin} ` +
-      `  AND "accountId" IN (${blockerIdsString})` +
-      `)`
-    ),
-    literal(
-      `NOT EXISTS (` +
-      `  SELECT 1 FROM "account" ` +
-      `  INNER JOIN "actor" ON account."actorId" = actor.id ` +
-      `  INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ` +
-      `  WHERE "account"."id" = ${columnNameJoin} ` +
-      `  AND "serverBlocklist"."accountId" IN (${blockerIdsString})` +
-      `)`
-    )
-  ]
-}
-function buildServerIdsFollowedBy (actorId: any) {
-  const actorIdNumber = forceNumber(actorId)
-  return '(' +
-    'SELECT "actor"."serverId" FROM "actorFollow" ' +
-    'INNER JOIN "actor" ON actor.id = "actorFollow"."targetActorId" ' +
-    'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
-  ')'
-}
-function buildWhereIdOrUUID (id: number | string) {
-  return validator.isInt('' + id) ? { id } : { uuid: id }
-}
-function parseAggregateResult (result: any) {
-  if (!result) return 0
-  const total = forceNumber(result)
-  if (isNaN(total)) return 0
-  return total
-}
-function parseRowCountResult (result: any) {
-  if (result.length !== 0) return result[0].total
-  return 0
-}
-function createSafeIn (sequelize: Sequelize, stringArr: (string | number)[]) {
-  return stringArr.map(t => {
-    return t === null
-      ? null
-      : sequelize.escape('' + t)
-  }).join(', ')
-}
-function buildLocalAccountIdsIn () {
-  return literal(
-    '(SELECT "account"."id" FROM "account" INNER JOIN "actor" ON "actor"."id" = "account"."actorId" AND "actor"."serverId" IS NULL)'
-  )
-}
-function buildLocalActorIdsIn () {
-  return literal(
-    '(SELECT "actor"."id" FROM "actor" WHERE "actor"."serverId" IS NULL)'
-  )
-}
-function buildDirectionAndField (value: string) {
-  let field: string
-  let direction: 'ASC' | 'DESC'
-  if (value.substring(0, 1) === '-') {
-    direction = 'DESC'
-    field = value.substring(1)
-  } else {
-    direction = 'ASC'
-    field = value
-  }
-  return { direction, field }
-}
-function searchAttribute (sourceField?: string, targetField?: string) {
-  if (!sourceField) return {}
-  return {
-    [targetField]: {
-      // FIXME: ts error
-      [Op.iLike as any]: `%${sourceField}%`
-    }
-  }
-}
-// ---------------------------------------------------------------------------
-export {
-  buildBlockedAccountSQL,
-  buildBlockedAccountSQLOptimized,
-  buildLocalActorIdsIn,
-  getPlaylistSort,
-  SortType,
-  buildLocalAccountIdsIn,
-  getSort,
-  getCommentSort,
-  getAdminUsersSort,
-  getVideoSort,
-  getBlacklistSort,
-  getChannelSyncSort,
-  createSimilarityAttribute,
-  throwIfNotValid,
-  buildServerIdsFollowedBy,
-  buildTrigramSearchIndex,
-  buildWhereIdOrUUID,
-  isOutdated,
-  parseAggregateResult,
-  getInstanceFollowsSort,
-  buildDirectionAndField,
-  createSafeIn,
-  searchAttribute,
-  parseRowCountResult
-}
-// ---------------------------------------------------------------------------
-function searchTrigramNormalizeValue (value: string) {
-  return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value))
-}
-function searchTrigramNormalizeCol (col: string) {
-  return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
-}
diff --git a/server/models/video/formatter/video-format-utils.ts b/server/models/video/formatter/video-format-utils.ts
index f285db477..6f05dbdc8 100644
--- a/server/models/video/formatter/video-format-utils.ts
+++ b/server/models/video/formatter/video-format-utils.ts
@@ -488,7 +488,7 @@ function videoModelToActivityPubObject (video: MVideoAP): VideoObject {
 }
 function getCategoryLabel (id: number) {
-  return VIDEO_CATEGORIES[id] || 'Misc'
+  return VIDEO_CATEGORIES[id] || 'Unknown'
 }
 function getLicenceLabel (id: number) {
diff --git a/server/models/video/sql/comment/video-comment-list-query-builder.ts b/server/models/video/sql/comment/video-comment-list-query-builder.ts
new file mode 100644
index 000000000..a7eed22a1
--- /dev/null
+++ b/server/models/video/sql/comment/video-comment-list-query-builder.ts
@@ -0,0 +1,400 @@
+import { Model, Sequelize, Transaction } from 'sequelize'
+import { AbstractRunQuery, ModelBuilder } from '@server/models/shared'
+import { ActorImageType, VideoPrivacy } from '@shared/models'
+import { createSafeIn, getSort, parseRowCountResult } from '../../../shared'
+import { VideoCommentTableAttributes } from './video-comment-table-attributes'
+export interface ListVideoCommentsOptions {
+  selectType: 'api' | 'feed' | 'comment-only'
+  start?: number
+  count?: number
+  sort?: string
+  videoId?: number
+  threadId?: number
+  accountId?: number
+  videoChannelId?: number
+  blockerAccountIds?: number[]
+  isThread?: boolean
+  notDeleted?: boolean
+  isLocal?: boolean
+  onLocalVideo?: boolean
+  onPublicVideo?: boolean
+  videoAccountOwnerId?: boolean
+  search?: string
+  searchAccount?: string
+  searchVideo?: string
+  includeReplyCounters?: boolean
+  transaction?: Transaction
+}
+export class VideoCommentListQueryBuilder extends AbstractRunQuery {
+  private readonly tableAttributes = new VideoCommentTableAttributes()
+  private innerQuery: string
+  private select = ''
+  private joins = ''
+  private innerSelect = ''
+  private innerJoins = ''
+  private innerLateralJoins = ''
+  private innerWhere = ''
+  private readonly built = {
+    cte: false,
+    accountJoin: false,
+    videoJoin: false,
+    videoChannelJoin: false,
+    avatarJoin: false
+  }
+  constructor (
+    protected readonly sequelize: Sequelize,
+    private readonly options: ListVideoCommentsOptions
+  ) {
+    super(sequelize)
+    if (this.options.includeReplyCounters && !this.options.videoId) {
+      throw new Error('Cannot include reply counters without videoId')
+    }
+  }
+  async listComments <T extends Model> () {
+    this.buildListQuery()
+    const results = await this.runQuery({ nest: true, transaction: this.options.transaction })
+    const modelBuilder = new ModelBuilder<T>(this.sequelize)
+    return modelBuilder.createModels(results, 'VideoComment')
+  }
+  async countComments () {
+    this.buildCountQuery()
+    const result = await this.runQuery({ transaction: this.options.transaction })
+    return parseRowCountResult(result)
+  }
+  // ---------------------------------------------------------------------------
+  private buildListQuery () {
+    this.buildInnerListQuery()
+    this.buildListSelect()
+    this.query = `${this.select} ` +
+      `FROM (${this.innerQuery}) AS "VideoCommentModel" ` +
+      `${this.joins} ` +
+      `${this.getOrder()}`
+  }
+  private buildInnerListQuery () {
+    this.buildWhere()
+    this.buildInnerListSelect()
+    this.innerQuery = `${this.innerSelect} ` +
+      `FROM "videoComment" AS "VideoCommentModel" ` +
+      `${this.innerJoins} ` +
+      `${this.innerLateralJoins} ` +
+      `${this.innerWhere} ` +
+      `${this.getOrder()} ` +
+      `${this.getInnerLimit()}`
+  }
+  // ---------------------------------------------------------------------------
+  private buildCountQuery () {
+    this.buildWhere()
+    this.query = `SELECT COUNT(*) AS "total" ` +
+      `FROM "videoComment" AS "VideoCommentModel" ` +
+      `${this.innerJoins} ` +
+      `${this.innerWhere}`
+  }
+  // ---------------------------------------------------------------------------
+  private buildWhere () {
+    let where: string[] = []
+    if (this.options.videoId) {
+      this.replacements.videoId = this.options.videoId
+      where.push('"VideoCommentModel"."videoId" = :videoId')
+    }
+    if (this.options.threadId) {
+      this.replacements.threadId = this.options.threadId
+      where.push('("VideoCommentModel"."id" = :threadId OR "VideoCommentModel"."originCommentId" = :threadId)')
+    }
+    if (this.options.accountId) {
+      this.replacements.accountId = this.options.accountId
+      where.push('"VideoCommentModel"."accountId" = :accountId')
+    }
+    if (this.options.videoChannelId) {
+      this.buildVideoChannelJoin()
+      this.replacements.videoChannelId = this.options.videoChannelId
+      where.push('"Account->VideoChannel"."id" = :videoChannelId')
+    }
+    if (this.options.blockerAccountIds) {
+      this.buildVideoChannelJoin()
+      where = where.concat(this.getBlockWhere('VideoCommentModel', 'Video->VideoChannel'))
+    }
+    if (this.options.isThread === true) {
+      where.push('"VideoCommentModel"."inReplyToCommentId" IS NULL')
+    }
+    if (this.options.notDeleted === true) {
+      where.push('"VideoCommentModel"."deletedAt" IS NULL')
+    }
+    if (this.options.isLocal === true) {
+      this.buildAccountJoin()
+      where.push('"Account->Actor"."serverId" IS NULL')
+    } else if (this.options.isLocal === false) {
+      this.buildAccountJoin()
+      where.push('"Account->Actor"."serverId" IS NOT NULL')
+    }
+    if (this.options.onLocalVideo === true) {
+      this.buildVideoJoin()
+      where.push('"Video"."remote" IS FALSE')
+    } else if (this.options.onLocalVideo === false) {
+      this.buildVideoJoin()
+      where.push('"Video"."remote" IS TRUE')
+    }
+    if (this.options.onPublicVideo === true) {
+      this.buildVideoJoin()
+      where.push(`"Video"."privacy" = ${VideoPrivacy.PUBLIC}`)
+    }
+    if (this.options.videoAccountOwnerId) {
+      this.buildVideoChannelJoin()
+      this.replacements.videoAccountOwnerId = this.options.videoAccountOwnerId
+      where.push(`"Video->VideoChannel"."accountId" = :videoAccountOwnerId`)
+    }
+    if (this.options.search) {
+      this.buildVideoJoin()
+      this.buildAccountJoin()
+      const escapedLikeSearch = this.sequelize.escape('%' + this.options.search + '%')
+      where.push(
+        `(` +
+          `"VideoCommentModel"."text" ILIKE ${escapedLikeSearch} OR ` +
+          `"Account->Actor"."preferredUsername" ILIKE ${escapedLikeSearch} OR ` +
+          `"Account"."name" ILIKE ${escapedLikeSearch} OR ` +
+          `"Video"."name" ILIKE ${escapedLikeSearch} ` +
+        `)`
+      )
+    }
+    if (this.options.searchAccount) {
+      this.buildAccountJoin()
+      const escapedLikeSearch = this.sequelize.escape('%' + this.options.searchAccount + '%')
+      where.push(
+        `(` +
+          `"Account->Actor"."preferredUsername" ILIKE ${escapedLikeSearch} OR ` +
+          `"Account"."name" ILIKE ${escapedLikeSearch} ` +
+        `)`
+      )
+    }
+    if (this.options.searchVideo) {
+      this.buildVideoJoin()
+      const escapedLikeSearch = this.sequelize.escape('%' + this.options.searchVideo + '%')
+      where.push(`"Video"."name" ILIKE ${escapedLikeSearch}`)
+    }
+    if (where.length !== 0) {
+      this.innerWhere = `WHERE ${where.join(' AND ')}`
+    }
+  }
+  private buildAccountJoin () {
+    if (this.built.accountJoin) return
+    this.innerJoins += ' LEFT JOIN "account" "Account" ON "Account"."id" = "VideoCommentModel"."accountId" ' +
+      'LEFT JOIN "actor" "Account->Actor" ON "Account->Actor"."id" = "Account"."actorId" ' +
+      'LEFT JOIN "server" "Account->Actor->Server" ON "Account->Actor"."serverId" = "Account->Actor->Server"."id" '
+    this.built.accountJoin = true
+  }
+  private buildVideoJoin () {
+    if (this.built.videoJoin) return
+    this.innerJoins += ' LEFT JOIN "video" "Video" ON "Video"."id" = "VideoCommentModel"."videoId" '
+    this.built.videoJoin = true
+  }
+  private buildVideoChannelJoin () {
+    if (this.built.videoChannelJoin) return
+    this.buildVideoJoin()
+    this.innerJoins += ' LEFT JOIN "videoChannel" "Video->VideoChannel" ON "Video"."channelId" = "Video->VideoChannel"."id" '
+    this.built.videoChannelJoin = true
+  }
+  private buildAvatarsJoin () {
+    if (this.options.selectType !== 'api' && this.options.selectType !== 'feed') return ''
+    if (this.built.avatarJoin) return
+    this.joins += `LEFT JOIN "actorImage" "Account->Actor->Avatars" ` +
+      `ON "VideoCommentModel"."Account.Actor.id" = "Account->Actor->Avatars"."actorId" ` +
+        `AND "Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}`
+    this.built.avatarJoin = true
+  }
+  // ---------------------------------------------------------------------------
+  private buildListSelect () {
+    const toSelect = [ '"VideoCommentModel".*' ]
+    if (this.options.selectType === 'api' || this.options.selectType === 'feed') {
+      this.buildAvatarsJoin()
+      toSelect.push(this.tableAttributes.getAvatarAttributes())
+    }
+    this.select = this.buildSelect(toSelect)
+  }
+  private buildInnerListSelect () {
+    let toSelect = [ this.tableAttributes.getVideoCommentAttributes() ]
+    if (this.options.selectType === 'api' || this.options.selectType === 'feed') {
+      this.buildAccountJoin()
+      this.buildVideoJoin()
+      toSelect = toSelect.concat([
+        this.tableAttributes.getVideoAttributes(),
+        this.tableAttributes.getAccountAttributes(),
+        this.tableAttributes.getActorAttributes(),
+        this.tableAttributes.getServerAttributes()
+      ])
+    }
+    if (this.options.includeReplyCounters === true) {
+      this.buildTotalRepliesSelect()
+      this.buildAuthorTotalRepliesSelect()
+      toSelect.push('"totalRepliesFromVideoAuthor"."count" AS "totalRepliesFromVideoAuthor"')
+      toSelect.push('"totalReplies"."count" AS "totalReplies"')
+    }
+    this.innerSelect = this.buildSelect(toSelect)
+  }
+  // ---------------------------------------------------------------------------
+  private getBlockWhere (commentTableName: string, channelTableName: string) {
+    const where: string[] = []
+    const blockerIdsString = createSafeIn(
+      this.sequelize,
+      this.options.blockerAccountIds,
+      [ `"${channelTableName}"."accountId"` ]
+    )
+    where.push(
+      `NOT EXISTS (` +
+        `SELECT 1 FROM "accountBlocklist" ` +
+        `WHERE "targetAccountId" = "${commentTableName}"."accountId" ` +
+        `AND "accountId" IN (${blockerIdsString})` +
+      `)`
+    )
+    where.push(
+      `NOT EXISTS (` +
+        `SELECT 1 FROM "account" ` +
+        `INNER JOIN "actor" ON account."actorId" = actor.id ` +
+        `INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ` +
+        `WHERE "account"."id" = "${commentTableName}"."accountId" ` +
+        `AND "serverBlocklist"."accountId" IN (${blockerIdsString})` +
+      `)`
+    )
+    return where
+  }
+  // ---------------------------------------------------------------------------
+  private buildTotalRepliesSelect () {
+    const blockWhereString = this.getBlockWhere('replies', 'videoChannel').join(' AND ')
+    // Help the planner by providing videoId that should filter out many comments
+    this.replacements.videoId = this.options.videoId
+    this.innerLateralJoins += `LEFT JOIN LATERAL (` +
+      `SELECT COUNT("replies"."id") AS "count" FROM "videoComment" AS "replies" ` +
+      `INNER JOIN "video" ON "video"."id" = "replies"."videoId" AND "replies"."videoId" = :videoId ` +
+      `LEFT JOIN "videoChannel" ON "video"."channelId" = "videoChannel"."id" ` +
+      `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ` +
+        `AND "deletedAt" IS NULL ` +
+        `AND ${blockWhereString} ` +
+    `) "totalReplies" ON TRUE `
+  }
+  private buildAuthorTotalRepliesSelect () {
+    // Help the planner by providing videoId that should filter out many comments
+    this.replacements.videoId = this.options.videoId
+    this.innerLateralJoins += `LEFT JOIN LATERAL (` +
+      `SELECT COUNT("replies"."id") AS "count" FROM "videoComment" AS "replies" ` +
+      `INNER JOIN "video" ON "video"."id" = "replies"."videoId" AND "replies"."videoId" = :videoId ` +
+      `INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ` +
+      `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" AND "replies"."accountId" = "videoChannel"."accountId"` +
+    `) "totalRepliesFromVideoAuthor" ON TRUE `
+  }
+  private getOrder () {
+    if (!this.options.sort) return ''
+    const orders = getSort(this.options.sort)
+    return 'ORDER BY ' + orders.map(o => `"${o[0]}" ${o[1]}`).join(', ')
+  }
+  private getInnerLimit () {
+    if (!this.options.count) return ''
+    this.replacements.limit = this.options.count
+    this.replacements.offset = this.options.start || 0
+    return `LIMIT :limit OFFSET :offset `
+  }
+}
diff --git a/server/models/video/sql/comment/video-comment-table-attributes.ts b/server/models/video/sql/comment/video-comment-table-attributes.ts
new file mode 100644
index 000000000..87f8750c1
--- /dev/null
+++ b/server/models/video/sql/comment/video-comment-table-attributes.ts
@@ -0,0 +1,43 @@
+import { Memoize } from '@server/helpers/memoize'
+import { AccountModel } from '@server/models/account/account'
+import { ActorModel } from '@server/models/actor/actor'
+import { ActorImageModel } from '@server/models/actor/actor-image'
+import { ServerModel } from '@server/models/server/server'
+import { VideoCommentModel } from '../../video-comment'
+export class VideoCommentTableAttributes {
+  @Memoize()
+  getVideoCommentAttributes () {
+    return VideoCommentModel.getSQLAttributes('VideoCommentModel').join(', ')
+  }
+  @Memoize()
+  getAccountAttributes () {
+    return AccountModel.getSQLAttributes('Account', 'Account.').join(', ')
+  }
+  @Memoize()
+  getVideoAttributes () {
+    return [
+      `"Video"."id" AS "Video.id"`,
+      `"Video"."uuid" AS "Video.uuid"`,
+      `"Video"."name" AS "Video.name"`
+    ].join(', ')
+  }
+  @Memoize()
+  getActorAttributes () {
+    return ActorModel.getSQLAPIAttributes('Account->Actor', `Account.Actor.`).join(', ')
+  }
+  @Memoize()
+  getServerAttributes () {
+    return ServerModel.getSQLAttributes('Account->Actor->Server', `Account.Actor.Server.`).join(', ')
+  }
+  @Memoize()
+  getAvatarAttributes () {
+    return ActorImageModel.getSQLAttributes('Account->Actor->Avatars', 'Account.Actor.Avatars.').join(', ')
+  }
+}
diff --git a/server/models/video/sql/video/shared/abstract-video-query-builder.ts b/server/models/video/sql/video/shared/abstract-video-query-builder.ts
index f0ce69501..cbd57ad8c 100644
--- a/server/models/video/sql/video/shared/abstract-video-query-builder.ts
+++ b/server/models/video/sql/video/shared/abstract-video-query-builder.ts
@@ -1,9 +1,9 @@
 import { Sequelize } from 'sequelize'
 import validator from 'validator'
-import { createSafeIn } from '@server/models/utils'
 import { MUserAccountId } from '@server/types/models'
 import { ActorImageType } from '@shared/models'
 import { AbstractRunQuery } from '../../../../shared/abstract-run-query'
+import { createSafeIn } from '../../../../shared'
 import { VideoTableAttributes } from './video-table-attributes'
 /**
diff --git a/server/models/video/sql/video/videos-id-list-query-builder.ts b/server/models/video/sql/video/videos-id-list-query-builder.ts
index 7c864bf27..62f1855c7 100644
--- a/server/models/video/sql/video/videos-id-list-query-builder.ts
+++ b/server/models/video/sql/video/videos-id-list-query-builder.ts
@@ -2,11 +2,12 @@ import { Sequelize, Transaction } from 'sequelize'
 import validator from 'validator'
 import { exists } from '@server/helpers/custom-validators/misc'
 import { WEBSERVER } from '@server/initializers/constants'
-import { buildDirectionAndField, createSafeIn, parseRowCountResult } from '@server/models/utils'
+import { buildSortDirectionAndField } from '@server/models/shared'
 import { MUserAccountId, MUserId } from '@server/types/models'
+import { forceNumber } from '@shared/core-utils'
 import { VideoInclude, VideoPrivacy, VideoState } from '@shared/models'
+import { createSafeIn, parseRowCountResult } from '../../../shared'
 import { AbstractRunQuery } from '../../../shared/abstract-run-query'
-import { forceNumber } from '@shared/core-utils'
 /**
 *
@@ -665,7 +666,7 @@ export class VideosIdListQueryBuilder extends AbstractRunQuery {
  }
  private buildOrder (value: string) {
-    const { direction, field } = buildDirectionAndField(value)
+    const { direction, field } = buildSortDirectionAndField(value)
    if (field.match(/^[a-zA-Z."]+$/) === null) throw new Error('Invalid sort column ' + field)
    if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()'
diff --git a/server/models/video/tag.ts b/server/models/video/tag.ts
index 653b9694b..cebde3755 100644
--- a/server/models/video/tag.ts
+++ b/server/models/video/tag.ts
@@ -4,7 +4,7 @@ import { MTag } from '@server/types/models'
 import { AttributesOnly } from '@shared/typescript-utils'
 import { VideoPrivacy, VideoState } from '../../../shared/models/videos'
 import { isVideoTagValid } from '../../helpers/custom-validators/videos'
-import { throwIfNotValid } from '../utils'
+import { throwIfNotValid } from '../shared'
 import { VideoModel } from './video'
 import { VideoTagModel } from './video-tag'
diff --git a/server/models/video/video-blacklist.ts b/server/models/video/video-blacklist.ts
index 1cd8224c0..9247d0e2b 100644
--- a/server/models/video/video-blacklist.ts
+++ b/server/models/video/video-blacklist.ts
@@ -5,7 +5,7 @@ import { AttributesOnly } from '@shared/typescript-utils'
 import { VideoBlacklist, VideoBlacklistType } from '../../../shared/models/videos'
 import { isVideoBlacklistReasonValid, isVideoBlacklistTypeValid } from '../../helpers/custom-validators/video-blacklist'
 import { CONSTRAINTS_FIELDS } from '../../initializers/constants'
-import { getBlacklistSort, searchAttribute, SortType, throwIfNotValid } from '../utils'
+import { getBlacklistSort, searchAttribute, throwIfNotValid } from '../shared'
 import { ThumbnailModel } from './thumbnail'
 import { VideoModel } from './video'
 import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
@@ -57,7 +57,7 @@ export class VideoBlacklistModel extends Model<Partial<AttributesOnly<VideoBlack
  static listForApi (parameters: {
    start: number
    count: number
-    sort: SortType
+    sort: string
    search?: string
    type?: VideoBlacklistType
  }) {
@@ -67,7 +67,7 @@ export class VideoBlacklistModel extends Model<Partial<AttributesOnly<VideoBlack
      return {
        offset: start,
        limit: count,
-        order: getBlacklistSort(sort.sortModel, sort.sortValue)
+        order: getBlacklistSort(sort)
      }
    }
diff --git a/server/models/video/video-caption.ts b/server/models/video/video-caption.ts
index 5fbcd6e3b..2eaa77407 100644
--- a/server/models/video/video-caption.ts
+++ b/server/models/video/video-caption.ts
@@ -23,7 +23,7 @@ import { isVideoCaptionLanguageValid } from '../../helpers/custom-validators/vid
 import { logger } from '../../helpers/logger'
 import { CONFIG } from '../../initializers/config'
 import { CONSTRAINTS_FIELDS, LAZY_STATIC_PATHS, VIDEO_LANGUAGES, WEBSERVER } from '../../initializers/constants'
-import { buildWhereIdOrUUID, throwIfNotValid } from '../utils'
+import { buildWhereIdOrUUID, throwIfNotValid } from '../shared'
 import { VideoModel } from './video'
 export enum ScopeNames {
diff --git a/server/models/video/video-change-ownership.ts b/server/models/video/video-change-ownership.ts
index 1a1b8c88d..2db4b523a 100644
--- a/server/models/video/video-change-ownership.ts
+++ b/server/models/video/video-change-ownership.ts
@@ -3,7 +3,7 @@ import { MVideoChangeOwnershipFormattable, MVideoChangeOwnershipFull } from '@se
 import { AttributesOnly } from '@shared/typescript-utils'
 import { VideoChangeOwnership, VideoChangeOwnershipStatus } from '../../../shared/models/videos'
 import { AccountModel } from '../account/account'
-import { getSort } from '../utils'
+import { getSort } from '../shared'
 import { ScopeNames as VideoScopeNames, VideoModel } from './video'
 enum ScopeNames {
diff --git a/server/models/video/video-channel-sync.ts b/server/models/video/video-channel-sync.ts
index 6e49cde10..a4cbf51f5 100644
--- a/server/models/video/video-channel-sync.ts
+++ b/server/models/video/video-channel-sync.ts
@@ -21,7 +21,7 @@ import { VideoChannelSync, VideoChannelSyncState } from '@shared/models'
 import { AttributesOnly } from '@shared/typescript-utils'
 import { AccountModel } from '../account/account'
 import { UserModel } from '../user/user'
-import { getChannelSyncSort, throwIfNotValid } from '../utils'
+import { getChannelSyncSort, throwIfNotValid } from '../shared'
 import { VideoChannelModel } from './video-channel'
 @DefaultScope(() => ({
diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 132c8f021..b71f5a197 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -43,8 +43,14 @@ import { ActorModel, unusedActorAttributesForAPI } from '../actor/actor'
 import { ActorFollowModel } from '../actor/actor-follow'
 import { ActorImageModel } from '../actor/actor-image'
 import { ServerModel } from '../server/server'
-import { setAsUpdated } from '../shared'
+import {
-import { buildServerIdsFollowedBy, buildTrigramSearchIndex, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils'
+  buildServerIdsFollowedBy,
+  buildTrigramSearchIndex,
+  createSimilarityAttribute,
+  getSort,
+  setAsUpdated,
+  throwIfNotValid
+} from '../shared'
 import { VideoModel } from './video'
 import { VideoPlaylistModel } from './video-playlist'
@@ -831,6 +837,6 @@ export class VideoChannelModel extends Model<Partial<AttributesOnly<VideoChannel
  }
  setAsUpdated (transaction?: Transaction) {
-    return setAsUpdated('videoChannel', this.id, transaction)
+    return setAsUpdated({ sequelize: this.sequelize, table: 'videoChannel', id: this.id, transaction })
  }
 }
diff --git a/server/models/video/video-comment.ts b/server/models/video/video-comment.ts
index af9614d30..ff5142809 100644
--- a/server/models/video/video-comment.ts
+++ b/server/models/video/video-comment.ts
@@ -1,4 +1,4 @@
-import { FindOptions, Op, Order, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize'
+import { FindOptions, Op, Order, QueryTypes, Sequelize, Transaction } from 'sequelize'
 import {
  AllowNull,
  BelongsTo,
@@ -13,11 +13,9 @@ import {
  Table,
  UpdatedAt
 } from 'sequelize-typescript'
-import { exists } from '@server/helpers/custom-validators/misc'
 import { getServerActor } from '@server/models/application/application'
 import { MAccount, MAccountId, MUserAccountId } from '@server/types/models'
-import { uniqify } from '@shared/core-utils'
+import { pick, uniqify } from '@shared/core-utils'
-import { VideoPrivacy } from '@shared/models'
 import { AttributesOnly } from '@shared/typescript-utils'
 import { ActivityTagObject, ActivityTombstoneObject } from '../../../shared/models/activitypub/objects/common-objects'
 import { VideoCommentObject } from '../../../shared/models/activitypub/objects/video-comment-object'
@@ -41,61 +39,19 @@ import {
 } from '../../types/models/video'
 import { VideoCommentAbuseModel } from '../abuse/video-comment-abuse'
 import { AccountModel } from '../account/account'
-import { ActorModel, unusedActorAttributesForAPI } from '../actor/actor'
+import { ActorModel } from '../actor/actor'
-import {
+import { buildLocalAccountIdsIn, buildSQLAttributes, throwIfNotValid } from '../shared'
-  buildBlockedAccountSQL,
+import { ListVideoCommentsOptions, VideoCommentListQueryBuilder } from './sql/comment/video-comment-list-query-builder'
-  buildBlockedAccountSQLOptimized,
-  buildLocalAccountIdsIn,
-  getCommentSort,
-  searchAttribute,
-  throwIfNotValid
-} from '../utils'
 import { VideoModel } from './video'
 import { VideoChannelModel } from './video-channel'
 export enum ScopeNames {
  WITH_ACCOUNT = 'WITH_ACCOUNT',
-  WITH_ACCOUNT_FOR_API = 'WITH_ACCOUNT_FOR_API',
  WITH_IN_REPLY_TO = 'WITH_IN_REPLY_TO',
-  WITH_VIDEO = 'WITH_VIDEO',
+  WITH_VIDEO = 'WITH_VIDEO'
-  ATTRIBUTES_FOR_API = 'ATTRIBUTES_FOR_API'
 }
 @Scopes(() => ({
-  [ScopeNames.ATTRIBUTES_FOR_API]: (blockerAccountIds: number[]) => {
-    return {
-      attributes: {
-        include: [
-          [
-            Sequelize.literal(
-              '(' +
-                'WITH "blocklist" AS (' + buildBlockedAccountSQL(blockerAccountIds) + ')' +
-                'SELECT COUNT("replies"."id") ' +
-                'FROM "videoComment" AS "replies" ' +
-                'WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ' +
-                'AND "deletedAt" IS NULL ' +
-                'AND "accountId" NOT IN (SELECT "id" FROM "blocklist")' +
-              ')'
-            ),
-            'totalReplies'
-          ],
-          [
-            Sequelize.literal(
-              '(' +
-                'SELECT COUNT("replies"."id") ' +
-                'FROM "videoComment" AS "replies" ' +
-                'INNER JOIN "video" ON "video"."id" = "replies"."videoId" ' +
-                'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
-                'WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ' +
-                'AND "replies"."accountId" = "videoChannel"."accountId"' +
-              ')'
-            ),
-            'totalRepliesFromVideoAuthor'
-          ]
-        ]
-      }
-    } as FindOptions
-  },
  [ScopeNames.WITH_ACCOUNT]: {
    include: [
      {
@@ -103,22 +59,6 @@ export enum ScopeNames {
      }
    ]
  },
-  [ScopeNames.WITH_ACCOUNT_FOR_API]: {
-    include: [
-      {
-        model: AccountModel.unscoped(),
-        include: [
-          {
-            attributes: {
-              exclude: unusedActorAttributesForAPI
-            },
-            model: ActorModel, // Default scope includes avatar and server
-            required: true
-          }
-        ]
-      }
-    ]
-  },
  [ScopeNames.WITH_IN_REPLY_TO]: {
    include: [
      {
@@ -252,6 +192,18 @@ export class VideoCommentModel extends Model<Partial<AttributesOnly<VideoComment
  })
  CommentAbuses: VideoCommentAbuseModel[]
+  // ---------------------------------------------------------------------------
+  static getSQLAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix
+    })
+  }
+  // ---------------------------------------------------------------------------
  static loadById (id: number, t?: Transaction): Promise<MComment> {
    const query: FindOptions = {
      where: {
@@ -319,93 +271,19 @@ export class VideoCommentModel extends Model<Partial<AttributesOnly<VideoComment
    searchAccount?: string
    searchVideo?: string
  }) {
-    const { start, count, sort, isLocal, search, searchAccount, searchVideo, onLocalVideo } = parameters
+    const queryOptions: ListVideoCommentsOptions = {
+      ...pick(parameters, [ 'start', 'count', 'sort', 'isLocal', 'search', 'searchVideo', 'searchAccount', 'onLocalVideo' ]),
-    const where: WhereOptions = {
+      selectType: 'api',
-      deletedAt: null
+      notDeleted: true
-    }
-    const whereAccount: WhereOptions = {}
-    const whereActor: WhereOptions = {}
-    const whereVideo: WhereOptions = {}
-    if (isLocal === true) {
-      Object.assign(whereActor, {
-        serverId: null
-      })
-    } else if (isLocal === false) {
-      Object.assign(whereActor, {
-        serverId: {
-          [Op.ne]: null
-        }
-      })
-    }
-    if (search) {
-      Object.assign(where, {
-        [Op.or]: [
-          searchAttribute(search, 'text'),
-          searchAttribute(search, '$Account.Actor.preferredUsername$'),
-          searchAttribute(search, '$Account.name$'),
-          searchAttribute(search, '$Video.name$')
-        ]
-      })
-    }
-    if (searchAccount) {
-      Object.assign(whereActor, {
-        [Op.or]: [
-          searchAttribute(searchAccount, '$Account.Actor.preferredUsername$'),
-          searchAttribute(searchAccount, '$Account.name$')
-        ]
-      })
-    }
-    if (searchVideo) {
-      Object.assign(whereVideo, searchAttribute(searchVideo, 'name'))
-    }
-    if (exists(onLocalVideo)) {
-      Object.assign(whereVideo, { remote: !onLocalVideo })
-    }
-    const getQuery = (forCount: boolean) => {
-      return {
-        offset: start,
-        limit: count,
-        order: getCommentSort(sort),
-        where,
-        include: [
-          {
-            model: AccountModel.unscoped(),
-            required: true,
-            where: whereAccount,
-            include: [
-              {
-                attributes: {
-                  exclude: unusedActorAttributesForAPI
-                },
-                model: forCount === true
-                  ? ActorModel.unscoped() // Default scope includes avatar and server
-                  : ActorModel,
-                required: true,
-                where: whereActor
-              }
-            ]
-          },
-          {
-            model: VideoModel.unscoped(),
-            required: true,
-            where: whereVideo
-          }
-        ]
-      }
    }
    return Promise.all([
-      VideoCommentModel.count(getQuery(true)),
+      new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MCommentAdminFormattable>(),
-      VideoCommentModel.findAll(getQuery(false))
+      new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments()
-    ]).then(([ total, data ]) => ({ total, data }))
+    ]).then(([ rows, count ]) => {
+      return { total: count, data: rows }
+    })
  }
  static async listThreadsForApi (parameters: {
@@ -416,67 +294,40 @@ export class VideoCommentModel extends Model<Partial<AttributesOnly<VideoComment
    sort: string
    user?: MUserAccountId
  }) {
-    const { videoId, isVideoOwned, start, count, sort, user } = parameters
+    const { videoId, user } = parameters
-    const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ videoId, user, isVideoOwned })
+    const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user })
-    const accountBlockedWhere = {
+    const commonOptions: ListVideoCommentsOptions = {
-      accountId: {
+      selectType: 'api',
-        [Op.notIn]: Sequelize.literal(
+      videoId,
-          '(' + buildBlockedAccountSQL(blockerAccountIds) + ')'
+      blockerAccountIds
-        )
-      }
    }
-    const queryList = {
+    const listOptions: ListVideoCommentsOptions = {
-      offset: start,
+      ...commonOptions,
-      limit: count,
+      ...pick(parameters, [ 'sort', 'start', 'count' ]),
-      order: getCommentSort(sort),
-      where: {
+      isThread: true,
-        [Op.and]: [
+      includeReplyCounters: true
-          {
-            videoId
-          },
-          {
-            inReplyToCommentId: null
-          },
-          {
-            [Op.or]: [
-              accountBlockedWhere,
-              {
-                accountId: null
-              }
-            ]
-          }
-        ]
-      }
    }
-    const findScopesList: (string | ScopeOptions)[] = [
+    const countOptions: ListVideoCommentsOptions = {
-      ScopeNames.WITH_ACCOUNT_FOR_API,
+      ...commonOptions,
-      {
-        method: [ ScopeNames.ATTRIBUTES_FOR_API, blockerAccountIds ]
-      }
-    ]
-    const countScopesList: ScopeOptions[] = [
+      isThread: true
-      {
+    }
-        method: [ ScopeNames.ATTRIBUTES_FOR_API, blockerAccountIds ]
-      }
-    ]
-    const notDeletedQueryCount = {
+    const notDeletedCountOptions: ListVideoCommentsOptions = {
-      where: {
+      ...commonOptions,
-        videoId,
-        deletedAt: null,
+      notDeleted: true
-        ...accountBlockedWhere
-      }
    }
    return Promise.all([
-      VideoCommentModel.scope(findScopesList).findAll(queryList),
+      new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, listOptions).listComments<MCommentAdminFormattable>(),
-      VideoCommentModel.scope(countScopesList).count(queryList),
+      new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, countOptions).countComments(),
-      VideoCommentModel.count(notDeletedQueryCount)
+      new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, notDeletedCountOptions).countComments()
    ]).then(([ rows, count, totalNotDeletedComments ]) => {
      return { total: count, data: rows, totalNotDeletedComments }
    })
@@ -484,54 +335,29 @@ export class VideoCommentModel extends Model<Partial<AttributesOnly<VideoComment
  static async listThreadCommentsForApi (parameters: {
    videoId: number
-    isVideoOwned: boolean
    threadId: number
    user?: MUserAccountId
  }) {
-    const { videoId, threadId, user, isVideoOwned } = parameters
+    const { user } = parameters
-    const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ videoId, user, isVideoOwned })
+    const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user })
-    const query = {
+    const queryOptions: ListVideoCommentsOptions = {
-      order: [ [ 'createdAt', 'ASC' ], [ 'updatedAt', 'ASC' ] ] as Order,
+      ...pick(parameters, [ 'videoId', 'threadId' ]),
-      where: {
-        videoId,
-        [Op.and]: [
-          {
-            [Op.or]: [
-              { id: threadId },
-              { originCommentId: threadId }
-            ]
-          },
-          {
-            [Op.or]: [
-              {
-                accountId: {
-                  [Op.notIn]: Sequelize.literal(
-                    '(' + buildBlockedAccountSQL(blockerAccountIds) + ')'
-                  )
-                }
-              },
-              {
-                accountId: null
-              }
-            ]
-          }
-        ]
-      }
-    }
-    const scopes: any[] = [
+      selectType: 'api',
-      ScopeNames.WITH_ACCOUNT_FOR_API,
+      sort: 'createdAt',
-      {
-        method: [ ScopeNames.ATTRIBUTES_FOR_API, blockerAccountIds ]
+      blockerAccountIds,
-      }
+      includeReplyCounters: true
-    ]
+    }
    return Promise.all([
-      VideoCommentModel.count(query),
+      new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MCommentAdminFormattable>(),
-      VideoCommentModel.scope(scopes).findAll(query)
+      new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments()
-    ]).then(([ total, data ]) => ({ total, data }))
+    ]).then(([ rows, count ]) => {
+      return { total: count, data: rows }
+    })
  }
  static listThreadParentComments (comment: MCommentId, t: Transaction, order: 'ASC' | 'DESC' = 'ASC'): Promise<MCommentOwner[]> {
@@ -559,31 +385,31 @@ export class VideoCommentModel extends Model<Partial<AttributesOnly<VideoComment
      .findAll(query)
  }
-  static async listAndCountByVideoForAP (video: MVideoImmutable, start: number, count: number, t?: Transaction) {
+  static async listAndCountByVideoForAP (parameters: {
-    const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({
+    video: MVideoImmutable
+    start: number
+    count: number
+  }) {
+    const { video } = parameters
+    const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user: null })
+    const queryOptions: ListVideoCommentsOptions = {
+      ...pick(parameters, [ 'start', 'count' ]),
+      selectType: 'comment-only',
      videoId: video.id,
-      isVideoOwned: video.isOwned()
+      sort: 'createdAt',
-    })
-    const query = {
+      blockerAccountIds
-      order: [ [ 'createdAt', 'ASC' ] ] as Order,
-      offset: start,
-      limit: count,
-      where: {
-        videoId: video.id,
-        accountId: {
-          [Op.notIn]: Sequelize.literal(
-            '(' + buildBlockedAccountSQL(blockerAccountIds) + ')'
-          )
-        }
-      },
-      transaction: t
    }
    return Promise.all([
-      VideoCommentModel.count(query),
+      new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MComment>(),
-      VideoCommentModel.findAll<MComment>(query)
+      new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments()
-    ]).then(([ total, data ]) => ({ total, data }))
+    ]).then(([ rows, count ]) => {
+      return { total: count, data: rows }
+    })
  }
  static async listForFeed (parameters: {
@@ -592,97 +418,36 @@ export class VideoCommentModel extends Model<Partial<AttributesOnly<VideoComment
    videoId?: number
    accountId?: number
    videoChannelId?: number
-  }): Promise<MCommentOwnerVideoFeed[]> {
+  }) {
-    const serverActor = await getServerActor()
+    const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user: null })
-    const { start, count, videoId, accountId, videoChannelId } = parameters
-    const whereAnd: WhereOptions[] = buildBlockedAccountSQLOptimized(
-      '"VideoCommentModel"."accountId"',
-      [ serverActor.Account.id, '"Video->VideoChannel"."accountId"' ]
-    )
-    if (accountId) {
+    const queryOptions: ListVideoCommentsOptions = {
-      whereAnd.push({
+      ...pick(parameters, [ 'start', 'count', 'accountId', 'videoId', 'videoChannelId' ]),
-        accountId
-      })
-    }
-    const accountWhere = {
+      selectType: 'feed',
-      [Op.and]: whereAnd
-    }
-    const videoChannelWhere = videoChannelId ? { id: videoChannelId } : undefined
+      sort: '-createdAt',
+      onPublicVideo: true,
+      notDeleted: true,
-    const query = {
+      blockerAccountIds
-      order: [ [ 'createdAt', 'DESC' ] ] as Order,
-      offset: start,
-      limit: count,
-      where: {
-        deletedAt: null,
-        accountId: accountWhere
-      },
-      include: [
-        {
-          attributes: [ 'name', 'uuid' ],
-          model: VideoModel.unscoped(),
-          required: true,
-          where: {
-            privacy: VideoPrivacy.PUBLIC
-          },
-          include: [
-            {
-              attributes: [ 'accountId' ],
-              model: VideoChannelModel.unscoped(),
-              required: true,
-              where: videoChannelWhere
-            }
-          ]
-        }
-      ]
    }
-    if (videoId) query.where['videoId'] = videoId
+    return new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MCommentOwnerVideoFeed>()
-    return VideoCommentModel
-      .scope([ ScopeNames.WITH_ACCOUNT ])
-      .findAll(query)
  }
  static listForBulkDelete (ofAccount: MAccount, filter: { onVideosOfAccount?: MAccountId } = {}) {
-    const accountWhere = filter.onVideosOfAccount
+    const queryOptions: ListVideoCommentsOptions = {
-      ? { id: filter.onVideosOfAccount.id }
+      selectType: 'comment-only',
-      : {}
-    const query = {
+      accountId: ofAccount.id,
-      limit: 1000,
+      videoAccountOwnerId: filter.onVideosOfAccount?.id,
-      where: {
-        deletedAt: null,
+      notDeleted: true,
-        accountId: ofAccount.id
+      count: 5000
-      },
-      include: [
-        {
-          model: VideoModel,
-          required: true,
-          include: [
-            {
-              model: VideoChannelModel,
-              required: true,
-              include: [
-                {
-                  model: AccountModel,
-                  required: true,
-                  where: accountWhere
-                }
-              ]
-            }
-          ]
-        }
-      ]
    }
-    return VideoCommentModel
+    return new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MComment>()
-      .scope([ ScopeNames.WITH_ACCOUNT ])
-      .findAll(query)
  }
  static async getStats () {
@@ -750,9 +515,7 @@ export class VideoCommentModel extends Model<Partial<AttributesOnly<VideoComment
  }
  isOwned () {
-    if (!this.Account) {
+    if (!this.Account) return false
-      return false
-    }
    return this.Account.isOwned()
  }
@@ -906,22 +669,15 @@ export class VideoCommentModel extends Model<Partial<AttributesOnly<VideoComment
  }
  private static async buildBlockerAccountIds (options: {
-    videoId: number
+    user: MUserAccountId
-    isVideoOwned: boolean
+  }): Promise<number[]> {
-    user?: MUserAccountId
+    const { user } = options
-  }) {
-    const { videoId, user, isVideoOwned } = options
    const serverActor = await getServerActor()
    const blockerAccountIds = [ serverActor.Account.id ]
    if (user) blockerAccountIds.push(user.Account.id)
-    if (isVideoOwned) {
-      const videoOwnerAccount = await AccountModel.loadAccountIdFromVideo(videoId)
-      if (videoOwnerAccount) blockerAccountIds.push(videoOwnerAccount.id)
-    }
    return blockerAccountIds
  }
 }
diff --git a/server/models/video/video-file.ts b/server/models/video/video-file.ts
index 9c4e6d078..07bc13de1 100644
--- a/server/models/video/video-file.ts
+++ b/server/models/video/video-file.ts
@@ -21,6 +21,7 @@ import {
 import validator from 'validator'
 import { logger } from '@server/helpers/logger'
 import { extractVideo } from '@server/helpers/video'
+import { CONFIG } from '@server/initializers/config'
 import { buildRemoteVideoBaseUrl } from '@server/lib/activitypub/url'
 import {
  getHLSPrivateFileUrl,
@@ -50,11 +51,9 @@ import {
 } from '../../initializers/constants'
 import { MVideoFile, MVideoFileStreamingPlaylistVideo, MVideoFileVideo } from '../../types/models/video/video-file'
 import { VideoRedundancyModel } from '../redundancy/video-redundancy'
-import { doesExist } from '../shared'
+import { doesExist, parseAggregateResult, throwIfNotValid } from '../shared'
-import { parseAggregateResult, throwIfNotValid } from '../utils'
 import { VideoModel } from './video'
 import { VideoStreamingPlaylistModel } from './video-streaming-playlist'
-import { CONFIG } from '@server/initializers/config'
 export enum ScopeNames {
  WITH_VIDEO = 'WITH_VIDEO',
@@ -266,7 +265,7 @@ export class VideoFileModel extends Model<Partial<AttributesOnly<VideoFileModel>
  static doesInfohashExist (infoHash: string) {
    const query = 'SELECT 1 FROM "videoFile" WHERE "infoHash" = $infoHash LIMIT 1'
-    return doesExist(query, { infoHash })
+    return doesExist(this.sequelize, query, { infoHash })
  }
  static async doesVideoExistForVideoFile (id: number, videoIdOrUUID: number | string) {
@@ -282,14 +281,14 @@ export class VideoFileModel extends Model<Partial<AttributesOnly<VideoFileModel>
                  'LEFT JOIN "video" "hlsVideo" ON "hlsVideo"."id" = "videoStreamingPlaylist"."videoId" AND "hlsVideo"."remote" IS FALSE ' +
                  'WHERE "torrentFilename" = $filename AND ("hlsVideo"."id" IS NOT NULL OR "webtorrent"."id" IS NOT NULL) LIMIT 1'
-    return doesExist(query, { filename })
+    return doesExist(this.sequelize, query, { filename })
  }
  static async doesOwnedWebTorrentVideoFileExist (filename: string) {
    const query = 'SELECT 1 FROM "videoFile" INNER JOIN "video" ON "video"."id" = "videoFile"."videoId" AND "video"."remote" IS FALSE ' +
                  `WHERE "filename" = $filename AND "storage" = ${VideoStorage.FILE_SYSTEM} LIMIT 1`
-    return doesExist(query, { filename })
+    return doesExist(this.sequelize, query, { filename })
  }
  static loadByFilename (filename: string) {
@@ -439,7 +438,7 @@ export class VideoFileModel extends Model<Partial<AttributesOnly<VideoFileModel>
    if (!element) return videoFile.save({ transaction })
    for (const k of Object.keys(videoFile.toJSON())) {
-      element[k] = videoFile[k]
+      element.set(k, videoFile[k])
    }
    return element.save({ transaction })
diff --git a/server/models/video/video-import.ts b/server/models/video/video-import.ts
index da6b92c7a..c040e0fda 100644
--- a/server/models/video/video-import.ts
+++ b/server/models/video/video-import.ts
@@ -22,7 +22,7 @@ import { isVideoImportStateValid, isVideoImportTargetUrlValid } from '../../help
 import { isVideoMagnetUriValid } from '../../helpers/custom-validators/videos'
 import { CONSTRAINTS_FIELDS, VIDEO_IMPORT_STATES } from '../../initializers/constants'
 import { UserModel } from '../user/user'
-import { getSort, searchAttribute, throwIfNotValid } from '../utils'
+import { getSort, searchAttribute, throwIfNotValid } from '../shared'
 import { ScopeNames as VideoModelScopeNames, VideoModel } from './video'
 import { VideoChannelSyncModel } from './video-channel-sync'
diff --git a/server/models/video/video-playlist-element.ts b/server/models/video/video-playlist-element.ts
index 7181b5599..b832f9768 100644
--- a/server/models/video/video-playlist-element.ts
+++ b/server/models/video/video-playlist-element.ts
@@ -31,7 +31,7 @@ import { VideoPlaylistElement, VideoPlaylistElementType } from '../../../shared/
 import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc'
 import { CONSTRAINTS_FIELDS } from '../../initializers/constants'
 import { AccountModel } from '../account/account'
-import { getSort, throwIfNotValid } from '../utils'
+import { getSort, throwIfNotValid } from '../shared'
 import { ForAPIOptions, ScopeNames as VideoScopeNames, VideoModel } from './video'
 import { VideoPlaylistModel } from './video-playlist'
@@ -309,7 +309,23 @@ export class VideoPlaylistElementModel extends Model<Partial<AttributesOnly<Vide
    return VideoPlaylistElementModel.increment({ position: by }, query)
  }
-  getType (this: MVideoPlaylistElementFormattable, displayNSFW?: boolean, accountId?: number) {
+  toFormattedJSON (
+    this: MVideoPlaylistElementFormattable,
+    options: { accountId?: number } = {}
+  ): VideoPlaylistElement {
+    return {
+      id: this.id,
+      position: this.position,
+      startTimestamp: this.startTimestamp,
+      stopTimestamp: this.stopTimestamp,
+      type: this.getType(options.accountId),
+      video: this.getVideoElement(options.accountId)
+    }
+  }
+  getType (this: MVideoPlaylistElementFormattable, accountId?: number) {
    const video = this.Video
    if (!video) return VideoPlaylistElementType.DELETED
@@ -323,34 +339,17 @@ export class VideoPlaylistElementModel extends Model<Partial<AttributesOnly<Vide
    if (video.privacy === VideoPrivacy.PRIVATE || video.privacy === VideoPrivacy.INTERNAL) return VideoPlaylistElementType.PRIVATE
    if (video.isBlacklisted() || video.isBlocked()) return VideoPlaylistElementType.UNAVAILABLE
-    if (video.nsfw === true && displayNSFW === false) return VideoPlaylistElementType.UNAVAILABLE
    return VideoPlaylistElementType.REGULAR
  }
-  getVideoElement (this: MVideoPlaylistElementFormattable, displayNSFW?: boolean, accountId?: number) {
+  getVideoElement (this: MVideoPlaylistElementFormattable, accountId?: number) {
    if (!this.Video) return null
-    if (this.getType(displayNSFW, accountId) !== VideoPlaylistElementType.REGULAR) return null
+    if (this.getType(accountId) !== VideoPlaylistElementType.REGULAR) return null
    return this.Video.toFormattedJSON()
  }
-  toFormattedJSON (
-    this: MVideoPlaylistElementFormattable,
-    options: { displayNSFW?: boolean, accountId?: number } = {}
-  ): VideoPlaylistElement {
-    return {
-      id: this.id,
-      position: this.position,
-      startTimestamp: this.startTimestamp,
-      stopTimestamp: this.stopTimestamp,
-      type: this.getType(options.displayNSFW, options.accountId),
-      video: this.getVideoElement(options.displayNSFW, options.accountId)
-    }
-  }
  toActivityPubObject (this: MVideoPlaylistElementAP): PlaylistElementObject {
    const base: PlaylistElementObject = {
      id: this.url,
diff --git a/server/models/video/video-playlist.ts b/server/models/video/video-playlist.ts
index 8bbe54c49..faf4bea78 100644
--- a/server/models/video/video-playlist.ts
+++ b/server/models/video/video-playlist.ts
@@ -21,12 +21,8 @@ import { activityPubCollectionPagination } from '@server/lib/activitypub/collect
 import { MAccountId, MChannelId } from '@server/types/models'
 import { buildPlaylistEmbedPath, buildPlaylistWatchPath, pick } from '@shared/core-utils'
 import { buildUUID, uuidToShort } from '@shared/extra-utils'
+import { ActivityIconObject, PlaylistObject, VideoPlaylist, VideoPlaylistPrivacy, VideoPlaylistType } from '@shared/models'
 import { AttributesOnly } from '@shared/typescript-utils'
-import { ActivityIconObject } from '../../../shared/models/activitypub/objects'
-import { PlaylistObject } from '../../../shared/models/activitypub/objects/playlist-object'
-import { VideoPlaylistPrivacy } from '../../../shared/models/videos/playlist/video-playlist-privacy.model'
-import { VideoPlaylistType } from '../../../shared/models/videos/playlist/video-playlist-type.model'
-import { VideoPlaylist } from '../../../shared/models/videos/playlist/video-playlist.model'
 import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc'
 import {
  isVideoPlaylistDescriptionValid,
@@ -53,7 +49,6 @@ import {
 } from '../../types/models/video/video-playlist'
 import { AccountModel, ScopeNames as AccountScopeNames, SummaryOptions } from '../account/account'
 import { ActorModel } from '../actor/actor'
-import { setAsUpdated } from '../shared'
 import {
  buildServerIdsFollowedBy,
  buildTrigramSearchIndex,
@@ -61,8 +56,9 @@ import {
  createSimilarityAttribute,
  getPlaylistSort,
  isOutdated,
+  setAsUpdated,
  throwIfNotValid
-} from '../utils'
+} from '../shared'
 import { ThumbnailModel } from './thumbnail'
 import { ScopeNames as VideoChannelScopeNames, VideoChannelModel } from './video-channel'
 import { VideoPlaylistElementModel } from './video-playlist-element'
@@ -641,7 +637,7 @@ export class VideoPlaylistModel extends Model<Partial<AttributesOnly<VideoPlayli
  }
  setAsRefreshed () {
-    return setAsUpdated('videoPlaylist', this.id)
+    return setAsUpdated({ sequelize: this.sequelize, table: 'videoPlaylist', id: this.id })
  }
  setVideosLength (videosLength: number) {
diff --git a/server/models/video/video-share.ts b/server/models/video/video-share.ts
index f2190037e..b4de2b20f 100644
--- a/server/models/video/video-share.ts
+++ b/server/models/video/video-share.ts
@@ -7,7 +7,7 @@ import { CONSTRAINTS_FIELDS } from '../../initializers/constants'
 import { MActorDefault, MActorFollowersUrl, MActorId } from '../../types/models'
 import { MVideoShareActor, MVideoShareFull } from '../../types/models/video'
 import { ActorModel } from '../actor/actor'
-import { buildLocalActorIdsIn, throwIfNotValid } from '../utils'
+import { buildLocalActorIdsIn, throwIfNotValid } from '../shared'
 import { VideoModel } from './video'
 enum ScopeNames {
diff --git a/server/models/video/video-streaming-playlist.ts b/server/models/video/video-streaming-playlist.ts
index 0386edf28..a85c79c9f 100644
--- a/server/models/video/video-streaming-playlist.ts
+++ b/server/models/video/video-streaming-playlist.ts
@@ -37,8 +37,7 @@ import {
  WEBSERVER
 } from '../../initializers/constants'
 import { VideoRedundancyModel } from '../redundancy/video-redundancy'
-import { doesExist } from '../shared'
+import { doesExist, throwIfNotValid } from '../shared'
-import { throwIfNotValid } from '../utils'
 import { VideoModel } from './video'
 @Table({
@@ -138,7 +137,7 @@ export class VideoStreamingPlaylistModel extends Model<Partial<AttributesOnly<Vi
  static doesInfohashExist (infoHash: string) {
    const query = 'SELECT 1 FROM "videoStreamingPlaylist" WHERE $infoHash = ANY("p2pMediaLoaderInfohashes") LIMIT 1'
-    return doesExist(query, { infoHash })
+    return doesExist(this.sequelize, query, { infoHash })
  }
  static buildP2PMediaLoaderInfoHashes (playlistUrl: string, files: unknown[]) {
@@ -237,7 +236,7 @@ export class VideoStreamingPlaylistModel extends Model<Partial<AttributesOnly<Vi
      `AND "video"."remote" IS FALSE AND "video"."uuid" = $videoUUID ` +
      `AND "storage" = ${VideoStorage.FILE_SYSTEM} LIMIT 1`
-    return doesExist(query, { videoUUID })
+    return doesExist(this.sequelize, query, { videoUUID })
  }
  assignP2PMediaLoaderInfoHashes (video: MVideo, files: unknown[]) {
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 56cc45cfe..1a10d2da2 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -32,7 +32,7 @@ import { getHLSDirectory, getHLSRedundancyDirectory, getHlsResolutionPlaylistFil
 import { VideoPathManager } from '@server/lib/video-path-manager'
 import { isVideoInPrivateDirectory } from '@server/lib/video-privacy'
 import { getServerActor } from '@server/models/application/application'
-import { ModelCache } from '@server/models/model-cache'
+import { ModelCache } from '@server/models/shared/model-cache'
 import { buildVideoEmbedPath, buildVideoWatchPath, pick } from '@shared/core-utils'
 import { ffprobePromise, getAudioStream, hasAudioStream, uuidToShort } from '@shared/extra-utils'
 import {
@@ -103,10 +103,9 @@ import { VideoRedundancyModel } from '../redundancy/video-redundancy'
 import { ServerModel } from '../server/server'
 import { TrackerModel } from '../server/tracker'
 import { VideoTrackerModel } from '../server/video-tracker'
-import { setAsUpdated } from '../shared'
+import { buildTrigramSearchIndex, buildWhereIdOrUUID, getVideoSort, isOutdated, setAsUpdated, throwIfNotValid } from '../shared'
 import { UserModel } from '../user/user'
 import { UserVideoHistoryModel } from '../user/user-video-history'
-import { buildTrigramSearchIndex, buildWhereIdOrUUID, getVideoSort, isOutdated, throwIfNotValid } from '../utils'
 import { VideoViewModel } from '../view/video-view'
 import {
  videoFilesModelToFormattedJSON,
@@ -1871,7 +1870,7 @@ export class VideoModel extends Model<Partial<AttributesOnly<VideoModel>>> {
  }
  setAsRefreshed (transaction?: Transaction) {
-    return setAsUpdated('video', this.id, transaction)
+    return setAsUpdated({ sequelize: this.sequelize, table: 'video', id: this.id, transaction })
  }
  // ---------------------------------------------------------------------------
diff --git a/server/models/view/local-video-viewer.ts b/server/models/view/local-video-viewer.ts
index 9d0d89a59..274117e86 100644
--- a/server/models/view/local-video-viewer.ts
+++ b/server/models/view/local-video-viewer.ts
@@ -21,6 +21,10 @@ import { LocalVideoViewerWatchSectionModel } from './local-video-viewer-watch-se
  indexes: [
    {
      fields: [ 'videoId' ]
+    },
+    {
+      fields: [ 'url' ],
+      unique: true
    }
  ]
 })