Item-history-update needs to be updated to work with MetaDB

[jeremythuff]

In item-history-update/nodes/connect.json:

• The name field needs to be refactored "Connect LDP" -> "Connect MetaDB"
• 'ldp' in variable references needs to be refactored to 'metadb' (ldp-url, ldp-user, ldp-password)
• designation field needs to be changed from ldp

In item-history-update/nodes/disconnect.json:

• The name field needs to be refactored "Disconnect LDP" -> "Disconnect MetaDB"
• designation field needs to be changed from ldp

books-call-number/nodes/js/buildQuery.js

• folio_reporting.loans_items l_items needs to be refactored to folio_derived.loans_items l_items
• PUBLIC.circulation_check_ins needs to be refactored to folio_circulation.check_in

[rmathew1011]

• folio_reporting.loans_items l_items needs to be refactored to folio_derived.loans_items l_items - found in
  item-history-update/nodes/initialQuery.json
• PUBLIC.circulation_check_ins needs to be refactored to folio_circulation.check_in- found in
  item-history-update/nodes/initialQuery.json

[rmathew1011]

update - working query:

SELECT
  item_id,
  hist_charges,
  hist_browses,
  Max(new_date) AS new_date,
  Sum(new_charges) AS new_charges,
  Sum(new_browses) AS new_browses,
  Max(last_transaction)
FROM
  (
    SELECT
      l_items.item_id AS item_id,
      hist.item_id AS history_id,
      CASE
        WHEN Cast(
          loan_return_date at time zone 'America/Chicago' AS DATE
        ) IS NULL THEN Cast(loan_date at time zone 'America/Chicago' AS DATE)
        ELSE Cast(
          loan_return_date at time zone 'America/Chicago' AS DATE
        )
      END AS new_date,
      hist.hist_charges AS hist_charges,
      hist.hist_browses AS hist_browses,
      CASE
        WHEN Cast(
          loan_return_date at time zone 'America/Chicago' AS DATE
        ) IS NULL THEN 0
        ELSE 1
      END AS new_charges,
      0 AS new_browses,
      last_transaction
    FROM
      folio_derived.loans_items l_items
      LEFT JOIN mis.item_history hist ON l_items.item_id::uuid = hist.item_id::uuid
    WHERE
      (
        Cast(loan_date at time zone 'America/Chicago' AS DATE) > hist.last_transaction
        OR Cast(
          loan_return_date at time zone 'America/Chicago' AS DATE
        ) > last_transaction
        OR hist.item_id IS NULL
      )
    UNION ALL
    SELECT
      (cci."jsonb" ->> 'itemId')::uuid AS item_id,
      hist.item_id AS history_id,
      CAST(
        CAST(cci."jsonb" ->> 'occurredDateTime' AS timestamptz) AT TIME ZONE 'America/Chicago' AS DATE
      ) AS new_date,
      hist.hist_charges AS hist_charges,
      hist.hist_browses AS hist_browses,
      0 AS new_charges,
      1 AS new_browses,
      last_transaction
    FROM
      folio_circulation.check_in cci
      LEFT JOIN mis.item_history hist ON (cci."jsonb" ->> 'itemId')::uuid = hist.item_id::uuid
    WHERE
      (
        Cast(
          CAST(cci."jsonb" ->> 'occurredDateTime' AS timestamptz) AT TIME ZONE 'America/Chicago' AS DATE
        ) > hist.last_transaction
        OR hist.item_id IS NULL
      )
      AND cci."jsonb" ->> 'itemStatusPriorToCheckIn' = 'Available'
  ) AS updates
WHERE
  (
    new_date >= last_transaction
    OR last_transaction IS NULL
  )
GROUP BY
  item_id,
  hist_charges,
  hist_browses,
  last_transaction
 limit 3;

Results ( using metadev - as mis schema is present here)

[rmathew1011]

Camunda Logs:

The user account : meta_wf currently has RO access only - post granting this account RW - we could see this workflow execute successfully.

This involves writing data to the mis schema.

2025-07-11T22:01:47.694Z  INFO 1 --- [mod-camunda] [nio-8081-exec-5] o.f.rest.camunda.delegate.SetupDelegate  : Setup [] started
2025-07-11T22:01:47.694Z  INFO 1 --- [mod-camunda] [nio-8081-exec-5] o.f.rest.camunda.delegate.SetupDelegate  : loading initial context
2025-07-11T22:01:47.695Z  INFO 1 --- [mod-camunda] [nio-8081-exec-5] o.f.rest.camunda.delegate.SetupDelegate  : loading scripts
2025-07-11T22:01:47.695Z  INFO 1 --- [mod-camunda] [nio-8081-exec-5] o.f.rest.camunda.delegate.SetupDelegate  : Setup finished in 0.924344 milliseconds
2025-07-11T22:01:47.728Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.d.DatabaseConnectionDelegate     : Connect MetaDB [] started
2025-07-11T22:01:47.729Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.d.DatabaseConnectionDelegate     : Connect MetaDB finished in 0.033575 milliseconds
2025-07-11T22:01:47.743Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.DatabaseQueryDelegate   : Initial Query to MetaDB [] started
2025-07-11T22:01:53.875Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.DatabaseQueryDelegate   : Initial Query to MetaDB finished in 6131.962761 milliseconds
[engine] WARNING: The polyglot context is using an implementation that does not support runtime compilation.
The guest application code will therefore be executed in interpreted mode only.
Execution only in interpreted mode will strongly impact the guest application performance.
For more information on using GraalVM see https://www.graalvm.org/java/quickstart/.
To disable this warning the '--engine.WarnInterpreterOnly=false' option or use the '-Dpolyglot.engine.WarnInterpreterOnly=false' system property.
[engine] WARNING: The polyglot context is using an implementation that does not support runtime compilation.
The guest application code will therefore be executed in interpreted mode only.
Execution only in interpreted mode will strongly impact the guest application performance.
For more information on using GraalVM see https://www.graalvm.org/java/quickstart/.
To disable this warning the '--engine.WarnInterpreterOnly=false' option or use the '-Dpolyglot.engine.WarnInterpreterOnly=false' system property.
2025-07-11T22:01:54.064Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.DatabaseQueryDelegate   : Upsert Query to LDP [] started
2025-07-11T22:01:54.073Z ERROR 1 --- [mod-camunda] [aTaskExecutor-3] f.r.c.a.DelegateExecutionExceptionAspect : Failed to send email notification of incident

org.springframework.mail.MailSendException: Mail server connection failed. Failed messages: org.eclipse.angus.mail.util.MailConnectException: Couldn't connect to host, port: smtp-relay.tamu.edu, 25; timeout -1;
  nested exception is:
	java.net.ConnectException: Connection refused

[rmathew1011]

We get the following error:

ENGINE-16004 Exception while closing command context: couldn't execute activity <serviceTask id="database_query_task_67d9b49f_fc45_4d10_b341_e59383dabaf8" ...>: ERROR: column "ae3bc759-e4cc-431b-b1d7-46c497c4c054" does not exist
  Position: 96

Resolution ( incorporating suggestion )

Replaced double quote(s) with single quote(s)

Camunda Logs:

2025-07-24T15:43:38.210Z  INFO 1 --- [mod-camunda] [nio-8081-exec-6] o.f.rest.camunda.delegate.SetupDelegate  : Setup [] started
2025-07-24T15:43:38.210Z  INFO 1 --- [mod-camunda] [nio-8081-exec-6] o.f.rest.camunda.delegate.SetupDelegate  : loading initial context
2025-07-24T15:43:38.210Z  INFO 1 --- [mod-camunda] [nio-8081-exec-6] o.f.rest.camunda.delegate.SetupDelegate  : loading scripts
2025-07-24T15:43:38.210Z  INFO 1 --- [mod-camunda] [nio-8081-exec-6] o.f.rest.camunda.delegate.SetupDelegate  : Setup finished in 0.270045 milliseconds
2025-07-24T15:43:38.239Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.d.DatabaseConnectionDelegate     : Connect MetaDB [] started
2025-07-24T15:43:38.239Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.d.DatabaseConnectionDelegate     : Connect MetaDB finished in 0.03449 milliseconds
2025-07-24T15:43:38.250Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.DatabaseQueryDelegate   : Initial Query to MetaDB [] started
2025-07-24T15:43:40.086Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.DatabaseQueryDelegate   : Initial Query to MetaDB finished in 1836.173687 milliseconds
[engine] WARNING: The polyglot context is using an implementation that does not support runtime compilation.
The guest application code will therefore be executed in interpreted mode only.
Execution only in interpreted mode will strongly impact the guest application performance.
For more information on using GraalVM see https://www.graalvm.org/java/quickstart/.
To disable this warning the '--engine.WarnInterpreterOnly=false' option or use the '-Dpolyglot.engine.WarnInterpreterOnly=false' system property.
[engine] WARNING: The polyglot context is using an implementation that does not support runtime compilation.
The guest application code will therefore be executed in interpreted mode only.
Execution only in interpreted mode will strongly impact the guest application performance.
For more information on using GraalVM see https://www.graalvm.org/java/quickstart/.
To disable this warning the '--engine.WarnInterpreterOnly=false' option or use the '-Dpolyglot.engine.WarnInterpreterOnly=false' system property.


 upsertQuery: 
INSERT INTO mis.item_history (item_id, hist_charges, hist_browses, last_transaction) 
VALUES
	('ae3bc759-e4cc-431b-b1d7-46c497c4c054', 48, 0, '2023-01-01'),
	('4f324c77-ea5a-3066-8fe4-46b0ad967ff0', 69, 7, '2024-10-23'),
	('5ba26001-f9df-3a67-ac74-4fcd812ee68a', 56, 0, '2023-02-01'),
	('4671e58c-404a-3340-8da5-ab9dca496aec', 46, 0, '2022-04-23'),
	('058ee2eb-988f-4e9c-962a-8bae6cccf4cd', 64, 4, '2024-12-04'),
	('116d3034-3860-3858-b63f-b3631a607bd5', 52, 1, '2023-02-01'),
	('14e27319-292d-3394-8c92-767a92da4a90', 50, 0, '2023-02-01'),
	('1ca359ae-6d52-34b2-8d74-9a438ba2a001', 46, 0, '2024-07-16'),
	('559b5aea-f52b-46ed-8aea-2d554661660d', 48, 0, '2023-01-01'),
	('b012f4bd-0199-3784-b35b-7d204d9a82b6', 46, 0, '2024-07-16'),
	('6d1e25f7-aaf6-4577-9a42-dbfbd06e58bc', 46, 0, '2023-04-20'),
	('98d37337-4117-4cf6-a76b-2d399dc3cf7e', 48, 0, '2023-01-01'),
	('23465c80-abbf-3c06-9875-0714f05a3e76', 47, 0, '2022-01-27'),
	('bdae93de-add3-3025-bc54-b2cc870215b6', 50, 0, '2023-02-01'),
	('12b50a7f-e620-4660-bec5-692788566503', 48, 1, '2024-09-10'),
	('54fccae3-b621-3b6e-a8df-b1b83fe7a465', 73, 5, '2024-12-08'),
	('00df6e8a-5846-3051-a911-081d0a3ec670', 55, 2, '2024-11-26'),
	('d180a6fe-adce-34db-bad8-2154142b7b07', 51, 0, '2023-02-01')
ON CONFLICT (item_id)
DO UPDATE SET
	hist_charges = EXCLUDED.hist_charges,
	hist_browses = EXCLUDED.hist_browses,
	last_transaction = EXCLUDED.last_transaction
2025-07-24T15:43:40.122Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.DatabaseQueryDelegate   : Upsert Query to MetaDB [] started
2025-07-24T15:43:40.301Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.DatabaseQueryDelegate   : Upsert Query to MetaDB finished in 178.852985 milliseconds
2025-07-24T15:43:40.314Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.d.DatabaseDisconnectDelegate     : Disconnect MetaDB [] started
2025-07-24T15:43:40.314Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.d.DatabaseDisconnectDelegate     : Disconnect MetaDB finished in 0.009521 milliseconds

[rmathew1011]

Update:
Email: https://folio-email-dev.library.tamu.edu/