Strange error when filtering createdAt with a sort key

[Lychas]

Environment information

System:
  OS: Windows 11 10.0.26100
  CPU: (28) x64 Intel(R) Core(TM) i7-14700KF
  Memory: 19.17 GB / 31.83 GB
Binaries:
  Node: 22.16.0 - C:\Program Files\nodejs\node.EXE
  Yarn: undefined - undefined
  npm: 11.4.2 - C:\Program Files\nodejs\npm.CMD
  pnpm: undefined - undefined
NPM Packages:
  @aws-amplify/auth-construct: 1.8.1
  @aws-amplify/backend: 1.16.1
  @aws-amplify/backend-ai: Not Found
  @aws-amplify/backend-auth: 1.7.1
  @aws-amplify/backend-cli: 1.8.0
  @aws-amplify/backend-data: 1.6.1
  @aws-amplify/backend-deployer: 2.1.3
  @aws-amplify/backend-function: 1.14.1
  @aws-amplify/backend-output-schemas: 1.7.0
  @aws-amplify/backend-output-storage: 1.3.1
  @aws-amplify/backend-secret: 1.4.0
  @aws-amplify/backend-storage: 1.4.1
  @aws-amplify/cli-core: 2.2.1
  @aws-amplify/client-config: 1.8.0
  @aws-amplify/data-construct: 1.16.1
  @aws-amplify/data-schema: 1.21.0
  @aws-amplify/deployed-backend-client: 1.8.0
  @aws-amplify/form-generator: 1.2.1
  @aws-amplify/model-generator: 1.2.0
  @aws-amplify/platform-core: 1.10.0
  @aws-amplify/plugin-types: 1.11.0
  @aws-amplify/sandbox: 2.1.2
  @aws-amplify/schema-generator: 1.4.0
  @aws-cdk/toolkit-lib: 1.1.1
  aws-amplify: 6.15.5
  aws-cdk-lib: 2.210.0
  typescript: 5.8.3
npm warn exec The following package was not found and will be installed: cdk@2.1025.0
No AWS environment variables
No CDK environment variables

Describe the bug

Hello there, I'm getting an error when filtering on createdAt while using a sort key to filter the data from GraphQl

This is the error from CloudWatch when running my function:

ERROR	Invoke Error 	
{
    "errorType": "Error",
    "errorMessage": "[{\"path\":[\"listActivityLogBySortKeyAndCreatedAt\"],\"data\":null,\"errorType\":\"DynamoDB:DynamoDbException\",\"errorInfo\":null,\"locations\":[{\"line\":9,\"column\":3,\"sourceName\":null}],\"message\":\"Invalid KeyConditionExpression: KeyConditionExpressions must only contain one condition per key (Service: DynamoDb, Status Code: 400, Request ID: 6O2VNKB86T6N7NKLFC2O8JV1ORVV4KQNSO5AEMVJF66Q9ASUAAJG) (SDK Attempt Count: 1)\"}]",
    "stack": [
        "Error: [{\"path\":[\"listActivityLogBySortKeyAndCreatedAt\"],\"data\":null,\"errorType\":\"DynamoDB:DynamoDbException\",\"errorInfo\":null,\"locations\":[{\"line\":9,\"column\":3,\"sourceName\":null}],\"message\":\"Invalid KeyConditionExpression: KeyConditionExpressions must only contain one condition per key (Service: DynamoDb, Status Code: 400, Request ID: 6O2VNKB86T6N7NKLFC2O8JV1ORVV4KQNSO5AEMVJF66Q9ASUAAJG) (SDK Attempt Count: 1)\"}]",
        "    at vP (file:///var/task/index.mjs:573:1821)",
        "    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)",
        "    at async Promise.all (index 0)",
        "    at async Runtime.NAn [as handler] (file:///var/task/index.mjs:573:1199)"
    ]
}

Whenever I pass any createAt filter to any query with "list(x)BySortKeyAndCreatedAt)" it returns this error and I can't understand it or why it's happening.

Any other table that gets createdAt filter that doesn't use a sortKey works great but if I even dare to put any filter on the createdAt on "list(x)BySortKeyAndCreatedAt) then it returns nothing, this error is shared throught the Amplify app with expo and on lambda functions with amplify.

Reproduction steps

this is my Schema:

import { type ClientSchema, a, defineData } from "@aws-amplify/backend";
import { calculateAgenciesMonthly } from "../functions/calculateAgenciesMonthly/resource";
import { checkIfExcelDataValid } from "../functions/checkIfExcelDataValid/resource";
import { createActivityLog } from "../functions/createActivityLog/resource";
import { createExcel } from "../functions/createExcel/resource";
import { createNewTask } from "../functions/createNewTask/resource";
import { createUser } from "../functions/createUser/resource";
import { deleteUser } from "../functions/deleteUser/resource";
import { editTask } from "../functions/editTask/resource";
import { editUser } from "../functions/editUser/resource";
import { getFullUser } from "../functions/getFullUser/resource";
import { getMassImportTemplate } from "../functions/getMassImportTemplate/resource";
import { getNewPackage } from "../functions/getNewPackage/resource";
import { massImport } from "../functions/massImport/resource";

const schema = a
    .schema({
        // models
        Employee: a
            .model({
                id: a.string().required(),
                name: a.string().required(),
                department: a.ref("Departments").required(),
                salary: a
                    .integer()
                    .required()
                    .validate((value) => value.gt(0)),
                expectedHours: a
                    .integer()
                    .required()
                    .validate((value) => value.gt(0)),
                tasks: a.hasMany("Task", "employeeId"),
                activityLogs: a.hasMany("ActivityLog", "employeeId"),
            })
            .disableOperations(["subscriptions"])
            .authorization((allow) => [
                allow.ownerDefinedIn("id").to(["read"]),
                allow.group("MANAGEMENT").to(["read"]),
            ]),

        Task: a
            .model({
                id: a.id().required(),
                title: a.string().required(),
                // start: a.datetime().required(),
                // end: a.datetime().required(),
                agency: a.ref("Agencies").required(),

                totalHours: a
                    .float()
                    .required()
                    .validate((value) => value.gt(0)),
                hourlyValue: a
                    .float()
                    .required()
                    .validate((value) => value.gt(0)),
                salaryAtTime: a
                    .float()
                    .required()
                    .validate((value) => value.gt(0)),
                taskValue: a
                    .float()
                    .required()
                    .validate((value) => value.gt(0)),
                projectNumber: a.string(),
                invoiceNumber: a.string(),
                taskDate: a.datetime().required(),
                sortKey: a.ref("SortKey").required(),
                createdAt: a.datetime().required(),
                governorate: a.ref("IraqGovernorates").required(),
                outsideIraqLocation: a.string(),

                employeeId: a.id().required(),
                employee: a.belongsTo("Employee", "employeeId"),

                customerNumber: a.id().required(),
                customer: a.belongsTo("Customer", "customerNumber"),
            })
            .disableOperations(["subscriptions"])
            .secondaryIndexes((index) => [
                index("employeeId").sortKeys(["createdAt"]),
                index("sortKey").sortKeys(["createdAt"]),
            ])
            .authorization((allow) => [
                allow.ownerDefinedIn("employeeId").to(["read"]),
                allow.group("MANAGEMENT"),
            ]),

        // This model calculates the total value and time spent on tasks for each agency monthly.
        // It is used for reporting and analytics purposes.
        AgencyTasks: a
            .model({
                id: a.id().required(),
                // from agency enums
                beckmanCost: a.float().required(),
                beckmanHours: a.float().required(),
                leicaCost: a.float().required(),
                leicaHours: a.float().required(),
                bioRadCost: a.float().required(),
                bioRadHours: a.float().required(),
                schmitzCost: a.float().required(),
                schmitzHours: a.float().required(),
                dexisCost: a.float().required(),
                dexisHours: a.float().required(),
                bioronCost: a.float().required(),
                bioronHours: a.float().required(),
                spectroCost: a.float().required(),
                spectroHours: a.float().required(),
                molecularCost: a.float().required(),
                molecularHours: a.float().required(),
                hqProjectCost: a.float().required(),
                hqProjectHours: a.float().required(),
                otherCost: a.float().required(),
                otherHours: a.float().required(),

                sortKey: a.ref("SortKey").required(),

                createdAt: a.datetime().required(),

                totalCost: a.float().required(),
                totalHours: a.float().required(),
            })
            .disableOperations(["subscriptions"])
            .secondaryIndexes((index) => [index("sortKey").sortKeys(["createdAt"])])
            .authorization((allow) => [allow.group("MANAGEMENT")]),

        Customer: a
            .model({
                name: a.string().required(),
                number: a.string().required(),
                city: a.string().required(),
                address: a.string(),
                createdAt: a.datetime(),

                tasks: a.hasMany("Task", "customerNumber"),
            })
            .disableOperations(["subscriptions"])
            .authorization((allow) => [
                allow.authenticated().to(["read"]),
                allow.group("MANAGEMENT"),
            ])
            .identifier(["number"]),

        ActivityLog: a
            .model({
                id: a.id().required(),
                action: a.ref("ActivityLogActions").required(),
                description: a.string().required(),
                createdAt: a.datetime().required(),
                sortKey: a.ref("SortKey").required(),
                employeeId: a.id().required(),
                employee: a.belongsTo("Employee", "employeeId"),
            })
            .disableOperations(["subscriptions"])
            .authorization((allow) => [allow.group("MANAGEMENT").to(["read"])])
            .secondaryIndexes((index) => [index("sortKey").sortKeys(["createdAt"])]),

        //ServiceHub
        ServiceHubUnAssignedTask: a
            .model({
                id: a.id().required(),

                type: a.string().required(),
                status: a.string().required(),
                description: a.string().required(),

                sortKey: a.ref("SortKey").required(),
                createdAt: a.datetime().required(),
            })
            .disableOperations(["subscriptions"])
            .secondaryIndexes((index) => [index("sortKey").sortKeys(["createdAt"])])
            .authorization((allow) => [
                allow.group("MANAGEMENT"),
                allow.group("SERVICE").to(["create"]),
                allow.group("SERVICEMANAGER"),
            ]),

        ServiceHubActiveTask: a
            .model({
                id: a.id().required(),
                type: a.string().required(),
                status: a.string().required(),
                description: a.string().required(),

                dueDate: a.datetime(),
                startDate: a.datetime(),
                assignedAt: a.datetime(),
                requestedAt: a.datetime(),

                sortKey: a.ref("SortKey").required(),
                createdAt: a.datetime().required(),
            })
            .disableOperations(["subscriptions"])
            .authorization((allow) => [allow.group("MANAGEMENT"), allow.group("SERVICEMANAGER")])
            .secondaryIndexes((index) => [index("sortKey").sortKeys(["createdAt"])]),

        ServiceHubFinishedTask: a
            .model({
                id: a.id().required(),
                type: a.string().required(),
                startDate: a.datetime().required(),
                endDate: a.datetime().required(),
                description: a.string().required(),

                parts: a.ref("serviceHubPart").array(),
                isPartReplaced: a.boolean().required(),
                comment: a.string().required(),

                beforePhotos: a.ref("photo").array(),
                afterPhotos: a.ref("photo").array(),

                personInCharge: a.string().required(),
                personInChargePhoneNumber: a.phone().required(),
                peopleTrained: a.string(),
                feedback: a.ref("feedback"),

                customerSignature: a.ref("photo").required(),
                engineerSignature: a.ref("photo").required(),

                assignedAt: a.datetime().required(),
                requestedAt: a.datetime().required(),
                status: a.string().required(),

                finishingEngineer: a.string().required(),

                sortKey: a.ref("SortKey").required(),
                createdAt: a.datetime().required(),
            })
            .disableOperations(["subscriptions"])
            .secondaryIndexes((index) => [index("sortKey").sortKeys(["createdAt"])])
            .authorization((allow) => [allow.group("MANAGEMENT"), allow.group("SERVICEMANAGER")]),

        ServiceHubDevice: a
            .model({
                id: a.id().required(),
                model: a.string().required(),
                brand: a.string().required(),
                serialNumber: a.string().required(),

                installationDate: a.datetime(),
                lastService: a.datetime(),
                isDeviceActive: a.boolean(),

                remarks: a.string().required(),
                location: a.string().required(),

                orderNumber: a.string().required(),
                city: a.string().required(),

                deviceHistory: a.ref("deviceHistory").array(),
            })
            .disableOperations(["subscriptions"])
            .authorization((allow) => [
                allow.group("MANAGEMENT"),
                allow.group("SERVICEMANAGER"),
                allow.ownerDefinedIn("employeeId").to(["read"]),
            ]),

        // functions
        createUser: a
            .mutation()
            .arguments({
                email: a.email().required(),
                givenName: a.string().required(),
                isManagement: a.boolean().required(),
                isServiceManager: a.boolean().required(),
                salary: a.integer().required(),
                department: a.ref("Departments").required(),
                expectedHours: a.integer().required(),
            })
            .returns(a.boolean().required())
            .authorization((allow) => [allow.group("MANAGEMENT")])
            .handler(a.handler.function(createUser)),

        editUser: a
            .mutation()
            .arguments({
                id: a.id().required(),
                expectedHours: a.integer(),
                email: a.email(),
                givenName: a.string(),
                managementStatusChange: a.ref("ManagementStatusChange"),
                userStatusChange: a.ref("UserStatusChange"),
                serviceStatusChange: a.ref("ServiceStatusChange"),
                serviceManagerStatusChange: a.ref("ServiceManagerStatusChange"),
                salary: a.integer(),
                department: a.ref("Departments"),
            })
            .returns(a.boolean().required())
            .authorization((allow) => [allow.group("MANAGEMENT")])
            .handler(a.handler.function(editUser)),

        deleteUser: a
            .mutation()
            .arguments({
                id: a.id().required(),
            })
            .returns(a.boolean().required())
            .authorization((allow) => [allow.group("MANAGEMENT")])
            .handler(a.handler.function(deleteUser)),

        getFullUser: a
            .query()
            .arguments({
                id: a.id().required(),
            })
            .returns(a.ref("getFullUserResponse"))
            .authorization((allow) => [allow.group("MANAGEMENT")])
            .handler(a.handler.function(getFullUser)),

        createNewTask: a
            .mutation()
            .arguments({
                totalHours: a.float().required(),
                title: a.string().required(),
                agency: a.ref("Agencies").required(),
                projectNumber: a.string(),
                invoiceNumber: a.string(),
                taskDate: a.datetime(),
                governorate: a.ref("IraqGovernorates").required(),
                outsideIraqLocation: a.string(),
                customerNumber: a.string(),
            })
            .returns(a.boolean().required())
            .authorization((allow) => [allow.authenticated()])
            .handler(a.handler.function(createNewTask)),

        editTask: a
            .mutation()
            .arguments({
                id: a.id().required(),
                totalHours: a.float(),
                title: a.string(),
                agency: a.ref("Agencies"),
                projectNumber: a.string(),
                invoiceNumber: a.string(),
                taskDate: a.datetime().required(),
                customerNumber: a.string().required(),
                governorate: a.ref("IraqGovernorates"),
                outsideIraqLocation: a.string(),
                isRemoveOutsideIraqLocation: a.boolean(),
            })
            .returns(a.boolean().required())
            .authorization((allow) => [allow.authenticated()])
            .handler(a.handler.function(editTask)),

        createExcel: a
            .query()
            .arguments({
                tables: a.ref("Tables").required().array(),
                startDate: a.datetime(),
                endDate: a.datetime(),
            })
            .returns(a.string().required())
            .authorization((allow) => [allow.group("MANAGEMENT")])
            .handler(a.handler.function(createExcel)),

        massImport: a
            .mutation()
            .arguments({
                excelPath: a.string().required(),
                isUpdateOldData: a.boolean().required(),
                importType: a.ref("MassImportTypes").required(),
            })
            .returns(a.boolean().required())
            .authorization((allow) => [allow.group("MANAGEMENT")])
            .handler(a.handler.function(massImport)),

        getMassImportTemplate: a
            .query()
            .arguments({
                template: a.ref("MassImportTemplates").required(),
            })
            .returns(a.string().required())
            .authorization((allow) => [allow.group("MANAGEMENT")])
            .handler(a.handler.function(getMassImportTemplate)),

        createActivityLogFunction: a
            .mutation()
            .arguments({
                action: a.ref("ActivityLogActions").required(),
                description: a.string().required(),
            })
            .authorization((allow) => [allow.authenticated()])
            .returns(a.boolean().required())
            .handler(a.handler.function(createActivityLog)),

        checkIfExcelDataValid: a
            .query()
            .arguments({
                importType: a.ref("MassImportTypes").required(),
                excelPath: a.string().required(),
            })
            .returns(a.ref("checkIfExcelDataValidResponse"))
            .authorization((allow) => [allow.group("MANAGEMENT")])
            .handler(a.handler.function(checkIfExcelDataValid)),
        getNewPackage: a
            .query()
            .arguments({
                version: a.string().required(),
            })
            .returns(a.ref("getNewPackageResponse"))
            .authorization((allow) => [allow.authenticated()])
            .handler(a.handler.function(getNewPackage)),

        // custom responses
        getFullUserResponse: a.customType({
            id: a.id().required(),
            name: a.string().required(),
            department: a.ref("Departments").required(),
            salary: a.integer().required(),
            expectedHours: a.integer().required(),
            email: a.email().required(),
            isManagement: a.boolean().required(),
            isServiceManager: a.boolean().required(),
            isEnabled: a.boolean().required(),
        }),

        checkIfExcelDataValidResponse: a.customType({
            isError: a.boolean().required(),
            errorMessage: a.string(),
        }),

        getNewPackageResponse: a.customType({
            isNewPackageAvailable: a.boolean().required(),
            packagePath: a.string(),
        }),

        // custom items
        serviceHubPart: a.customType({
            isPartReplaced: a.boolean().required(),
            partName: a.string().required(),
            partNumber: a.string().required(),
            quantity: a.integer().required(),
        }),

        deviceHistory: a.customType({
            customerName: a.string().required(),
            location: a.string().required(),
            startDate: a.datetime(),
            endDate: a.datetime().required(),
        }),

        photo: a.customType({
            path: a.string().required(),
            aspectRation: a.float().required(),
        }),

        feedback: a.customType({
            reliability: a.integer().required(),
            time: a.integer().required(),
            competence: a.integer().required(),
            accuracy: a.integer().required(),
            satisfaction: a.integer().required(),
            comment: a.string(),
        }),

        // enumeration
        Agencies: a.enum([
            "Beckman",
            "Leica",
            "BioRad",
            "Schmitz",
            "Dexis",
            "Bioron",
            "Spectro",
            "Molecular",
            "HqProject",
            "Other",
        ]),
        Departments: a.enum([
            "PrivateSales",
            "GovernmentSales",
            "OtherSales",
            "Management",
            "CustomerSupport",
            "Marketing",
            "Accounting",
            "TechnicalService",
            "MinistryOfHealthAffairs",
            "DebtCollection",
            "Legal",
        ]),
        IraqGovernorates: a.enum([
            "AlAnbar",
            "Babil",
            "Baghdad",
            "Basra",
            "DhiQar",
            "AlQadisiyyah",
            "Diyala",
            "Duhok",
            "Erbil",
            "Karbala",
            "Kirkuk",
            "Maysan",
            "Muthanna",
            "Najaf",
            "Nineveh",
            "SalahAlDin",
            "Sulaymaniyah",
            "Wasit",
            "Halabja",
            "OutsideIraq",
        ]),
        ManagementStatusChange: a.enum(["TO_MANAGEMENT", "REMOVE_MANAGEMENT"]),
        UserStatusChange: a.enum(["TO_ENABLE", "TO_DISABLE"]),
        ServiceStatusChange: a.enum(["TO_SERVICE", "REMOVE_SERVICE"]),
        ServiceManagerStatusChange: a.enum(["TO_SERVICEMANAGER", "REMOVE_SERVICEMANAGER"]),
        SortKey: a.enum(["sorted"]),
        Tables: a.enum(["tasks", "employees", "agencyTasks", "customers", "activityLogs"]),
        MassImportTypes: a.enum(["customers", "employees"]),
        MassImportTemplates: a.enum(["customers", "employees"]),
        ActivityLogActions: a.enum(["create", "update", "delete", "massImport", "export"]),
    })
    .authorization((allow) => [
        allow.resource(createUser),
        allow.resource(editUser),
        allow.resource(getFullUser),
        allow.resource(createNewTask),
        allow.resource(editTask),
        allow.resource(calculateAgenciesMonthly),
        allow.resource(createExcel),
        allow.resource(massImport),
        allow.resource(getMassImportTemplate),
        allow.resource(createActivityLog),
        allow.resource(checkIfExcelDataValid),
        allow.resource(getNewPackage),
        allow.resource(deleteUser),
    ]);

export type Schema = ClientSchema<typeof schema>;

export const data = defineData({
    schema,
});

and this is one of the function that fails

import type { Handler } from "aws-lambda";

import { PutObjectCommand, PutObjectCommandInput, S3Client } from "@aws-sdk/client-s3";

import { Sha256 } from "@aws-crypto/sha256-js";
import { defaultProvider } from "@aws-sdk/credential-provider-node";
import { HttpRequest } from "@aws-sdk/protocol-http";
import { SignatureV4 } from "@aws-sdk/signature-v4";
import fetch from "node-fetch";

import ExcelJS from "exceljs";

import type {
    createdAtType,
    fetchedGraphqlContentType,
    graphqlContentType,
    graphQlVariablesType,
} from "./types";

import {
    listActivityLogBySortKeyAndCreatedAt,
    listAgencyTasksBySortKeyAndCreatedAt,
    listCustomers,
    listEmployees,
    listTaskBySortKeyAndCreatedAt,
} from "../../../types/normalDepth/queries";

import type { Schema } from "../../data/resource";

const bucketName = process.env.STORAGE_BUCKET_NAME;
const region = process.env.AWS_REGION;
const GRAPHQL_ENDPOINT = new URL(process.env.AMPLIFY_DATA_GRAPHQL_ENDPOINT!);

const client = new S3Client({ region: region });

export const handler: Handler = async (
    event,
    context
): Promise<Schema["createExcel"]["returnType"]> => {
    console.log("event", event);
    console.log("context", context);
    console.log("environment variables", process.env);

    const { tables, startDate, endDate }: Schema["createExcel"]["args"] = event.arguments;

    let createdAtFilter: createdAtType = {};

    const graphqlContent: graphqlContentType = [];
    if (startDate && endDate) {
        createdAtFilter = {
            between: [startDate, endDate],
        };
    } else if (startDate) {
        createdAtFilter = {
            gt: startDate,
        };
    } else if (endDate) {
        createdAtFilter = {
            lt: endDate,
        };
    } else {
        createdAtFilter = undefined;
    }

    if (!tables || tables.length === 0) {
        console.error("No tables provided for export");
        throw new Error("No tables provided for export");
    }

    const globalLimit = 100_000;

    tables.forEach((table) => {
        let query: string;
        let variables: graphQlVariablesType;
        switch (table) {
            case "tasks":
                query = listTaskBySortKeyAndCreatedAt;
                variables = {
                    sortKey: "sorted",
                    createdAt: createdAtFilter,
                    limit: globalLimit,
                    sortDirection: "DESC",
                };
                graphqlContent.push(
                    getGraphQlContent(query, variables, "listTaskBySortKeyAndCreatedAt")
                );
                break;
            case "employees":
                query = listEmployees;
                variables = {
                    limit: globalLimit,
                    filter: {
                        createdAt: createdAtFilter,
                    },
                };
                graphqlContent.push(getGraphQlContent(query, variables, "listEmployees"));
                break;
            case "agencyTasks":
                query = listAgencyTasksBySortKeyAndCreatedAt;
                variables = {
                    sortKey: "sorted",
                    createdAt: createdAtFilter,
                    limit: globalLimit,
                    sortDirection: "DESC",
                };
                graphqlContent.push(
                    getGraphQlContent(query, variables, "listAgencyTasksBySortKeyAndCreatedAt")
                );
                break;
            case "customers":
                query = listCustomers;
                variables = {
                    limit: globalLimit,
                    filter: {
                        createdAt: createdAtFilter,
                    },
                };
                graphqlContent.push(getGraphQlContent(query, variables, "listCustomers"));
                break;
            case "activityLogs":
                query = listActivityLogBySortKeyAndCreatedAt;
                variables = {
                    limit: globalLimit,
                    sortKey: "sorted",
                    createdAt: createdAtFilter,
                    sortDirection: "DESC",
                };
                graphqlContent.push(
                    getGraphQlContent(query, variables, "listActivityLogBySortKeyAndCreatedAt")
                );
                break;
        }
    });

    const results = await Promise.all(graphqlContent);
    const excelFile = createExcelFile(results, tables);

    const excelFileName = `exported_data_${new Date().toISOString()}.xlsx`;
    const excelFilePath = `excels/${excelFileName}`;
    const isUploaded = await uploadToS3(excelFile, excelFilePath);
    if (!isUploaded) {
        throw new Error("Failed to upload Excel file");
    }
    return excelFilePath;
};

async function getGraphQlContent(
    query: string,
    variables: graphQlVariablesType,
    queryName: string
) {
    const requestToBeSigned = new HttpRequest({
        method: "POST",
        headers: {
            "Content-Type": "application/json",
            host: GRAPHQL_ENDPOINT.host,
        },
        hostname: GRAPHQL_ENDPOINT.host,
        body: JSON.stringify({ query: query, variables: variables }),
        path: GRAPHQL_ENDPOINT.pathname,
    });

    const signer = new SignatureV4({
        credentials: defaultProvider(),
        region: process.env.AWS_REGION!,
        service: "appsync",
        sha256: Sha256,
    });

    const signedRequest = await signer.sign(requestToBeSigned);

    const response = await fetch(GRAPHQL_ENDPOINT.toString(), {
        method: signedRequest.method,
        headers: signedRequest.headers,
        body: signedRequest.body,
    });

    const data: any = await response.json();
    if (data.errors) throw new Error(JSON.stringify(data.errors));
    return data.data[queryName].items;
}

function createExcelFile(
    graphqlContent: fetchedGraphqlContentType,
    tables: string[]
): ExcelJS.Workbook {
    const workbook = new ExcelJS.Workbook();
    workbook.creator = "Lab Consult Employees App";
    workbook.created = new Date();
    workbook.modified = new Date();
    workbook.lastModifiedBy = "Lab Consult Employees App";

    let workbookSheets: ExcelJS.Worksheet[] = [];
    graphqlContent.forEach((content, index) => {
        const sheet = workbook.addWorksheet(`Sheet ${index + 1} - ${tables[index]}`);
        workbookSheets.push(sheet);
    });

    workbook.worksheets.forEach((sheet, index) => {
        const content = graphqlContent[index];
        if (content.length === 0 || !content) {
            sheet.addRow(["No data available for this table"]);
            return;
        }

        let isHeaderSet = false;
        content.forEach((item) => {
            if (item === null || item === undefined) {
                return;
            } else if (item.__typename === "Task") {
                if (!isHeaderSet) {
                    sheet.columns = [
                        { header: "ID", key: "id", width: 20 },
                        { header: "Title", key: "title", width: 30 },
                        { header: "Employee Name", key: "employeeName", width: 30 },
                        { header: "Employee Department", key: "employeeDepartment", width: 30 },
                        { header: "Agency", key: "agency", width: 20 },
                        { header: "Total Hours", key: "totalHours", width: 15 },
                        { header: "Hourly Value", key: "hourlyValue", width: 15 },
                        { header: "Salary at Time", key: "salaryAtTime", width: 20 },
                        { header: "Project Number", key: "projectNumber", width: 20 },
                        { header: "Customer Name", key: "customerName", width: 20 },
                        { header: "Customer Number", key: "customerNumber", width: 20 },
                        { header: "Invoice Number", key: "invoiceNumber", width: 20 },
                        { header: "Task Date", key: "taskDate", width: 25 },
                        { header: "Created At", key: "createdAt", width: 25 },
                        { header: "Updated At", key: "updatedAt", width: 25 },
                    ];
                    isHeaderSet = true;
                }
                sheet.addRow({
                    id: item.id,
                    title: item.title,
                    employeeName: item.employee?.name || "N/A",
                    employeeDepartment: item.employee?.department || "N/A",
                    agency: item.agency,
                    totalHours: item.totalHours,
                    hourlyValue: item.hourlyValue,
                    salaryAtTime: item.salaryAtTime,
                    createdAt: item.createdAt,
                    updatedAt: item.updatedAt,
                    projectNumber: item.projectNumber || "N/A",
                    customerName: item.customer?.name || "N/A",
                    customerNumber: item.customer?.number || "N/A",
                    invoiceNumber: item.invoiceNumber || "N/A",
                    taskDate: item.taskDate,
                });
            } else if (item.__typename === "Employee") {
                if (!isHeaderSet) {
                    sheet.columns = [
                        { header: "ID", key: "id", width: 20 },
                        { header: "Name", key: "name", width: 30 },
                        { header: "Department", key: "department", width: 30 },
                        { header: "Expected Hours", key: "expectedHours", width: 15 },
                        { header: "Salary", key: "salary", width: 15 },
                        { header: "Created At", key: "createdAt", width: 25 },
                        { header: "Updated At", key: "updatedAt", width: 25 },
                    ];
                    isHeaderSet = true;
                }
                sheet.addRow({
                    id: item.id,
                    name: item.name,
                    department: item.department,
                    expectedHours: item.expectedHours,
                    salary: item.salary,
                    createdAt: item.createdAt,
                    updatedAt: item.updatedAt,
                });
            } else if (item.__typename === "AgencyTasks") {
                if (!isHeaderSet) {
                    sheet.columns = [
                        { header: "ID", key: "id", width: 20 },
                        { header: "Total Hours", key: "totalHours", width: 15 },
                        { header: "Total Cost", key: "totalCost", width: 15 },
                        { header: "Created At", key: "createdAt", width: 25 },
                        { header: "Updated At", key: "updatedAt", width: 25 },
                        { header: "Beckman Hours", key: "beckmanHours", width: 15 },
                        { header: "Beckman Cost", key: "beckmanCost", width: 15 },
                        { header: "BioRad Hours", key: "bioRadHours", width: 15 },
                        { header: "BioRad Cost", key: "bioRadCost", width: 15 },
                        { header: "Leica Hours", key: "leicaHours", width: 15 },
                        { header: "Leica Cost", key: "leicaCost", width: 15 },
                        { header: "Schmitz Hours", key: "schmitzHours", width: 15 },
                        { header: "Schmitz Cost", key: "schmitzCost", width: 15 },
                        { header: "Dexis Hours", key: "dexisHours", width: 15 },
                        { header: "Dexis Cost", key: "dexisCost", width: 15 },
                        { header: "Bioron Hours", key: "bioronHours", width: 15 },
                        { header: "Bioron Cost", key: "bioronCost", width: 15 },
                        { header: "Spectro Hours", key: "spectroHours", width: 15 },
                        { header: "Spectro Cost", key: "spectroCost", width: 15 },
                        { header: "Molecular Hours", key: "molecularHours", width: 15 },
                        { header: "Molecular Cost", key: "molecularCost", width: 15 },
                        { header: "HQ Project Hours", key: "hqProjectHours", width: 15 },
                        { header: "HQ Project Cost", key: "hqProjectCost", width: 15 },
                        { header: "Other Hours", key: "otherHours", width: 15 },
                        { header: "Other Cost", key: "otherCost", width: 15 },
                    ];
                    isHeaderSet = true;
                }
                sheet.addRow({
                    id: item.id,
                    totalHours: item.totalHours,
                    totalCost: item.totalCost,
                    createdAt: item.createdAt,
                    updatedAt: item.updatedAt,
                    beckmanHours: item.beckmanHours,
                    beckmanCost: item.beckmanCost,
                    bioRadHours: item.bioRadHours,
                    bioRadCost: item.bioRadCost,
                    leicaHours: item.leicaHours,
                    leicaCost: item.leicaCost,
                    schmitzHours: item.schmitzHours,
                    schmitzCost: item.schmitzCost,
                    dexisHours: item.dexisHours,
                    dexisCost: item.dexisCost,
                    bioronHours: item.bioronHours,
                    bioronCost: item.bioronCost,
                    spectroHours: item.spectroHours,
                    spectroCost: item.spectroCost,
                    molecularHours: item.molecularHours,
                    molecularCost: item.molecularCost,
                    hqProjectHours: item.hqProjectHours,
                    hqProjectCost: item.hqProjectCost,
                    otherHours: item.otherHours,
                    otherCost: item.otherCost,
                });
            } else if (item.__typename === "Customer") {
                if (!isHeaderSet) {
                    sheet.columns = [
                        { header: "Name", key: "name", width: 30 },
                        { header: "Number", key: "number", width: 20 },
                        { header: "City", key: "city", width: 20 },
                        { header: "Address", key: "address", width: 30 },
                        { header: "Created At", key: "createdAt", width: 25 },
                        { header: "Updated At", key: "updatedAt", width: 25 },
                    ];
                    isHeaderSet = true;
                }
                sheet.addRow({
                    name: item.name,
                    number: item.number,
                    city: item.city,
                    address: item.address || "N/A",
                    createdAt: item.createdAt,
                    updatedAt: item.updatedAt,
                });
            } else if (item.__typename === "ActivityLog") {
                if (!isHeaderSet) {
                    sheet.columns = [
                        { header: "ID", key: "id", width: 20 },
                        { header: "Employee", key: "employee", width: 30 },
                        { header: "Description", key: "description", width: 50 },
                        { header: "Action", key: "action", width: 30 },
                        { header: "Created At", key: "createdAt", width: 25 },
                    ];
                    isHeaderSet = true;
                }
                sheet.addRow({
                    id: item.id,
                    employee: item.employee?.name || "Error",
                    action: item.action,
                    description: item.description,
                    createdAt: item.createdAt,
                });
            }
        });
    });

    return workbook;
}

async function uploadToS3(excel: ExcelJS.Workbook, fileName: string): Promise<boolean> {
    try {
        const buffer = await excel.xlsx.writeBuffer();
        const uint8Array = new Uint8Array(buffer as ArrayBuffer);
        const input: PutObjectCommandInput = {
            Bucket: bucketName,
            Key: fileName,
            Body: uint8Array,
        };
        const command = new PutObjectCommand(input);
        const response = await client.send(command);
        console.log("Upload response:", response);
        return true;
    } catch (error) {
        console.error("Error uploading to S3:", error);
        return false;
    }
}

Even though the code seems correct, I still have no idea what is happening, for now, I disabled the createAt filter in the app, but hopefully this gets resolved soon!

[pahud]

Current vs Expected Behavior

Current Behavior

// This fails with KeyConditionExpression error
const variables = {
    sortKey: "sorted",
    createdAt: { between: [startDate, endDate] },
    limit: globalLimit,
    sortDirection: "DESC",
};
// Query: listActivityLogBySortKeyAndCreatedAt

Expected Behavior

// This should work correctly
const variables = {
    sortKey: "sorted", 
    createdAt: { between: [startDate, endDate] },
    limit: globalLimit,
    sortDirection: "DESC",
};
// Should generate proper DynamoDB KeyConditionExpression

---

Hi @Lychas,

Thank you for reporting this issue! You've identified a bug in Amplify Backend's GraphQL resolver generation for secondary indexes when filtering on createdAt with sort keys.

The issue occurs because the generated GraphQL resolver is incorrectly constructing the DynamoDB KeyConditionExpression when both sortKey (partition key) and createdAt (sort key) parameters are provided to queries like listActivityLogBySortKeyAndCreatedAt. This violates DynamoDB's constraint that KeyConditionExpressions must only contain one condition per key.

Root Cause: The resolver generation logic in the secondary index transformer is not properly handling the key structure for indexes defined with index("sortKey").sortKeys(["createdAt"]).

Workaround: As you've discovered, disabling the createdAt filter temporarily works, or you can use regular queries without secondary indexes for now.

Next Steps: This requires a fix in the GraphQL transformer packages, specifically in the index transformer resolver generation.

We encourage community contributions! If you're interested in submitting a PR to fix this issue, the problem is likely in the KeyConditionExpression construction logic for secondary indexes in the amplify-graphql-index-transformer package.

I'll label this as a bug and escalate it to the team for prioritization.

[Lychas]

Hello, @pahud
Has this bug been fixed yet?