Debugging in Environments

Debugging in QA, Preprod, and Prod

Most of the issues with reports is due to a data issue or a bug in the code. Reports service uses postgres to generate documents and hydrates the data from only the queue topic. Each table is a SDL model defintion that is found in sdl/models.ts unless the model contains a no_db decorator. If there is a no_db decorator, the model is not saved into postgres.

Postgres columns

Each of the tables will contain the following columns:

  • offset (number) - Kafka message offset
  • sender_id (string) - who sent the message
  • updated_at (timestamp in UTC) - the business effective timestamp of when this happened
  • message (jsonb) - JSON of the Kafka message By using jsonb for messages, migrations are not needed whenever the message changes. We validate and keep track of the fields used for each model in /apps/reports/src/db/models.ts Jsonb is case sensitive, so if a message changed from uppercase to lowercase and back to uppercase, the query needs to be changed to force it to be uppercase.

Most of the queries looks at the latest message as of a specific timestamp. updated_at column is used for all queries and is populated by postgres sink. Since a lot of messages does not contain a consistent business effective timestamp, Postgres-sink determines the updated_at by

const updated_at =
    parsedMessage["UpdatedAt"]
    ?? parsedMessage["UpdateTimestamp"]
    ?? headers.get("timestamp")
    ?? Temporal.Instant.fromEpochMilliseconds(
        record.timestamp,
    ).toString();

Querying the database

Use \dt to show all the tables.

To query a table, the table name needs to be in '' such as SELECT * FROM 'Account';

To query for something specific in a message such as Id, jsonb operators and functions can be used. Example: SELECT * FROM 'Account' WHERE message ->> 'Id' in ('500', '600');

Below is a good sample query that highlights how to search HoldingTransaction messages.

SELECT DISTINCT ON (message ->> 'Id')
    message->>'Id' as id, message->>'Account' as account, message->>'CreateTimestamp' as timestamp, message->>'ExternalTransactionCode', message->'Metadata'->>'memo' as memo
FROM
    "HoldingTransaction"
WHERE
    (message->>'CreateTimestamp')::timestamptz >= '2022-04-02 00:00:00 America/New_York' AND (message->>'CreateTimestamp')::timestamptz < '2022-04-03 00:00:00 America/New_York'::timestamptz
ORDER BY (message ->> 'Id'), (message->>'CreateTimestamp') ASC;

Let's break this down:

  • In SELECT, there is a lot of message->>'<message field>' as <field>. This grabs the message field and puts it in a field column. So for message->>'Id' as id, the results would have each row with the message id in column id. Without the as <field> the column will be called as unknown. All message fields needs to have single quotes ('').
  • DISTINCT ON gets the unique fields related to the message.
  • In FROM, this is the table/dataset the query is using. Double quotes ("") are used here since the table name is uppercase. Postgres naming convention defaults to lower case
  • In WHERE,
    • (message->>'CreateTimestamp')::timestamptz symbolizes that this field is a timestamp instead of a string. If we want messages from '2022-04-02 00:00:00 America/New_York' and there is a message at exactly this time and ::timestamptz is not in the query, the message will not be picked up
  • ORDER BY is how the results are presented. If ORDER BY or if the sort direction (ascending or descending) is not present, it will default to ascending. If using DISTINCT ON (), the value that needs to be unique needs to also be in ORDER BY.

Prod to Preprod sync

Prod to preprod sync happens nightly and the postgres database will be updated with prod data.

Debug in QA, pre-prod, and prod

To debug in a specific environment, run in a terminal:

kubectx <environment>

environment could be teleport-qa, teleport-preprod, and teleport-prod.

Then run:

kubectl get pods -n default

The output would look something similar to

kubectl exec -tNAME                                READY   STATUS    RESTARTS   AGE
engage-675695f4db-4tj8t             1/1     Running   0          3d21h
engage-675695f4db-5qpws             1/1     Running   0          3d20h
postgres-restore-57cb84cbb6-bfjrb   1/1     Running   0          3d20h
web-0                               1/1     Running   0          3d21h
web-1                               1/1     Running   0          3d20h

Run in terminal:

 kubectl exec -ti <any engage pod> -- bash

bash-5.1# should pop up and then run

psql -h <aws hostname> -U <username> -d reports

|Environment | AWS hostname | Username | Title in 1Password| ||__|______________________| | qa | reports.cbalq0op98ih.us-east-1.rds.amazonaws.com | postgres | RDS Master User - qa - reports | | preprod | reports.cqtrw1xgjkzv.us-east-1.rds.amazonaws.com | postgres| RDS Master User - preprod - reports | | prod | reports.csmh3ymbuckm.us-east-1.rds.amazonaws.com | readonly | ccmx prod rds readonly login |

The next prompt is Password for user postgres:. Go into 1Password and look for the title. Copy and paste the password.

A successful connection would be show postgres=>.

Then run

SELECT * FROM 'Account' LIMIT 1;

The last command is an example command to run.

Reemitting a message onto the queue to fix data issue

When reemiting a message onto the queue, it should ideally come from the service that creates the message. Reports does not look at the sender_id.

To make the message effective as of this timestamp, the message needs to contain UpdatedAt. As of Dec 7, none of the producers produces UpdatedAt and timestamp of the message is used as the business effective timestamp.

The next question becomes what timestamp should UpdatedAt be. Most of the queries that are used in reports looks at the latest message as of this specific date. For monthly statement, UpdatedAt needs to be before the last day of the month but should be the latest message. For example, Account 5 has an incorrect top level account, the current date is 12/7/2023, and statements needs to be generated for Nov 2023. If UpdatedAt is not in the message, the corrected Account message will have a business effective timestamp of 12/7/2023 and that won't fix the monthly statement generation. UpdatedAt needs to at be at least before 11/30.

Reports Infrastructure →