Skip to content

tms_booking

customer table

customer table's properties and encrypted rows are encrypted on application-level, meaning that they cannot be un-encrypted for viewing or editing by normal means and currently always require a developer to make changes via code to manipulate those rows. Koste:

unfortunately I don't think [decrypting yourself] it's possible
cause the encryption is done on the application level
that's very bad, and I have somewhere in the backlog a migration task
cause it prevents us to do queries
we need to fetch the entire user table
decrypt and do some operations

Copayment data

Makupa has previously requested if they could get the following information: * booking_uuid * bill_details→'

Example bill_details query

-- This makes it so that unnecessary symbols (like +) are not printed
\pset format unaligned
SELECT jsonb_pretty(bill_details) FROM booking WHERE uuid::uuid = '5099874d-594c-4c93-a2a1-8a1e26412f9e' LIMIT 1;

Example bill_details row prettified:

jsonb_pretty
{
    "customer_charges": [
        {
            "used_age": 79,
            "copayment": {
                "vat": {
                    "nanos": 750000000,
                    "serialized": "0.75",
                    "currency_code": "EUR",
                    "display_float": 0.75,
                    "serialized_currency": "0.75 €"
                },
                "total": {
                    "nanos": 100000000,
                    "units": 6,
                    "serialized": "6.10",
                    "currency_code": "EUR",
                    "display_float": 6.1,
                    "serialized_currency": "6.10 €"
                },
                "value": {
                    "nanos": 350000000,
                    "units": 5,
                    "serialized": "5.35",
                    "currency_code": "EUR",
                    "display_float": 5.35,
                    "serialized_currency": "5.35 €"
                },
                "vat_percent": 14
            },
            "customer_uuid": "eb8909fe-f31e-43ca-958a-d5d50c5e9f79",
            "used_distance_meters": 20204
        }
    ]
}

SELECT
    t.uuid,
    charges.copayment -> 'value' ->> 'serialized' AS value,
    charges.copayment -> 'total' ->> 'serialized' AS total,
    charges.customer_uuid,
    charges.exempt_from_copayment,
    bill_details->'all_exempt_from_copayment' AS all_exempt_from_copayment
FROM
    booking AS t,
    jsonb_to_recordset(t.bill_details -> 'customer_charges') AS charges(
        customer_uuid UUID,
        exempt_from_copayment BOOLEAN,
        copayment JSONB
    )
WHERE
    t.org_index = '1017'
    AND pickup_booked >= '2025-08-01'
    AND pickup_booked <= '2025-08-31'
    AND charges.copayment -> 'value' ->> 'serialized' IS NOT NULL;

Makupa org - list from tms-booking: get booking_uuid + copayment_sum (https://app.clickup.com/t/2496230/TMS-6632)

Subpages