     1	import { requirePackage } from 'hooked-api'
     2	import { createSchema } from 'json-rest-schema'
     3	import { createKnexTable, addKnexFields, alterKnexFields } from './lib/dbTablesOperations.js'
     4	import { buildFieldSelection, isNonDatabaseField } from './lib/querying-writing/knex-field-helpers.js'
     5	import { getForeignKeyFields } from './lib/querying-writing/field-utils.js'
     6	import { buildQuerySelection } from './lib/querying/knex-query-helpers-base.js'
     7	import { toJsonApiRecord, buildJsonApiResponse } from './lib/querying/knex-json-api-transformers-querying.js'
     8	import { processBelongsToRelationships } from './lib/writing/knex-json-api-transformers-writing.js'
     9	import { toJsonApiRecordWithBelongsTo } from './lib/querying-writing/knex-json-api-transformers.js'
    10	import { processIncludes } from './lib/querying/knex-process-includes.js'
    11	import { loadRelationshipIdentifiers } from './lib/querying/knex-relationship-includes.js'
    12	import {
    13	  polymorphicFiltersHook,
    14	  crossTableFiltersHook,
    15	  basicFiltersHook
    16	} from './lib/querying/knex-query-helpers.js'
    17	import { RestApiResourceError, RestApiValidationError } from '../../lib/rest-api-errors.js'
    18	import { supportsWindowFunctions, getDatabaseInfo } from './lib/querying-writing/database-capabilities.js'
    19	import { ERROR_SUBTYPES, DEFAULT_QUERY_LIMIT, DEFAULT_MAX_QUERY_LIMIT } from './lib/querying-writing/knex-constants.js'
    20	import {
    21	  calculatePaginationMeta,
    22	  generatePaginationLinks,
    23	  generateCursorPaginationLinks,
    24	  buildCursorMeta,
    25	  parseCursor
    26	} from './lib/querying/knex-pagination-helpers.js'
    27	import { getUrlPrefix } from './lib/querying/url-helpers.js'
    28	import { createStorageAdapter } from './lib/storage/storage-adapter.js'
    29	
    30	/**
    31	 * Strips non-database fields (computed and virtual) from attributes before database operations
    32	 * @param {Object} attributes - The attributes object
    33	 * @param {Object} schemaInfo - The schema info containing computed field definitions and schema structure
    34	 * @returns {Object} Attributes with computed and virtual fields removed
    35	 */
    36	const stripNonDatabaseFields = (attributes, schemaInfo) => {
    37	  if (!attributes || !schemaInfo) return attributes || {}
    38	
    39	  const { computed = {}, schemaStructure = {} } = schemaInfo
    40	  return Object.entries(attributes)
    41	    .filter(([key]) => {
    42	      // Remove computed fields
    43	      if (key in computed) return false
    44	      // Remove fields marked as virtual in the schema
    45	      const fieldDef = schemaStructure[key]
    46	      if (fieldDef && fieldDef.virtual === true) {
    47	        return false
    48	      }
    49	      return true
    50	    })
    51	    .reduce((acc, [key, val]) => ({ ...acc, [key]: val }), {})
    52	}
    53	
    54	export const RestApiKnexPlugin = {
    55	  name: 'rest-api-knex',
    56	  dependencies: ['rest-api'],
    57	
    58	  async install ({ helpers, vars, pluginOptions, api, log, scopes, addHook, addScopeMethod }) {
    59	    // Try to import knex dynamically
    60	    let knexLib
    61	    try {
    62	      knexLib = await import('knex')
    63	    } catch (e) {
    64	      requirePackage('knex', 'rest-api-knex',
    65	        'Knex.js is required for database operations. This is a peer dependency that allows you to control the version.')
    66	    }
    67	
    68	    // Get Knex instance from plugin options
    69	    const knexOptions = pluginOptions || {}
    70	    const knex = knexOptions.knex
    71	
    72	    // Expose Knex instance and helpers in a structured way
    73	    api.knex = {
    74	      instance: knex,
    75	      helpers: {}
    76	    }
    77	
    78	    const storageAdapters = new Map()
    79	
    80	    const getScopeStorageAdapter = (scopeName) => {
    81	      if (!scopeName) return null
    82	      const resource = api.resources?.[scopeName] || scopes?.[scopeName]
    83	      const schemaInfo = resource?.vars?.schemaInfo
    84	      if (!schemaInfo) return null
    85	
    86	      const cached = storageAdapters.get(scopeName)
    87	      if (cached && cached.schemaInfo === schemaInfo) {
    88	        return cached.adapter
    89	      }
    90	
    91	      const adapter = createStorageAdapter({ knex, schemaInfo })
    92	      storageAdapters.set(scopeName, { adapter, schemaInfo })
    93	      if (resource?.vars) {
    94	        resource.vars.storageAdapter = adapter
    95	      }
    96	      return adapter
    97	    }
    98	
    99	    api.knex.helpers.getStorageAdapter = getScopeStorageAdapter
   100	    helpers.getStorageAdapter = getScopeStorageAdapter
   101	
   102	    const createSelectTranslator = (adapter) => {
   103	      if (!adapter) return null
   104	      return (field, alias) => {
   105	        if (field === '*') {
   106	          return alias ? `${alias}.*` : '*'
   107	        }
   108	        const translated = adapter.translateColumn(field)
   109	        const result = translated || field
   110	        if (!alias) {
   111	          return result
   112	        }
   113	        if (result.includes('.')) {
   114	          return result
   115	        }
   116	        return `${alias}.${result}`
   117	      }
   118	    }
   119	
   120	    // Check database capabilities
   121	    const hasWindowFunctions = await supportsWindowFunctions(knex)
   122	    const dbInfo = await getDatabaseInfo(knex)
   123	
   124	    // Store capabilities in API instance for access throughout
   125	    api.knex.capabilities = {
   126	      windowFunctions: hasWindowFunctions,
   127	      dbInfo
   128	    }
   129	
   130	    log.info('Database capabilities detected:', {
   131	      database: dbInfo.client,
   132	      version: dbInfo.version,
   133	      windowFunctions: hasWindowFunctions
   134	    })
   135	
   136	    // Cross-table search functions are now imported directly and used with full signatures
   137	
   138	    /* ╔═════════════════════════════════════════════════════════════════════╗
   139	     * ║                  MAIN QUERY FILTERING HOOK                              ║
   140	     * ║  This is the heart of the filtering system. It processes searchSchema   ║
   141	     * ║  filters and builds SQL WHERE conditions with proper JOINs              ║
   142	     * ╚═════════════════════════════════════════════════════════════════════╝ */
   143	
   144	    // Register the three separate filter hooks
   145	    // Dependencies object for the hooks
   146	    const polymorphicFiltersHookParams = { log, scopes, knex, getStorageAdapter: getScopeStorageAdapter }
   147	
   148	    // Register in specific order: polymorphic → cross-table → basic
   149	    // This ensures proper field qualification when JOINs are present
   150	
   151	    // 1. Polymorphic filters (adds JOINs for polymorphic relationships)
   152	    addHook('knexQueryFiltering', 'polymorphicFiltersHook', {},
   153	      async (hookParams) => polymorphicFiltersHook(hookParams, polymorphicFiltersHookParams)
   154	    )
   155	
   156	    // 2. Cross-table filters (adds JOINs for cross-table fields)
   157	    addHook('knexQueryFiltering', 'crossTableFiltersHook', {},
   158	      async (hookParams) => crossTableFiltersHook(hookParams, polymorphicFiltersHookParams)
   159	    )
   160	
   161	    // 3. Basic filters (processes simple main table filters)
   162	    addHook('knexQueryFiltering', 'basicFiltersHook', {},
   163	      async (hookParams) => basicFiltersHook(hookParams, polymorphicFiltersHookParams)
   164	    )
   165	
   166	    // 3. Basic filters (processes simple main table filters)
   167	    addHook('release', 'releaseHook', {},
   168	      async ({ api }) => api.knex.instance.destroy()
   169	    )
   170	
   171	    // Helper scope method to get all schema-related information
   172	    addScopeMethod('createKnexTable', async ({ vars, scope, scopeName, scopeOptions, runHooks }) => {
   173	      // Create a filtered schema that excludes virtual fields
   174	      const schemaStructure = vars.schemaInfo.schemaStructure || {}
   175	      const filteredSchema = {}
   176	
   177	      // Copy only non-virtual fields to the filtered schema
   178	      Object.entries(schemaStructure).forEach(([fieldName, fieldDef]) => {
   179	        if (!fieldDef.virtual) {
   180	          filteredSchema[fieldName] = fieldDef
   181	        }
   182	      })
   183	
   184	      // Create schema object from filtered fields
   185	      const tableSchemaInstance = createSchema(filteredSchema)
   186	
   187	      await createKnexTable(api.knex.instance, vars.schemaInfo, tableSchemaInstance, scopeOptions)
   188	    })
   189	
   190	    // Helper scope method to alter existing fields in a table
   191	    addScopeMethod('alterKnexFields', async ({ vars, scope, scopeName, scopeOptions, runHooks, params }) => {
   192	    // Validate required parameters
   193	      if (!params.fields || typeof params.fields !== 'object') {
   194	        throw new Error('fields parameter is required for alterKnexFields')
   195	      }
   196	
   197	      await alterKnexFields(
   198	        api.knex.instance,
   199	        vars.schemaInfo.tableName,
   200	        params.fields,
   201	        params.options // Pass through any additional options
   202	      )
   203	    })
   204	
   205	    // Helper scope method to add a field to an existing table
   206	    addScopeMethod('addKnexFields', async ({ vars, scope, scopeName, scopeOptions, runHooks, params }) => {
   207	      // Create schema object from filtered fields
   208	      const partialTableSchema = createSchema(params.fields)
   209	
   210	      await addKnexFields(api.knex.instance, vars.schemaInfo.tableName, partialTableSchema)
   211	    })
   212	
   213	    helpers.newTransaction = async () => {
   214	      return knex.transaction()
   215	    }
   216	
   217	    /* ╔═════════════════════════════════════════════════════════════════════╗
   218	     * ║                    DATA OPERATION METHODS                           ║
   219	     * ║  Implementation of the storage interface required by REST API plugin║
   220	     * ╚═════════════════════════════════════════════════════════════════════╝ */
   221	
   222	    /**
   223	     * Checks if a resource exists in the database
   224	     *
   225	     * @param {Object} params - The parameters object
   226	     * @param {string} params.scopeName - The name of the resource scope (e.g., 'books', 'authors')
   227	     * @param {Object} params.context - The context object containing request-specific data
   228	     * @param {string|number} params.context.id - The ID of the resource to check for existence
   229	     * @param {Object} params.context.schemaInfo - Schema information for the resource
   230	     * @param {string} params.context.schemaInfo.tableName - The database table name (e.g., 'basic_books')
   231	     * @param {string} params.context.schemaInfo.idProperty - The primary key field name (e.g., 'id')
   232	     * @param {Object} params.context.db - Database connection (knex instance or transaction)
   233	     * @returns {Promise<boolean>} True if the resource exists, false otherwise
   234	     */
   235	    helpers.dataExists = async ({ scopeName, context }) => {
   236	      const storageAdapter = getScopeStorageAdapter(scopeName)
   237	      if (storageAdapter) {
   238	        context.storageAdapter = storageAdapter
   239	      }
   240	
   241	      const id = context.id
   242	
   243	      const tableName = storageAdapter?.getTableName?.() || context.schemaInfo.tableName
   244	      const idProperty = storageAdapter?.getIdColumn?.() || context.schemaInfo.idProperty
   245	      const db = context.db || api.knex.instance
   246	
   247	      log.debug(`[Knex] EXISTS ${tableName}/${id}`)
   248	
   249	      const selectClause = idProperty !== 'id' ? `${idProperty} as id` : 'id'
   250	
   251	      const record = await db(tableName)
   252	        .where(idProperty, id)
   253	        .select(selectClause)
   254	        .first()
   255	
   256	      return !!record
   257	    }
   258	
   259	    /**
   260	     * Retrieves a single resource by ID with support for sparse fieldsets and includes
   261	     *
   262	     * @param {Object} params - The parameters object
   263	     * @param {string} params.scopeName - The name of the resource scope (e.g., 'books', 'authors')
   264	     * @param {Object} params.context - The context object containing request-specific data
   265	     * @param {string|number} params.context.id - The ID of the resource to retrieve
   266	     * @param {Object} params.context.schemaInfo - Schema information for the resource
   267	     * @param {string} params.context.schemaInfo.tableName - The database table name (e.g., 'basic_books')
   268	     * @param {string} params.context.schemaInfo.idProperty - The primary key field name (e.g., 'id')
   269	     * @param {Object} params.context.schemaInfo.schemaInstance - The full schema definition for the resource
   270	     * @param {Object} params.context.db - Database connection (knex instance or transaction)
   271	     * @param {Object} [params.context.queryParams] - Query parameters for sparse fieldsets and includes
   272	     * @param {Object} [params.context.queryParams.fields] - Sparse fieldset selections
   273	     * @param {Array<string>} [params.context.queryParams.include] - Related resources to include
   274	     * @param {Object} [params.context.computedDependencies] - Set by function to track computed field dependencies
   275	     * @returns {Promise<Object>} JSON:API formatted response with data and optional included resources
   276	     * @throws {RestApiResourceError} When the resource is not found
   277	     */
   278	    helpers.dataGet = async ({ scopeName, context, runHooks }) => {
   279	      const storageAdapter = getScopeStorageAdapter(scopeName)
   280	      if (storageAdapter) {
   281	        context.storageAdapter = storageAdapter
   282	      }
   283	      const scope = api.resources[scopeName]
   284	      if (!scope) {
   285	        log.error('[DATA-GET] scope is undefined!', { scopeName, availableScopes: Object.keys(api.resources || {}) })
   286	        throw new Error(`Scope '${scopeName}' not found in api.resources`)
   287	      }
   288	      if (!scope.scopeName && !scope.name) {
   289	        log.debug('[DATA-GET] Scope structure:', {
   290	          scopeKeys: Object.keys(scope),
   291	          scopeName,
   292	          hasVars: !!scope.vars,
   293	          varKeys: scope.vars ? Object.keys(scope.vars) : []
   294	        })
   295	      }
   296	      const id = context.id
   297	      const tableName = storageAdapter?.getTableName?.() || context.schemaInfo.tableName
   298	      const idProperty = storageAdapter?.getIdColumn?.() || context.schemaInfo.idProperty
   299	      const db = context.db || api.knex.instance
   300	
   301	      log.debug(`[Knex] GET ${tableName}/${id}`)
   302	
   303	      // Build field selection for sparse fieldsets
   304	      // This determines which fields to SELECT from database
   305	      // and tracks dependencies needed for computed fields
   306	      const fieldSelectionInfo = await buildFieldSelection(
   307	        scope,
   308	        { context }
   309	      )
   310	
   311	      // Store dependency info in context for enrichAttributes
   312	      // Example: If user requests 'profit_margin' (computed), this might contain ['cost']
   313	      // The REST API plugin will use this to remove 'cost' from response if not requested
   314	      context.computedDependencies = fieldSelectionInfo.computedDependencies
   315	
   316	      // Build query - no filtering hooks for single records
   317	      // Permission checks will handle access control
   318	      let query = db(tableName).where(idProperty, id)
   319	
   320	      // Apply field selection
   321	      const selectTranslator = createSelectTranslator(storageAdapter)
   322	      query = buildQuerySelection(
   323	        query,
   324	        tableName,
   325	        fieldSelectionInfo.fieldsToSelect,
   326	        false,
   327	        selectTranslator ? { translateColumn: selectTranslator } : undefined
   328	      )
   329	
   330	      const record = await query.first()
   331	
   332	      if (!record) {
   333	        throw new RestApiResourceError(
   334	          'Resource not found',
   335	          {
   336	            subtype: ERROR_SUBTYPES.NOT_FOUND,
   337	            resourceType: scopeName,
   338	            resourceId: id
   339	          }
   340	        )
   341	      }
   342	
   343	      // Load relationship identifiers for all hasMany relationships
   344	      const records = [record] // Wrap in array for processing
   345	      await loadRelationshipIdentifiers(records, scopeName, scopes, db)
   346	
   347	      // Process includes
   348	      const included = await processIncludes(scope, records, {
   349	        log,
   350	        scopes,
   351	        knex,
   352	        context
   353	      })
   354	
   355	      // Build and return response
   356	      return buildJsonApiResponse(scope, records, included, true, scopeName, context)
   357	    }
   358	
   359	    /**
   360	     * Retrieves a single resource by ID with minimal processing (no includes or sparse fieldsets)
   361	     *
   362	     * @param {Object} params - The parameters object
   363	     * @param {string} params.scopeName - The name of the resource scope (e.g., 'books', 'authors')
   364	     * @param {Object} params.context - The context object containing request-specific data
   365	     * @param {string|number} params.context.id - The ID of the resource to retrieve
   366	     * @param {Object} params.context.schemaInfo - Schema information for the resource
   367	     * @param {string} params.context.schemaInfo.tableName - The database table name (e.g., 'basic_books')
   368	     * @param {string} params.context.schemaInfo.idProperty - The primary key field name (e.g., 'id')
   369	     * @param {Object} params.context.db - Database connection (knex instance or transaction)
   370	     * @returns {Promise<Object|null>} JSON:API formatted resource with belongsTo relationships, or null if not found
   371	     */
   372	    helpers.dataGetMinimal = async ({ scopeName, context }) => {
   373	      const storageAdapter = getScopeStorageAdapter(scopeName)
   374	      if (storageAdapter) {
   375	        context.storageAdapter = storageAdapter
   376	      }
   377	      const scope = api.resources[scopeName]
   378	      const id = context.id
   379	      const tableName = storageAdapter?.getTableName?.() || context.schemaInfo.tableName
   380	      const idProperty = storageAdapter?.getIdColumn?.() || context.schemaInfo.idProperty
   381	      const db = context.db || api.knex.instance
   382	
   383	      log.debug(`[Knex] GET_MINIMAL ${tableName}/${id}`)
   384	
   385	      // Build query - no filtering hooks for single records
   386	      // Permission checks will handle access control
   387	      let query = db(tableName).where(idProperty, id)
   388	
   389	      // Add alias if idProperty is not 'id'
   390	      if (idProperty !== 'id') {
   391	        query = query.select('*', `${idProperty} as id`)
   392	      }
   393	
   394	      // Execute query
   395	      const record = await query.first()
   396	
   397	      if (!record) {
   398	        return null
   399	      }
   400	
   401	      // Transform to JSON:API format with belongsTo relationships
   402	      return toJsonApiRecordWithBelongsTo(scope, record, scopeName)
   403	    }
   404	
   405	    /**
   406	     * Queries resources with support for filtering, sorting, pagination, sparse fieldsets, and includes
   407	     *
   408	     * @param {Object} params - The parameters object
   409	     * @param {string} params.scopeName - The name of the resource scope (e.g., 'books', 'authors')
   410	     * @param {Object} params.context - The context object containing request-specific data
   411	     * @param {Object} params.context.schemaInfo - Schema information for the resource
   412	     * @param {string} params.context.schemaInfo.tableName - The database table name (e.g., 'basic_books')
   413	     * @param {Object} params.context.schemaInfo.schemaInstance - The full schema definition for the resource
   414	     * @param {Object} params.context.schemaInfo.searchSchemaInstance - Search schema for filtering capabilities
   415	     * @param {string} params.context.schemaInfo.idProperty - The primary key field name (e.g., 'id')
   416	     * @param {Object} params.context.queryParams - Query parameters object
   417	     * @param {Object} [params.context.queryParams.filters] - Filter conditions
   418	     * @param {Array<string>} [params.context.queryParams.sort] - Sort fields (prefix with - for DESC)
   419	     * @param {Object} [params.context.queryParams.page] - Pagination parameters
   420	     * @param {number} [params.context.queryParams.page.size] - Page size
   421	     * @param {number} [params.context.queryParams.page.number] - Page number (offset pagination)
   422	     * @param {string} [params.context.queryParams.page.after] - Cursor for forward pagination
   423	     * @param {string} [params.context.queryParams.page.before] - Cursor for backward pagination
   424	     * @param {Array<string>} [params.context.queryParams.include] - Related resources to include
   425	     * @param {Object} [params.context.queryParams.fields] - Sparse fieldset selections
   426	     * @param {Object} params.context.db - Database connection (knex instance or transaction)
   427	     * @param {Array<string>} params.context.sortableFields - Array of fields that can be sorted
   428	     * @param {Object} [params.context.knexQuery] - Temporarily set during hooks for query building
   429	     * @param {Object} [params.context.computedDependencies] - Set by function to track computed field dependencies
   430	     * @param {Function} params.runHooks - Function to run hooks (e.g., 'knexQueryFiltering')
   431	     * @returns {Promise<Object>} JSON:API formatted response with data array, optional included resources, and pagination meta/links
   432	     */
   433	    helpers.dataQuery = async ({ scopeName, context, runHooks }) => {
   434	      const storageAdapter = getScopeStorageAdapter(scopeName)
   435	      if (storageAdapter) {
   436	        context.storageAdapter = storageAdapter
   437	      }
   438	      const scope = api.resources[scopeName]
   439	      const tableName = storageAdapter?.getTableName?.() || context.schemaInfo.tableName
   440	      const schemaInfo = context.schemaInfo
   441	      const queryParams = context.queryParams
   442	      const db = context.db || api.knex.instance
   443	      const sortableFields = context.sortableFields
   444	
   445	      log.trace('[DATA-QUERY] Starting dataQuery', { scopeName })
   446	      log.debug(`[Knex] QUERY ${tableName}`, queryParams)
   447	
   448	      // Build field selection for sparse fieldsets
   449	      // This determines which fields to SELECT from database
   450	      // and tracks dependencies needed for computed fields
   451	      const fieldSelectionInfo = await buildFieldSelection(
   452	        scope,
   453	        { context }
   454	      )
   455	
   456	      // Store dependency info in context for enrichAttributes
   457	      // Example: If user requests 'profit_margin' (computed), this might contain ['cost']
   458	      // The REST API plugin will use this to remove 'cost' from response if not requested
   459	      context.computedDependencies = fieldSelectionInfo.computedDependencies
   460	
   461	      // Start building query with table prefix (for JOIN support)
   462	      let query = db(tableName)
   463	
   464	      const selectTranslator = createSelectTranslator(storageAdapter)
   465	      query = buildQuerySelection(
   466	        query,
   467	        tableName,
   468	        fieldSelectionInfo.fieldsToSelect,
   469	        true,
   470	        selectTranslator ? { translateColumn: selectTranslator } : undefined
   471	      )
   472	
   473	      /* ═══════════════════════════════════════════════════════════════════
   474	       * FILTERING HOOKS
   475	       * This is where the magic happens. The knexQueryFiltering hook is called
   476	       * to apply all filter conditions. The searchSchemaFilter hook (registered
   477	       * above) will process the searchSchema and apply filters with JOINs.
   478	       *
   479	       * IMPORTANT: Each hook should wrap its conditions in query.where(function() {...})
   480	       * to ensure proper grouping and prevent accidental filter bypass.
   481	       * ═══════════════════════════════════════════════════════════════════ */
   482	
   483	      log.trace('[DATA-QUERY] Calling knexQueryFiltering hook', { hasQuery: !!query, hasFilters: !!queryParams.filters, scopeName, tableName })
   484	
   485	      log.trace('[DATA-QUERY] About to call runHooks', { hookName: 'knexQueryFiltering' })
   486	
   487	      log.trace('[DATA-QUERY] Storing query data in context before calling runHooks')
   488	
   489	      // YOU ARE HERE: pass 'sesrchSchemaInstance' in the context below
   490	
   491	      // Store the query data in context where hooks can access it
   492	      // This is the proper way to share data between methods and hooks
   493	      if (context) {
   494	        context.knexQuery = {
   495	          query,
   496	          filters: queryParams.filters,
   497	          schemaInfo,
   498	          scopeName,
   499	          tableName,
   500	          db,
   501	          adapter: storageAdapter,
   502	        }
   503	
   504	        log.trace('[DATA-QUERY] Stored data in context', { hasStoredData: !!context.knexQuery, filters: queryParams.filters })
   505	      }
   506	
   507	      await runHooks('knexQueryFiltering')
   508	
   509	      // Clean up after hook execution
   510	      if (context && context.knexQuery) {
   511	        delete context.knexQuery
   512	      }
   513	
   514	      log.trace('[DATA-QUERY] Finished knexQueryFiltering hook')
   515	
   516	      // Apply sorting directly (no hooks)
   517	      if (queryParams.sort && queryParams.sort.length > 0) {
   518	        queryParams.sort.forEach(sortField => {
   519	          const desc = sortField.startsWith('-')
   520	          const field = desc ? sortField.substring(1) : sortField
   521	
   522	          // Check if field is sortable
   523	          if (sortableFields && sortableFields.length > 0 && !sortableFields.includes(field)) {
   524	            log.warn(`Ignoring non-sortable field: ${field}`)
   525	            return // Skip non-sortable fields
   526	          }
   527	
   528	          // Map relationship names to actual database columns for sorting
   529	          // Check if this is a relationship field that needs to be mapped
   530	          let dbField = field
   531	          const searchField = schemaInfo.searchSchemaStructure?.[field]
   532	          if (searchField?.actualField) {
   533	            // This is a relationship field, use the actual database column
   534	            dbField = searchField.actualField
   535	          }
   536	          const translatedField = storageAdapter?.translateColumn
   537	            ? storageAdapter.translateColumn(dbField)
   538	            : dbField
   539	
   540	          query.orderBy(translatedField, desc ? 'desc' : 'asc')
   541	        })
   542	      }
   543	
   544	      // Apply pagination
   545	      // Check if page object has any actual pagination parameters
   546	      const hasPageParams = queryParams.page &&
   547	        (queryParams.page.size !== undefined ||
   548	         queryParams.page.number !== undefined ||
   549	         queryParams.page.after !== undefined ||
   550	         queryParams.page.before !== undefined)
   551	
   552	      if (hasPageParams) {
   553	        const requestedSize = queryParams.page.size || scope.vars.queryDefaultLimit || DEFAULT_QUERY_LIMIT
   554	        const pageSize = Math.min(
   555	          requestedSize,
   556	          scope.vars.queryMaxLimit || DEFAULT_MAX_QUERY_LIMIT
   557	        )
   558	
   559	        // Validate page size
   560	        if (requestedSize <= 0) {
   561	          throw new RestApiValidationError(
   562	            'Page size must be greater than 0',
   563	            {
   564	              fields: ['page.size'],
   565	              violations: [{
   566	                field: 'page.size',
   567	                rule: 'min_value',
   568	                message: 'Page size must be a positive number'
   569	              }]
   570	            }
   571	          )
   572	        }
   573	
   574	        // Offset-based pagination
   575	        if (queryParams.page.number !== undefined) {
   576	          const pageNumber = queryParams.page.number || 1
   577	          query
   578	            .limit(pageSize)
   579	            .offset((pageNumber - 1) * pageSize)
   580	        }
   581	        // Cursor-based pagination
   582	        else if (queryParams.page.after || queryParams.page.before) {
   583	          // Fetch one extra record to determine if there are more
   584	          query.limit(pageSize + 1)
   585	
   586	          // Get the configured ID property
   587	          const idProperty = context.schemaInfo.idProperty || 'id'
   588	
   589	          if (queryParams.page.after) {
   590	            let cursorData
   591	            try {
   592	              cursorData = parseCursor(queryParams.page.after)
   593	            } catch (error) {
   594	              throw new RestApiValidationError(
   595	                'Invalid cursor format in page[after] parameter',
   596	                {
   597	                  fields: ['page.after'],
   598	                  violations: [{
   599	                    field: 'page.after',
   600	                    rule: 'invalid_cursor',
   601	                    message: 'The cursor value is not valid'
   602	                  }]
   603	                }
   604	              )
   605	            }
   606	
   607	            // Build sort fields array with directions
   608	            const sortFields = []
   609	            if (queryParams.sort && queryParams.sort.length > 0) {
   610	              queryParams.sort.forEach(sortField => {
   611	                const desc = sortField.startsWith('-')
   612	                const field = desc ? sortField.substring(1) : sortField
   613	                sortFields.push({ field, direction: desc ? 'DESC' : 'ASC' })
   614	              })
   615	            } else if (scope.vars.defaultSort) {
   616	              sortFields.push({
   617	                field: scope.vars.defaultSort.field || idProperty,
   618	                direction: scope.vars.defaultSort.direction || 'ASC'
   619	              })
   620	            } else {
   621	              sortFields.push({ field: idProperty, direction: 'ASC' })
   622	            }
   623	
   624	            // Build compound WHERE clause for multi-field cursor
   625	            query.where(function () {
   626	              sortFields.forEach((sortInfo, index) => {
   627	                const { field, direction } = sortInfo
   628	                const qualifiedField = `${tableName}.${field}`
   629	                const cursorValue = cursorData[field]
   630	
   631	                if (cursorValue === undefined) {
   632	                  log.warn(`Cursor missing value for sort field: ${field}`)
   633	                  return
   634	                }
   635	
   636	                // Build condition for this level and all previous levels
   637	                this.orWhere(function () {
   638	                  // All previous fields must be equal
   639	                  for (let i = 0; i < index; i++) {
   640	                    const prevField = sortFields[i].field
   641	                    const prevQualifiedField = `${tableName}.${prevField}`
   642	                    const prevValue = cursorData[prevField]
   643	                    if (prevValue !== undefined) {
   644	                      this.where(prevQualifiedField, '=', prevValue)
   645	                    }
   646	                  }
   647	
   648	                  // Current field must be greater/less than cursor value
   649	                  if (direction === 'DESC') {
   650	                    this.where(qualifiedField, '<', cursorValue)
   651	                  } else {
   652	                    this.where(qualifiedField, '>', cursorValue)
   653	                  }
   654	                })
   655	              })
   656	            })
   657	          } else if (queryParams.page.before) {
   658	            let cursorData
   659	            try {
   660	              cursorData = parseCursor(queryParams.page.before)
   661	            } catch (error) {
   662	              throw new RestApiValidationError(
   663	                'Invalid cursor format in page[before] parameter',
   664	                {
   665	                  fields: ['page.before'],
   666	                  violations: [{
   667	                    field: 'page.before',
   668	                    rule: 'invalid_cursor',
   669	                    message: 'The cursor value is not valid'
   670	                  }]
   671	                }
   672	              )
   673	            }
   674	
   675	            // Build sort fields array with directions
   676	            const sortFields = []
   677	            if (queryParams.sort && queryParams.sort.length > 0) {
   678	              queryParams.sort.forEach(sortField => {
   679	                const desc = sortField.startsWith('-')
   680	                const field = desc ? sortField.substring(1) : sortField
   681	                sortFields.push({ field, direction: desc ? 'DESC' : 'ASC' })
   682	              })
   683	            } else if (scope.vars.defaultSort) {
   684	              sortFields.push({
   685	                field: scope.vars.defaultSort.field || idProperty,
   686	                direction: scope.vars.defaultSort.direction || 'ASC'
   687	              })
   688	            } else {
   689	              sortFields.push({ field: idProperty, direction: 'ASC' })
   690	            }
   691	
   692	            // Build compound WHERE clause for multi-field cursor (reversed for before)
   693	            query.where(function () {
   694	              sortFields.forEach((sortInfo, index) => {
   695	                const { field, direction } = sortInfo
   696	                const qualifiedField = `${tableName}.${field}`
   697	                const cursorValue = cursorData[field]
   698	
   699	                if (cursorValue === undefined) {
   700	                  log.warn(`Cursor missing value for sort field: ${field}`)
   701	                  return
   702	                }
   703	
   704	                // Build condition for this level and all previous levels
   705	                this.orWhere(function () {
   706	                  // All previous fields must be equal
   707	                  for (let i = 0; i < index; i++) {
   708	                    const prevField = sortFields[i].field
   709	                    const prevQualifiedField = `${tableName}.${prevField}`
   710	                    const prevValue = cursorData[prevField]
   711	                    if (prevValue !== undefined) {
   712	                      this.where(prevQualifiedField, '=', prevValue)
   713	                    }
   714	                  }
   715	
   716	                  // Current field must be less/greater than cursor value (reversed)
   717	                  if (direction === 'DESC') {
   718	                    this.where(qualifiedField, '>', cursorValue)
   719	                  } else {
   720	                    this.where(qualifiedField, '<', cursorValue)
   721	                  }
   722	                })
   723	              })
   724	            })
   725	          }
   726	        }
   727	        // Default pagination if only size is specified - treat as cursor-based for "load more"
   728	        else {
   729	          // Fetch one extra to detect hasMore
   730	          query.limit(pageSize + 1)
   731	        }
   732	      } else {
   733	        // No pagination params provided - apply default limit
   734	        const defaultLimit = scope.vars.queryDefaultLimit || DEFAULT_QUERY_LIMIT
   735	        query.limit(defaultLimit)
   736	      }
   737	
   738	      // Execute query
   739	      const records = await query
   740	
   741	      // Store query string for response building
   742	      const queryParts = []
   743	      Object.entries(queryParams).forEach(([key, value]) => {
   744	        if (Array.isArray(value)) {
   745	          // Handle arrays (like sort, include)
   746	          if (value.length > 0) {
   747	            queryParts.push(`${key}=${value.map(v => encodeURIComponent(v)).join(',')}`)
   748	          }
   749	        } else if (typeof value === 'object' && value !== null) {
   750	          // Handle nested objects (like filters, fields, page)
   751	          Object.entries(value).forEach(([subKey, subValue]) => {
   752	            if (subValue !== undefined && subValue !== null) {
   753	              if (typeof subValue === 'object' && !Array.isArray(subValue)) {
   754	                // Handle deeply nested objects (like filters[country][code])
   755	                Object.entries(subValue).forEach(([subSubKey, subSubValue]) => {
   756	                  if (subSubValue !== undefined && subSubValue !== null) {
   757	                    queryParts.push(`${key}[${subKey}][${subSubKey}]=${encodeURIComponent(subSubValue)}`)
   758	                  }
   759	                })
   760	              } else {
   761	                queryParts.push(`${key}[${subKey}]=${encodeURIComponent(subValue)}`)
   762	              }
   763	            }
   764	          })
   765	        } else if (value !== undefined && value !== null) {
   766	          queryParts.push(`${key}=${encodeURIComponent(value)}`)
   767	        }
   768	      })
   769	
   770	      // Initialize returnMeta namespace for thread-safe metadata
   771	      context.returnMeta = context.returnMeta || {}
   772	      context.returnMeta.queryString = queryParts.length > 0 ? `?${queryParts.join('&')}` : ''
   773	
   774	      // Execute count query for pagination if offset-based pagination is used
   775	      if (queryParams.page?.number !== undefined || (queryParams.page?.size !== undefined && !queryParams.page?.after && !queryParams.page?.before)) {
   776	        const page = parseInt(queryParams.page?.number) || 1
   777	        const pageSize = parseInt(queryParams.page?.size) || scope.vars.queryDefaultLimit || DEFAULT_QUERY_LIMIT
   778	
   779	        // Only execute count query if enabled
   780	        if (scope.vars.enablePaginationCounts) {
   781	          // Build count query with same filters as main query
   782	          const countQuery = db(tableName)
   783	
   784	          // Apply filters through hooks (same as main query)
   785	          if (queryParams.filters && Object.keys(queryParams.filters).length > 0) {
   786	            // Store query data in context for hooks
   787	            if (context) {
   788	              context.knexQuery = {
   789	                query: countQuery,
   790	                filters: queryParams.filters,
   791	                scopeName,
   792	                tableName,
   793	                schemaInfo,
   794	                db
   795	              }
   796	            }
   797	
   798	            // Run the same filtering hooks
   799	            await runHooks('knexQueryFiltering')
   800	
   801	            // Clean up
   802	            if (context && context.knexQuery) {
   803	              delete context.knexQuery
   804	            }
   805	          }
   806	
   807	          // Get total count
   808	          const countResult = await countQuery.count('* as total').first()
   809	          const total = parseInt(countResult.total)
   810	
   811	          // Calculate pagination metadata with total
   812	          context.returnMeta.paginationMeta = calculatePaginationMeta(total, page, pageSize)
   813	        } else {
   814	          // Without count, we can still provide basic pagination info
   815	          context.returnMeta.paginationMeta = {
   816	            page,
   817	            pageSize
   818	            // No total, pageCount, or hasMore when counts are disabled
   819	          }
   820	        }
   821	
   822	        // Generate links
   823	        const urlPrefix = getUrlPrefix(context, scope)
   824	        context.returnMeta.paginationLinks = generatePaginationLinks(
   825	          urlPrefix,
   826	          scopeName,
   827	          queryParams,
   828	          context.returnMeta.paginationMeta
   829	        )
   830	      }
   831	
   832	      // Handle cursor-based pagination meta
   833	      // Generate cursor metadata when using cursor parameters OR when only size is specified (no page number)
   834	      if (queryParams.page?.after || queryParams.page?.before ||
   835	          (queryParams.page?.size && queryParams.page?.number === undefined)) {
   836	        const pageSize = parseInt(queryParams.page?.size) || scope.vars.queryDefaultLimit || DEFAULT_QUERY_LIMIT
   837	
   838	        // Check if there are more records
   839	        // We fetched pageSize + 1 records to detect if there are more
   840	        const hasMore = records.length > pageSize
   841	
   842	        // Remove the extra record if present
   843	        if (hasMore) {
   844	          records.pop()
   845	        }
   846	
   847	        // Determine sort fields for cursor
   848	        const idProperty = context.schemaInfo.idProperty || 'id'
   849	        let sortFields = [idProperty]
   850	        if (queryParams.sort && queryParams.sort.length > 0) {
   851	          sortFields = queryParams.sort.map(s => s.startsWith('-') ? s.substring(1) : s)
   852	        }
   853	
   854	        context.returnMeta.paginationMeta = buildCursorMeta(records, pageSize, hasMore, sortFields)
   855	        const urlPrefix = getUrlPrefix(context, scope)
   856	        context.returnMeta.paginationLinks = generateCursorPaginationLinks(
   857	          urlPrefix,
   858	          scopeName,
   859	          queryParams,
   860	          records,
   861	          pageSize,
   862	          hasMore,
   863	          sortFields
   864	        )
   865	      }
   866	
   867	      // Load relationship identifiers for all hasMany relationships
   868	      await loadRelationshipIdentifiers(records, scopeName, scopes, db)
   869	
   870	      // Process includes
   871	      const included = await processIncludes(scope, records, {
   872	        log,
   873	        scopes,
   874	        knex,
   875	        context,
   876	        api
   877	      })
   878	
   879	      // Build and return response
   880	      return buildJsonApiResponse(scope, records, included, false, scopeName, context)
   881	    }
   882	
   883	    /**
   884	     * Creates a new resource in the database
   885	     *
   886	     * @param {Object} params - The parameters object
   887	     * @param {string} params.scopeName - The name of the resource scope (e.g., 'books', 'authors')
   888	     * @param {Object} params.context - The context object containing request-specific data
   889	     * @param {Object} params.context.schemaInfo - Schema information for the resource
   890	     * @param {string} params.context.schemaInfo.tableName - The database table name (e.g., 'basic_books')
   891	     * @param {string} params.context.schemaInfo.idProperty - The primary key field name (e.g., 'id')
   892	     * @param {Object} params.context.schemaInfo.schemaInstance - The full schema definition for the resource
   893	     * @param {Object} params.context.db - Database connection (knex instance or transaction)
   894	     * @param {Object} params.context.inputRecord - JSON:API formatted input record
   895	     * @param {Object} params.context.inputRecord.data - The resource data
   896	     * @param {Object} params.context.inputRecord.data.attributes - The resource attributes to insert
   897	     * @returns {Promise<string|number>} The ID of the newly created resource
   898	     */
   899	    helpers.dataPost = async ({ scopeName, context }) => {
   900	      const storageAdapter = getScopeStorageAdapter(scopeName)
   901	      if (storageAdapter) {
   902	        context.storageAdapter = storageAdapter
   903	      }
   904	      const scope = api.resources[scopeName]
   905	      const tableName = storageAdapter?.getTableName?.() || context.schemaInfo.tableName
   906	      const idProperty = storageAdapter?.getIdColumn?.() || context.schemaInfo.idProperty
   907	      const db = context.db || api.knex.instance
   908	      const inputRecord = context.inputRecord
   909	
   910	      log.debug(`[Knex] POST ${tableName}`, inputRecord)
   911	
   912	      // Extract attributes from JSON:API format
   913	      const attributes = inputRecord.data.attributes
   914	
   915	      // Strip non-database fields (computed and virtual) before insert
   916	      const dbAttributes = stripNonDatabaseFields(attributes, context.schemaInfo)
   917	
   918	      // Insert and get the new ID
   919	      const result = await db(tableName).insert(dbAttributes).returning(idProperty)
   920	
   921	      // Extract the ID value (SQLite returns array of objects)
   922	      const id = result[0]?.[idProperty] || result[0]
   923	      return id
   924	    }
   925	
   926	    /**
   927	     * Replaces an entire resource (PUT operation) or creates it with a specific ID
   928	     *
   929	     * @param {Object} params - The parameters object
   930	     * @param {string} params.scopeName - The name of the resource scope (e.g., 'books', 'authors')
   931	     * @param {Object} params.context - The context object containing request-specific data
   932	     * @param {string|number} params.context.id - The ID of the resource to replace or create
   933	     * @param {Object} params.context.schemaInfo - Schema information for the resource
   934	     * @param {string} params.context.schemaInfo.tableName - The database table name (e.g., 'basic_books')
   935	     * @param {string} params.context.schemaInfo.idProperty - The primary key field name (e.g., 'id')
   936	     * @param {Object} params.context.schemaInfo.schemaInstance - The full schema definition for the resource
   937	     * @param {Object} params.context.db - Database connection (knex instance or transaction)
   938	     * @param {Object} params.context.inputRecord - JSON:API formatted input record
   939	     * @param {Object} params.context.inputRecord.data - The resource data
   940	     * @param {Object} [params.context.inputRecord.data.attributes] - The resource attributes
   941	     * @param {Object} [params.context.inputRecord.data.relationships] - The resource relationships (processed for foreign keys)
   942	     * @param {boolean} params.context.isCreate - Whether this is a create operation (true) or update (false)
   943	     * @returns {Promise<void>} Resolves when the operation is complete
   944	     * @throws {RestApiResourceError} When updating and the resource is not found
   945	     */
   946	    helpers.dataPut = async ({ scopeName, context }) => {
   947	      const storageAdapter = getScopeStorageAdapter(scopeName)
   948	      if (storageAdapter) {
   949	        context.storageAdapter = storageAdapter
   950	      }
   951	      const scope = api.resources[scopeName]
   952	      const id = context.id
   953	      const tableName = storageAdapter?.getTableName?.() || context.schemaInfo.tableName
   954	      const idProperty = storageAdapter?.getIdColumn?.() || context.schemaInfo.idProperty
   955	      const db = context.db || api.knex.instance
   956	      const inputRecord = context.inputRecord
   957	      const isCreate = context.isCreate
   958	
   959	      log.debug(`[Knex] PUT ${tableName}/${id} (isCreate: ${context.isCreate})`)
   960	
   961	      // Extract attributes and process relationships using helper
   962	      const attributes = inputRecord.data.attributes || {}
   963	      const foreignKeyUpdates = processBelongsToRelationships(scope, { context })
   964	      const mergedAttributes = { ...attributes, ...foreignKeyUpdates }
   965	
   966	      // Strip non-database fields (computed and virtual) before database operation
   967	      const finalAttributes = stripNonDatabaseFields(mergedAttributes, context.schemaInfo)
   968	
   969	      // Map 'id' to actual idProperty if needed (for PUT with specific ID)
   970	      if (idProperty !== 'id' && inputRecord.data.id) {
   971	        finalAttributes[idProperty] = inputRecord.data.id
   972	      }
   973	
   974	      if (isCreate) {
   975	        // Create mode - insert new record with specified ID
   976	        const recordData = {
   977	          ...finalAttributes,
   978	          [idProperty]: id
   979	        }
   980	
   981	        await db(tableName).insert(recordData)
   982	      } else {
   983	        // Update mode - check if record exists first
   984	        const exists = await db(tableName)
   985	          .where(idProperty, id)
   986	          .first()
   987	
   988	        if (!exists) {
   989	          throw new RestApiResourceError(
   990	            'Resource not found',
   991	            {
   992	              subtype: ERROR_SUBTYPES.NOT_FOUND,
   993	              resourceType: scopeName,
   994	              resourceId: id
   995	            }
   996	          )
   997	        }
   998	
   999	        // Remove the idProperty from attributes to prevent updating the primary key
  1000	        delete finalAttributes[idProperty]
  1001	
  1002	        // Update the record (replace all fields)
  1003	        if (Object.keys(finalAttributes).length > 0) {
  1004	          await db(tableName)
  1005	            .where(idProperty, id)
  1006	            .update(finalAttributes)
  1007	        }
  1008	      }
  1009	    }
  1010	
  1011	    /**
  1012	     * Partially updates a resource (PATCH operation)
  1013	     *
  1014	     * @param {Object} params - The parameters object
  1015	     * @param {string} params.scopeName - The name of the resource scope (e.g., 'books', 'authors')
  1016	     * @param {Object} params.context - The context object containing request-specific data
  1017	     * @param {string|number} params.context.id - The ID of the resource to update
  1018	     * @param {Object} params.context.schemaInfo - Schema information for the resource
  1019	     * @param {string} params.context.schemaInfo.tableName - The database table name (e.g., 'basic_books')
  1020	     * @param {string} params.context.schemaInfo.idProperty - The primary key field name (e.g., 'id')
  1021	     * @param {Object} params.context.schemaInfo.schemaInstance - The full schema definition for the resource
  1022	     * @param {Object} params.context.db - Database connection (knex instance or transaction)
  1023	     * @param {Object} params.context.inputRecord - JSON:API formatted input record with partial updates
  1024	     * @param {Object} params.context.inputRecord.data - The resource data
  1025	     * @param {Object} [params.context.inputRecord.data.attributes] - The resource attributes to update
  1026	     * @param {Object} [params.context.inputRecord.data.relationships] - The resource relationships (processed for foreign keys)
  1027	     * @returns {Promise<void>} Resolves when the update is complete
  1028	     * @throws {RestApiResourceError} When the resource is not found
  1029	     */
  1030	    helpers.dataPatch = async ({ scopeName, context }) => {
  1031	      const storageAdapter = getScopeStorageAdapter(scopeName)
  1032	      if (storageAdapter) {
  1033	        context.storageAdapter = storageAdapter
  1034	      }
  1035	      const scope = api.resources[scopeName]
  1036	      const id = context.id
  1037	      const tableName = storageAdapter?.getTableName?.() || context.schemaInfo.tableName
  1038	      const idProperty = storageAdapter?.getIdColumn?.() || context.schemaInfo.idProperty
  1039	      const db = context.db || api.knex.instance
  1040	      const inputRecord = context.inputRecord
  1041	
  1042	      log.debug(`[Knex] PATCH ${tableName}/${id}`)
  1043	
  1044	      // Check if record exists
  1045	      const exists = await db(tableName)
  1046	        .where(idProperty, id)
  1047	        .first()
  1048	
  1049	      if (!exists) {
  1050	        throw new RestApiResourceError(
  1051	          'Resource not found',
  1052	          {
  1053	            subtype: ERROR_SUBTYPES.NOT_FOUND,
  1054	            resourceType: scopeName,
  1055	            resourceId: id
  1056	          }
  1057	        )
  1058	      }
  1059	
  1060	      // Extract attributes and process relationships using helper
  1061	      const attributes = inputRecord.data.attributes || {}
  1062	      const foreignKeyUpdates = processBelongsToRelationships(scope, { context })
  1063	      const mergedAttributes = { ...attributes, ...foreignKeyUpdates }
  1064	
  1065	      // Strip non-database fields (computed and virtual) before database operation
  1066	      const finalAttributes = stripNonDatabaseFields(mergedAttributes, context.schemaInfo)
  1067	
  1068	      log.debug('[Knex] PATCH finalAttributes:', finalAttributes)
  1069	
  1070	      // Remove the idProperty from attributes to prevent updating the primary key
  1071	      delete finalAttributes[idProperty]
  1072	
  1073	      // Update only if there are changes
  1074	      if (Object.keys(finalAttributes).length > 0) {
  1075	        await db(tableName)
  1076	          .where(idProperty, id)
  1077	          .update(finalAttributes)
  1078	      }
  1079	    }
  1080	
  1081	    /**
  1082	     * Deletes a resource from the database
  1083	     *
  1084	     * @param {Object} params - The parameters object
  1085	     * @param {string} params.scopeName - The name of the resource scope (e.g., 'books', 'authors')
  1086	     * @param {Object} params.context - The context object containing request-specific data
  1087	     * @param {string|number} params.context.id - The ID of the resource to delete
  1088	     * @param {Object} params.context.schemaInfo - Schema information for the resource
  1089	     * @param {string} params.context.schemaInfo.tableName - The database table name (e.g., 'basic_books')
  1090	     * @param {string} params.context.schemaInfo.idProperty - The primary key field name (e.g., 'id')
  1091	     * @param {Object} params.context.db - Database connection (knex instance or transaction)
  1092	     * @returns {Promise<Object>} Returns { success: true } when deletion is successful
  1093	     * @throws {RestApiResourceError} When the resource is not found
  1094	     */
  1095	    helpers.dataDelete = async ({ scopeName, context }) => {
  1096	      const storageAdapter = getScopeStorageAdapter(scopeName)
  1097	      if (storageAdapter) {
  1098	        context.storageAdapter = storageAdapter
  1099	      }
  1100	      const scope = api.resources[scopeName]
  1101	      const id = context.id
  1102	
  1103	      const tableName = storageAdapter?.getTableName?.() || context.schemaInfo.tableName
  1104	      const idProperty = storageAdapter?.getIdColumn?.() || context.schemaInfo.idProperty
  1105	      const db = context.db || api.knex.instance
  1106	
  1107	      log.debug(`[Knex] DELETE ${tableName}/${id}`)
  1108	
  1109	      // Check if record exists
  1110	      const exists = await db(tableName)
  1111	        .where(idProperty, id)
  1112	        .first()
  1113	
  1114	      if (!exists) {
  1115	        throw new RestApiResourceError(
  1116	          'Resource not found',
  1117	          {
  1118	            subtype: ERROR_SUBTYPES.NOT_FOUND,
  1119	            resourceType: scopeName,
  1120	            resourceId: id
  1121	          }
  1122	        )
  1123	      }
  1124	
  1125	      // Delete the record
  1126	      await db(tableName)
  1127	        .where(idProperty, id)
  1128	        .delete()
  1129	
  1130	      return { success: true }
  1131	    }
  1132	
  1133	    log.info('RestApiKnexPlugin installed - basic CRUD operations ready')
  1134	  }
  1135	}
