Skip to content

Privacy-first password manager with built-in email aliasing. Fully encrypted and self-hostable.

18.1 KB
Raw Blame
/**
 * SQL query constants for Item operations.
 * Centralizes all item-related queries to avoid duplication.
 */
export class ItemQueries {
  /**
   * Base SELECT for items with common fields.
   * Includes LEFT JOIN to the item's logo row, which carries the image bytes for a favicon or an
   * uploaded image, and the catalog key for a built-in logo (which has no bytes). Folder paths are
   * computed in the repository layer.
   */
  public static readonly BASE_SELECT = `
    SELECT DISTINCT
      i.Id,
      i.ManifestId,
      i.Name,
      i.ItemType,
      i.FolderId,
      l.FileData as Logo,
      l.Id as LogoId,
      l.Kind as LogoKind,
      l.Source as LogoSource,
      l.Name as LogoName,
      CASE WHEN EXISTS (SELECT 1 FROM Passkeys pk WHERE pk.ItemId = i.Id AND pk.ManifestId = i.ManifestId AND pk.IsDeleted = 0) THEN 1 ELSE 0 END as HasPasskey,
      CASE WHEN EXISTS (SELECT 1 FROM Attachments att WHERE att.ItemId = i.Id AND att.ManifestId = i.ManifestId AND att.IsDeleted = 0) THEN 1 ELSE 0 END as HasAttachment,
      CASE WHEN EXISTS (SELECT 1 FROM TotpCodes tc WHERE tc.ItemId = i.Id AND tc.ManifestId = i.ManifestId AND tc.IsDeleted = 0) THEN 1 ELSE 0 END as HasTotp,
      i.CreatedAt,
      i.UpdatedAt,
      i.ArchivedAt
    FROM Items i
    LEFT JOIN Logos l ON i.LogoId = l.Id AND l.ManifestId = i.ManifestId`;

  /**
   * Get all active items (not deleted, not in trash, not archived).
   *
   * This is the source list for both the main item list and autofill, so archived items are excluded
   * from both by this single predicate.
   */
  public static readonly GET_ALL_ACTIVE = `
    ${ItemQueries.BASE_SELECT}
    WHERE i.IsDeleted = 0 AND i.DeletedAt IS NULL AND i.ArchivedAt IS NULL
    ORDER BY i.CreatedAt DESC`;

  /**
   * Get the active items of one folder, matched on the folder's whole key.
   */
  public static readonly GET_BY_FOLDER = `
    ${ItemQueries.BASE_SELECT}
    WHERE i.IsDeleted = 0 AND i.DeletedAt IS NULL AND i.ArchivedAt IS NULL AND i.FolderId = ? AND i.ManifestId = ?
    ORDER BY i.CreatedAt DESC`;

  /**
   * Get the active items a manifest stores outside every folder.
   */
  public static readonly GET_AT_MANIFEST_TOP_LEVEL = `
    ${ItemQueries.BASE_SELECT}
    WHERE i.IsDeleted = 0 AND i.DeletedAt IS NULL AND i.ArchivedAt IS NULL AND i.FolderId IS NULL AND i.ManifestId = ?
    ORDER BY i.CreatedAt DESC`;

  /**
   * Get every active item that the folder counts need.
   */
  public static readonly GET_ALL_SUMMARIES = `
    SELECT
      i.Id,
      i.ManifestId,
      i.ItemType,
      i.FolderId,
      CASE WHEN EXISTS (SELECT 1 FROM Passkeys pk WHERE pk.ItemId = i.Id AND pk.ManifestId = i.ManifestId AND pk.IsDeleted = 0) THEN 1 ELSE 0 END as HasPasskey,
      CASE WHEN EXISTS (SELECT 1 FROM Attachments att WHERE att.ItemId = i.Id AND att.ManifestId = i.ManifestId AND att.IsDeleted = 0) THEN 1 ELSE 0 END as HasAttachment,
      CASE WHEN EXISTS (SELECT 1 FROM TotpCodes tc WHERE tc.ItemId = i.Id AND tc.ManifestId = i.ManifestId AND tc.IsDeleted = 0) THEN 1 ELSE 0 END as HasTotp
    FROM Items i
    WHERE i.IsDeleted = 0 AND i.DeletedAt IS NULL AND i.ArchivedAt IS NULL`;

  /**
   * Get all archived items. Trashed items are excluded: an item that is both archived and trashed
   * belongs in "Recently Deleted", which is the more urgent of the two states.
   */
  public static readonly GET_ARCHIVED = `
    ${ItemQueries.BASE_SELECT}
    WHERE i.IsDeleted = 0 AND i.DeletedAt IS NULL AND i.ArchivedAt IS NOT NULL
    ORDER BY i.ArchivedAt DESC`;

  /**
   * Count of archived items.
   */
  public static readonly COUNT_ARCHIVED = `
    SELECT COUNT(*) as count
    FROM Items
    WHERE IsDeleted = 0 AND DeletedAt IS NULL AND ArchivedAt IS NOT NULL`;

  /**
   * Get a single item by its manifest-qualified key: an id alone does not identify a row, since the
   * same id can exist in two or more manifests. Items in the trash count as not found.
   */
  public static readonly GET_BY_ID = `
    SELECT
      i.Id,
      i.ManifestId,
      i.Name,
      i.ItemType,
      i.FolderId,
      l.FileData as Logo,
      l.Id as LogoId,
      l.Kind as LogoKind,
      l.Source as LogoSource,
      l.Name as LogoName,
      CASE WHEN EXISTS (SELECT 1 FROM Passkeys pk WHERE pk.ItemId = i.Id AND pk.ManifestId = i.ManifestId AND pk.IsDeleted = 0) THEN 1 ELSE 0 END as HasPasskey,
      CASE WHEN EXISTS (SELECT 1 FROM Attachments att WHERE att.ItemId = i.Id AND att.ManifestId = i.ManifestId AND att.IsDeleted = 0) THEN 1 ELSE 0 END as HasAttachment,
      CASE WHEN EXISTS (SELECT 1 FROM TotpCodes tc WHERE tc.ItemId = i.Id AND tc.ManifestId = i.ManifestId AND tc.IsDeleted = 0) THEN 1 ELSE 0 END as HasTotp,
      i.CreatedAt,
      i.UpdatedAt,
      i.ArchivedAt
    FROM Items i
    LEFT JOIN Logos l ON i.LogoId = l.Id AND l.ManifestId = i.ManifestId
    WHERE i.Id = ? AND i.ManifestId = ? AND i.IsDeleted = 0 AND i.DeletedAt IS NULL`;

  /**
   * Count of recently deleted items.
   */
  public static readonly COUNT_RECENTLY_DELETED = `
    SELECT COUNT(*) as count
    FROM Items
    WHERE IsDeleted = 0 AND DeletedAt IS NOT NULL`;

  /**
   * Get field values for multiple items, matched on the whole item key.
   *
   * The caller passes `(ManifestId, Id)` pairs rather than bare ids: items are keyed by both, so two
   * manifests may hold an item with the same Id and matching on the Id alone would hand each one the
   * other's fields.
   * @param itemCount - Number of items (for placeholder generation)
   * @returns Query with one (?, ?) placeholder pair per item, bound as [manifestId, itemId, ...]
   */
  public static getFieldValuesForItems(itemCount: number): string {
    const placeholders = ItemQueries.itemKeyPlaceholders(itemCount);
    return `
      SELECT
        fv.ItemId,
        fv.ManifestId,
        fv.FieldKey,
        fv.FieldDefinitionId,
        fd.Label as CustomLabel,
        fd.FieldType as CustomFieldType,
        fd.IsHidden as CustomIsHidden,
        fd.EnableHistory as CustomEnableHistory,
        fv.Value,
        fv.Weight as DisplayOrder
      FROM FieldValues fv
      LEFT JOIN FieldDefinitions fd ON fd.ManifestId = fv.ManifestId AND fd.Id = fv.FieldDefinitionId
      WHERE (fv.ManifestId, fv.ItemId) IN (VALUES ${placeholders})
        AND fv.IsDeleted = 0
      ORDER BY fv.ItemId, fv.Weight, fv.ValueIndex`;
  }

  /**
   * Get field values for a single item.
   */
  public static readonly GET_FIELD_VALUES_FOR_ITEM = `
    SELECT
      fv.FieldKey,
      fv.FieldDefinitionId,
      fd.Label as CustomLabel,
      fd.FieldType as CustomFieldType,
      fd.IsHidden as CustomIsHidden,
      fd.EnableHistory as CustomEnableHistory,
      fv.Value,
      fv.Weight as DisplayOrder
    FROM FieldValues fv
    LEFT JOIN FieldDefinitions fd ON fd.ManifestId = fv.ManifestId AND fd.Id = fv.FieldDefinitionId
    WHERE fv.ItemId = ? AND fv.ManifestId = ? AND fv.IsDeleted = 0
    ORDER BY fv.Weight, fv.ValueIndex`;

  /**
   * Get tags for multiple items, matched on the whole item key (see {@link getFieldValuesForItems}).
   * @param itemCount - Number of items (for placeholder generation)
   * @returns Query with one (?, ?) placeholder pair per item, bound as [manifestId, itemId, ...]
   */
  public static getTagsForItems(itemCount: number): string {
    const placeholders = ItemQueries.itemKeyPlaceholders(itemCount);
    return `
      SELECT
        it.ItemId,
        it.ManifestId,
        t.Id,
        t.Name,
        t.Color
      FROM ItemTags it
      INNER JOIN Tags t ON t.ManifestId = it.ManifestId AND t.Id = it.TagId
      WHERE (it.ManifestId, it.ItemId) IN (VALUES ${placeholders})
        AND it.IsDeleted = 0
        AND t.IsDeleted = 0
      ORDER BY t.DisplayOrder, t.Name`;
  }

  /**
   * Generate placeholders for item key pairs.
   */
  private static itemKeyPlaceholders(itemCount: number): string {
    return Array(itemCount).fill('(?, ?)').join(', ');
  }

  /**
   * Get tags for a single item.
   */
  public static readonly GET_TAGS_FOR_ITEM = `
    SELECT
      t.Id,
      t.Name,
      t.Color
    FROM ItemTags it
    INNER JOIN Tags t ON t.ManifestId = it.ManifestId AND t.Id = it.TagId
    WHERE it.ItemId = ? AND it.ManifestId = ? AND it.IsDeleted = 0 AND t.IsDeleted = 0
    ORDER BY t.DisplayOrder, t.Name`;

  /**
   * Insert a new item into the manifest the caller names, which is its folder's own.
   */
  public static readonly INSERT_ITEM = `
    INSERT INTO Items (Id, Name, ItemType, LogoId, FolderId, ManifestId, CreatedAt, UpdatedAt, IsDeleted)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;

  /**
   * Update an existing item with explicit LogoId setting (can clear LogoId to null).
   */
  public static readonly UPDATE_ITEM_WITH_LOGO = `
    UPDATE Items
    SET Name = ?,
        ItemType = ?,
        FolderId = ?,
        ManifestId = ?,
        LogoId = ?,
        UpdatedAt = ?
    WHERE Id = ? AND ManifestId = ?`;

  /**
   * Move item to trash (set DeletedAt).
   */
  public static readonly TRASH_ITEM = `
    UPDATE Items
    SET DeletedAt = ?,
        UpdatedAt = ?
    WHERE Id = ? AND ManifestId = ? AND IsDeleted = 0`;

  /**
   * Restore item from trash (clear DeletedAt).
   */
  public static readonly RESTORE_ITEM = `
    UPDATE Items
    SET DeletedAt = NULL,
        UpdatedAt = ?
    WHERE Id = ? AND ManifestId = ? AND IsDeleted = 0 AND DeletedAt IS NOT NULL`;

  /**
   * Archive an item (set ArchivedAt).
   */
  public static readonly ARCHIVE_ITEM = `
    UPDATE Items
    SET ArchivedAt = ?,
        UpdatedAt = ?
    WHERE Id = ? AND ManifestId = ? AND IsDeleted = 0 AND ArchivedAt IS NULL`;

  /**
   * Unarchive an item (clear ArchivedAt).
   */
  public static readonly UNARCHIVE_ITEM = `
    UPDATE Items
    SET ArchivedAt = NULL,
        UpdatedAt = ?
    WHERE Id = ? AND ManifestId = ? AND IsDeleted = 0 AND ArchivedAt IS NOT NULL`;

  /**
   * Convert item to tombstone for permanent deletion.
   */
  public static readonly TOMBSTONE_ITEM = `
    UPDATE Items
    SET IsDeleted = 1,
        Name = NULL,
        LogoId = NULL,
        FolderId = NULL,
        ArchivedAt = NULL,
        UpdatedAt = ?
    WHERE Id = ? AND ManifestId = ?`;

  /**
   * Get all unique email addresses the vault still routes mail to. Addresses the user switched off are excluded:
   * their claim is disabled server-side, so no new mail arrives for them and their inbox stays hidden.
   */
  public static readonly GET_ROUTABLE_EMAIL_ADDRESSES = `
    SELECT DISTINCT fv.Value as Email
    FROM FieldValues fv
    INNER JOIN Items i ON fv.ItemId = i.Id AND fv.ManifestId = i.ManifestId
    WHERE fv.FieldKey = ?
      AND fv.Value IS NOT NULL
      AND fv.Value != ''
      AND fv.IsDeleted = 0
      AND fv.IsDisabled = 0
      AND i.IsDeleted = 0
      AND i.DeletedAt IS NULL`;

  /**
   * Count the live, switched-on login email fields carrying one address. Zero means the vault holds no enabled
   * claim for it, so its mailbox must not be shown.
   */
  public static readonly COUNT_ROUTABLE_EMAIL_FIELDS = `
    SELECT COUNT(*) as Count
    FROM FieldValues fv
    INNER JOIN Items i ON fv.ItemId = i.Id AND fv.ManifestId = i.ManifestId
    WHERE fv.FieldKey = ?
      AND LOWER(fv.Value) = ?
      AND fv.IsDeleted = 0
      AND fv.IsDisabled = 0
      AND i.IsDeleted = 0
      AND i.DeletedAt IS NULL`;

  /**
   * Look up an item (reference + name) by an email address stored in any of its login email fields.
   */
  public static readonly GET_ITEM_BY_EMAIL = `
    SELECT i.Id as Id, i.ManifestId as ManifestId, i.Name as Name
    FROM FieldValues fv
    INNER JOIN Items i ON fv.ItemId = i.Id AND fv.ManifestId = i.ManifestId
    WHERE fv.FieldKey = ?
      AND fv.Value = ?
      AND fv.IsDeleted = 0
      AND i.IsDeleted = 0
      AND i.DeletedAt IS NULL
    LIMIT 1`;

  /**
   * Get item-level fields for change detection during updates.
   */
  public static readonly GET_ITEM_FIELDS = `
    SELECT Name, ItemType, FolderId, LogoId
    FROM Items
    WHERE Id = ? AND ManifestId = ? AND IsDeleted = 0`;
}

/**
 * SQL query constants for FieldValue operations.
 */
export class FieldValueQueries {
  /**
   * Get every field value row of an item, tombstones included, so a write can bring a removed field's row back
   * instead of adding a second row for the same field.
   */
  public static readonly GET_ALL_FOR_ITEM = `
    SELECT Id, FieldKey, FieldDefinitionId, Value, Weight, ValueIndex, IsDeleted
    FROM FieldValues
    WHERE ItemId = ? AND ManifestId = ?
    ORDER BY ValueIndex`;

  /**
   * Insert a new field value, into the manifest of the item it belongs to.
   */
  public static readonly INSERT = `
    INSERT INTO FieldValues (Id, ItemId, ManifestId, FieldDefinitionId, FieldKey, Value, Weight, ValueIndex, CreatedAt, UpdatedAt, IsDeleted)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

  /**
   * Update an existing field value, bringing it back when it was removed before.
   */
  public static readonly UPDATE = `
    UPDATE FieldValues
    SET Value = ?,
        Weight = ?,
        ValueIndex = ?,
        IsDeleted = 0,
        UpdatedAt = ?
    WHERE Id = ? AND ManifestId = ?`;

  /**
   * Soft delete a field value.
   */
  public static readonly SOFT_DELETE = `
    UPDATE FieldValues
    SET IsDeleted = 1,
        UpdatedAt = ?
    WHERE Id = ? AND ManifestId = ?`;

  /**
   * Get existing field values for history tracking.
   */
  public static readonly GET_FOR_HISTORY = `
    SELECT FieldKey, Value
    FROM FieldValues
    WHERE ItemId = ? AND ManifestId = ? AND IsDeleted = 0 AND FieldKey IS NOT NULL
    ORDER BY ValueIndex`;
}

/**
 * SQL query constants for FieldDefinition operations.
 */
export class FieldDefinitionQueries {
  /**
   * Check if a field definition exists in the item's manifest. Binds [definitionId, manifestId].
   */
  public static readonly EXISTS = `
    SELECT Id FROM FieldDefinitions WHERE Id = ? AND ManifestId = ?`;

  /**
   * Insert a new field definition into the item's manifest. Binds [definitionId, manifestId, ...].
   */
  public static readonly INSERT = `
    INSERT INTO FieldDefinitions (Id, ManifestId, FieldType, Label, IsMultiValue, IsHidden, EnableHistory, Weight, ApplicableToTypes, CreatedAt, UpdatedAt, IsDeleted)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

  /**
   * Update an existing field definition, reviving it when soft-deleted.
   */
  public static readonly UPDATE = `
    UPDATE FieldDefinitions
    SET Label = ?,
        FieldType = ?,
        IsHidden = ?,
        Weight = ?,
        UpdatedAt = ?,
        IsDeleted = 0
    WHERE Id = ? AND ManifestId = ?`;
}

/**
 * SQL query constants for FieldHistory operations.
 */
export class FieldHistoryQueries {
  /**
   * Insert a history record, into the manifest of the item it belongs to.
   */
  public static readonly INSERT = `
    INSERT INTO FieldHistories (Id, ItemId, ManifestId, FieldDefinitionId, FieldKey, ValueSnapshot, ChangedAt, CreatedAt, UpdatedAt, IsDeleted)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

  /**
   * Get history records for a field.
   */
  public static readonly GET_FOR_FIELD = `
    SELECT
      Id,
      ItemId,
      FieldKey,
      ValueSnapshot,
      ChangedAt,
      CreatedAt,
      UpdatedAt
    FROM FieldHistories
    WHERE ItemId = ? AND ManifestId = ? AND FieldKey = ? AND IsDeleted = 0
    ORDER BY ChangedAt DESC
    LIMIT ?`;

  /**
   * Get all history records for pruning.
   */
  public static readonly GET_FOR_PRUNING = `
    SELECT Id, ChangedAt
    FROM FieldHistories
    WHERE ItemId = ? AND ManifestId = ? AND FieldKey = ? AND IsDeleted = 0
    ORDER BY ChangedAt DESC`;

  /**
   * Soft delete old history records.
   * @param count - Number of records to delete
   * @returns Query with placeholders
   */
  public static softDeleteOld(count: number): string {
    const placeholders = Array(count).fill('?').join(',');
    return `
      UPDATE FieldHistories
      SET IsDeleted = 1, UpdatedAt = ?
      WHERE ManifestId = ? AND Id IN (${placeholders})`;
  }

  /**
   * Soft delete a single history record.
   */
  public static readonly SOFT_DELETE = `
    UPDATE FieldHistories
    SET IsDeleted = 1, UpdatedAt = ?
    WHERE Id = ? AND ManifestId = ?`;
}

/**
 * SQL query constants for TotpCode operations.
 */
export class TotpCodeQueries {
  /**
   * Get an item's TOTP codes. Scoped by manifest as well as item: an id alone does not name one item,
   * so a bare `ItemId` match would pull in a same-id item's codes from another manifest.
   */
  public static readonly GET_BY_ITEM_ID = `
    SELECT Id, Name, SecretKey, Algorithm, Digits, Period, ItemId
    FROM TotpCodes
    WHERE ItemId = ? AND ManifestId = ? AND IsDeleted = 0`;

  /**
   * Insert a new TOTP code, into the manifest of the item it belongs to.
   */
  public static readonly INSERT = `
    INSERT INTO TotpCodes (Id, Name, SecretKey, Algorithm, Digits, Period, ItemId, ManifestId, CreatedAt, UpdatedAt, IsDeleted)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

  /**
   * Update an existing TOTP code.
   */
  public static readonly UPDATE = `
    UPDATE TotpCodes
    SET Name = ?,
        SecretKey = ?,
        Algorithm = ?,
        Digits = ?,
        Period = ?,
        UpdatedAt = ?
    WHERE Id = ? AND ManifestId = ?`;

  /**
   * Soft delete a TOTP code.
   */
  public static readonly SOFT_DELETE = `
    UPDATE TotpCodes
    SET IsDeleted = 1,
        UpdatedAt = ?
    WHERE Id = ? AND ManifestId = ?`;
}

/**
 * SQL query constants for Attachment operations.
 */
export class AttachmentQueries {
  /**
   * Get an item's attachments. Scoped by manifest as well as item, for the same reason as
   * {@link TotpCodeQueries.GET_BY_ITEM_ID}.
   */
  public static readonly GET_BY_ITEM_ID = `
    SELECT
      Id,
      Filename,
      Blob,
      ItemId,
      CreatedAt,
      UpdatedAt,
      IsDeleted
    FROM Attachments
    WHERE ItemId = ? AND ManifestId = ? AND IsDeleted = 0`;

  /**
   * Insert a new attachment, into the manifest of the item it belongs to.
   */
  public static readonly INSERT = `
    INSERT INTO Attachments (Id, Filename, Blob, ItemId, ManifestId, CreatedAt, UpdatedAt, IsDeleted)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)`;

  /**
   * Soft delete an attachment. Also drops the Blob bytes so storage is reclaimed
   * immediately while the row remains as a tombstone for LWW sync.
   */
  public static readonly SOFT_DELETE = `
    UPDATE Attachments
    SET IsDeleted = 1,
        Blob = NULL,
        UpdatedAt = ?
    WHERE Id = ? AND ManifestId = ?`;
}