Ryan Cooke In part one of “securing serverless”, I showed how to get started building an app on top of Neon.tech. It couldn’t have been easier — after a short sign up and you get a PG database that is ready to use! Neon has both passwordless auth (for use with psql cli), as well as a full password auth string to configure with our app. You can find those in Neon console.
Our example collected basic customer PII from a web app and saved it to an accounts + account_owners table in the Neon database. The PII fields got encrypted by JumpWire’s HTTP proxy before it was saved into Neon. This alleviated the risk of using a hosted database that is open to the Internet, even if our Neon credentials were lost or stolen, the data in remains protected.
These are pretty simple queries however, basic CRUD stuff. In this post, we’ll start collecting card transactions with metadata (categories, merchant data), and the goal is to do an analysis of spending behavior with more demanding SQL on Neon.
First some backstory
As a startup founder, I don’t have any excess cash these days, so it’s really important that I don’t let discretionary spending drive me into debt one month.
| Not the life of a startup founder via GIPHY |
By loading all my bank and card transactions into Neon, I can use more complex SQL queries to aggregate information — calculating sums, averages, discrete percentiles, etc. Now this new data also contains sensitive information, where and how I spend my money. It’s not necessarily PII, but it’s still information I’d prefer to not make public.
(Would you share your credit card statements with everyone? Well I am! In the examples below, I’m using my real bank and credit card transactions.)
There must be a catch
There’s a problem with just using the same encryption techniques as the last post — encrypting the same value multiple times produces different outputs. This is a key characteristic of encryption that defends encrypted data from brute-force attacks!
But the problem is that it breaks standard operations that I need to use to analyze spending patterns, such as calculating my total spend by category or merchant, or forecasting upcoming payments. While we could pull all of the data into the backend, decrypt it, then calculate it in code, this is terribly inefficient! For even mid-sized data sets, I’d need specialized software or infrastructure to do this. Surely there’s a better way?
Tokenize everything
It’s called tokenization! Tokenization makes a one-way hash of the original data, that produces the same value for the same term every time. This is a perfect replacement for the original data, because now we can use SQL aggregation operations offered by Neon’s PostgreSQL engine. We can even set up indicies and unique constraints in the same way we would with raw data. This makes it possible to apply tokenization to every text field in our database, without incurring any additional overhead. Amazing!
Here’s a peak at what the data looks like in the database, this is my actual spending from last month:
SELECT merchants.name AS merchant, categories.name AS category, amount, transaction_time
FROM transactions
INNER JOIN merchants ON merchants.id = transactions.merchant_id
INNER JOIN categories ON categories.id = transactions.category_id
ORDER BY transaction_time DESC LIMIT 20;
merchant | category | amount | transaction_time
--------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+---------+------------------------
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWXstn0IcXgeYHyYhNwZcNZ6DpnrE8evteZkUconNp2+OQ== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUv/kbWIlsJxD31ICjDy6ezGEOKu0uG3/3bKVFs/3dLEA== | -885.42 | 2023-06-05 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWWTNab1RKaah1p294N4U+q9Rr/ihEERxFkfaLNFll+JIQ== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUnQreySkl5LkfTZGrhI7xPK1zaKrscahJQiH2qsdIMjA== | -0.12 | 2023-06-04 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWVtn2f9a6nhhQBVVyQ82M3GsFd12NN+bLkLRQ2RYtldNw== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWU0VE9AQis0xh8uBiWJvEt9F9/21iqpklSOIKWUBLDpBA== | -4.0 | 2023-06-04 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWVwBcAGS9pO23rX1QDLCB7nBCW6ZEQnWco1YqZXjxmr5g== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWWhxsl/QV8ODtgyrYSOzjMSVlIhZIQ9RfAQZgGn3ac3qQ== | -15.99 | 2023-06-04 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWXnd7y+oioGXQH9KUwas2SJUgwWuYZ1qkVE9GEFM2sYbg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUDYxHQBFRwPgpj7kt25zGaMEFYEE8F603I4KpQ6LDycQ== | -14.99 | 2023-06-03 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWWA/oC2w7UGEcLSjbGLFN220/Ao/j9aOd6CciUIP0NClQ== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXSuY+1NxTyPMCqewrW2/kQ3VNyN9fevoGKFCWQoyK5kg== | -2.75 | 2023-06-03 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWWnlgCE81KQOiKs5T/wlcj1GbQZszUpHzit0fuXNWWa9g== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWW1to+dRf8MIF9uId1E4SP3CBBVhm4v+ROU1BfLb70L8A== | -16.32 | 2023-06-03 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWXa1B8kLSpmcUrPGcbxStwEtgXRxaZDAonW5rB9HzS8Eg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUPLMJkNlUOROd6se17ODhHQs4bQFtGaBwI082s9DIYJA== | -38.0 | 2023-06-02 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWXA1lTTr8Ox4u+97NVuerX6M9sxNyZeVcpkXw2pS+twaw== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXNs4yNyJrWtKC4XrMTc0VyCw+2ynseZAfQmvO0fHdJXA== | -20.8 | 2023-06-02 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWWPHsWA95+q2EdhYACocDJxRc4edlS5xRGZ8wpnW9zPWg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUPLMJkNlUOROd6se17ODhHQs4bQFtGaBwI082s9DIYJA== | -13.37 | 2023-06-02 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWXMCDQtPb15IzMFoOMwGrAAHErr33JLwRkQEEGDEw7nng== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWU0VE9AQis0xh8uBiWJvEt9F9/21iqpklSOIKWUBLDpBA== | -14.0 | 2023-06-02 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWXstn0IcXgeYHyYhNwZcNZ6DpnrE8evteZkUconNp2+OQ== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUv/kbWIlsJxD31ICjDy6ezGEOKu0uG3/3bKVFs/3dLEA== | 885.42 | 2023-06-02 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWWA/oC2w7UGEcLSjbGLFN220/Ao/j9aOd6CciUIP0NClQ== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXSuY+1NxTyPMCqewrW2/kQ3VNyN9fevoGKFCWQoyK5kg== | -2.75 | 2023-06-01 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWWA/oC2w7UGEcLSjbGLFN220/Ao/j9aOd6CciUIP0NClQ== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXSuY+1NxTyPMCqewrW2/kQ3VNyN9fevoGKFCWQoyK5kg== | -2.75 | 2023-06-01 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWVoTUm/yjVMpKmq5wF+nMDkR11N8LqqjcHbyespxhv/tA== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUPLMJkNlUOROd6se17ODhHQs4bQFtGaBwI082s9DIYJA== | -35.0 | 2023-06-01 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWW8vvIN7DZrXIu1c9OUjtjy/7W7sE41EGQCm1VkOuV79A== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXOqlU+g4+58S4+Rz7GqP4MUzKQQc3uhxh0o+Sydp426g== | -5.0 | 2023-06-01 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWViQu4fglrB5bmTDNFZ8gd2H2lChWDlXp9sGxbFEoz1ZA== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUPLMJkNlUOROd6se17ODhHQs4bQFtGaBwI082s9DIYJA== | -16.0 | 2023-05-31 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWU5EPB9RhiIkFZyNSM9xjwTxCPDjONTYCMHRnHVatjNmQ== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWVT/o37bZ4bAyGa3dzD/7dBVX3VedxlPQJgl8Rj3vSo/g== | 2497.76 | 2023-05-31 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWX8RX1C12ej8+sk31hZX7GVW3fIE9oTj6F4TcRUSc1MqQ== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUDYxHQBFRwPgpj7kt25zGaMEFYEE8F603I4KpQ6LDycQ== | -88.31 | 2023-05-31 08:13:14-04
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9TAAAABG5hbWUkK2Zxs2ZWxd4KGTT4CNqWfuw6AKsaXTj6pQMY2SHxpg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUnQreySkl5LkfTZGrhI7xPK1zaKrscahJQiH2qsdIMjA== | -12.99 | 2023-05-31 08:13:14-04Tokens are also irreversible*, meaning there is no way to “unhash” the hashed value. But wait, won’t we want to display the categories in the UI, otherwise what’s the point of doing all this?
JumpWire employs a clever trick to retrieve the original data - it also encrypts the data when making the token, and stores the encrypted value in a token lookup table. In this way we can protect the data, retrieve when necessary (decrypt), and run comparison operations (on the hash). We have cake and can eat it too!
 |
|---|
| It’s not cake, but feels like it |
Show me the SQL
Let’s get our database set up — we add tables for storing transactions, and each transaction has a merchant and category.
CREATE TABLE IF NOT EXISTS categories (
id uuid PRIMARY KEY NOT NULL,
name TEXT,
account_owner_id uuid NOT NULL,
created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_category_owner_uniq ON categories (account_owner_id, name);
CREATE TABLE IF NOT EXISTS merchants (
id uuid PRIMARY KEY NOT NULL,
name TEXT,
account_owner_id uuid NOT NULL,
created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_merchant_owner_uniq ON merchants (account_owner_id, name);
CREATE TABLE IF NOT EXISTS transactions (
id uuid PRIMARY KEY NOT NULL,
category_id uuid REFERENCES categories,
merchant_id uuid REFERENCES merchants,
memo TEXT,
amount numeric,
abs_amount numeric,
balance numeric,
account_id uuid REFERENCES accounts,
account_owner_id uuid REFERENCES account_owners,
is_spend boolean NOT NULL DEFAULT FALSE,
is_income boolean NOT NULL DEFAULT FALSE,
transaction_time timestamp with time zone NOT NULL,
created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
);Now as I said, account, category and merchant names are kind of sensitive. We’ll use JumpWire to label these fields, and create a policy to encode their values as tokens.
 |  |
|---|---|
| Merchant name as token | Category name as token |
 |
|---|
| JumpWire policy set to tokenize fields with label ‘token’ |
(Actually collecting and storing transactions is out of scope for this post. Let’s just say that Plaid has a great free tier)
I can calculate how much I spend at a particular merchant, or within a category. Here’s a query for that, note that I am grouping on the categories.name field which is a tokenized value of the category name:
SELECT categories.name, SUM(abs_amount) AS spend
FROM transactions
LEFT JOIN categories ON categories.id = category_id
WHERE is_spend IS TRUE
GROUP BY categories.name;
name | spend
--------------------------------------------------------------------------------------------------------------------------+----------
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWU/QBMnIK+Uj3bVXojI+fiudhfsDIbL15IU4ogQJ65VLw== | 158.0
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWU0VE9AQis0xh8uBiWJvEt9F9/21iqpklSOIKWUBLDpBA== | 736.31
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXOqlU+g4+58S4+Rz7GqP4MUzKQQc3uhxh0o+Sydp426g== | 641.07
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXNs4yNyJrWtKC4XrMTc0VyCw+2ynseZAfQmvO0fHdJXA== | 535.33
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXiOkRdD6cKqAmt36YHYPVkq0CihHS2tHwipPaZtRJFjg== | 39.27
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWX/wCftzA7z8vYse7FJgFbaHmb0qbryK6B6J6pYcNe4SA== | 12383.80
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWWLZ8PYiPkPx2t0Z6wr1EUoThvRSYbaqzHYlmkwcx2f8Q== | 1417.76
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWVfM2D72ml8p/aZYzCI9Lzjw0RXRFCXjDxDDRWm08x+kQ== | 693.64
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUnQreySkl5LkfTZGrhI7xPK1zaKrscahJQiH2qsdIMjA== | 1147.95
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUQikSrs5wXjYo05hFQbftpsQIUtUYsEyXVmm5qkEMrDg== | 9999.9
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWVYOD555qLUbKneIVA0IIDDE3zSaT9gke8qfiGsvK2+mg== | 14846.16
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUPLMJkNlUOROd6se17ODhHQs4bQFtGaBwI082s9DIYJA== | 7906.21
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUDYxHQBFRwPgpj7kt25zGaMEFYEE8F603I4KpQ6LDycQ== | 1070.07
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWVXVtzTSI+DbLcJYF4j/mghNSyCPr/Csnax8xVwD74Scw== | 38.1
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUZWtRHVxNRn2nyXM07g/dWVTck5KHxXa6BdPe6AXBC5w== | 2087.67
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUv/kbWIlsJxD31ICjDy6ezGEOKu0uG3/3bKVFs/3dLEA== | 43414.80
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWVmClCG1Rlw3RjMDagY4pO76APhLE9Eh04ntaep3cZjkg== | 1087.27
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXSuY+1NxTyPMCqewrW2/kQ3VNyN9fevoGKFCWQoyK5kg== | 13717.79
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWX5fp2g47h58Knfl5riYKX34Tce2xJ8GGLU+GGYEWbNwQ== | 122.72
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWW1to+dRf8MIF9uId1E4SP3CBBVhm4v+ROU1BfLb70L8A== | 494.91
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWWhxsl/QV8ODtgyrYSOzjMSVlIhZIQ9RfAQZgGn3ac3qQ== | 582.85
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXwJoy5EAoBgTMR62U4Iei8zKo+yQ4NjjAj+MHYQbaVag== | 87000.0
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWVnCoAuuuynjk8xhazE3hkcb0H97OjJPdfwYPNKI87ENg== | 1090.84
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXydyyWmvz+PJXoj6io76wpgeM/8yLB6GKwW+zY8oBbeQ== | 5400.0Can you tell what I spend the most money on? Neither can I! (I really hope that $87K is Rent)
Let’s try a more interesting query, as just a sum of spending by category is kind of simple and not offering much insight. Since I’m tracking transactions across multiple accounts (credit cards, bank accounts), I want to calculate how much I spend in each category, for the last 6 months, for each account.
SELECT
accounts.name AS account,
categories.name AS category,
DATE_TRUNC('month', transaction_time) AS transaction_month,
SUM(abs_amount) AS spend
FROM transactions
LEFT JOIN categories ON categories.id = category_id
LEFT JOIN accounts ON accounts.id = account_id
WHERE transaction_time >= DATE_TRUNC('month', now()) - interval '5' month
AND is_spend IS TRUE
GROUP BY accounts.name, categories.name, DATE_TRUNC('month', transaction_time)
ORDER BY DATE_TRUNC('month', transaction_time) DESC LIMIT 20;
account | category | transaction_month | spend
--------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+------------------------+--------
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWULZQwTkL6ytRdP+hsXfz78K/lkePOKETtdjL18HHCgXg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWU0VE9AQis0xh8uBiWJvEt9F9/21iqpklSOIKWUBLDpBA== | 2023-06-01 00:00:00-04 | 18.0
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWULZQwTkL6ytRdP+hsXfz78K/lkePOKETtdjL18HHCgXg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUDYxHQBFRwPgpj7kt25zGaMEFYEE8F603I4KpQ6LDycQ== | 2023-06-01 00:00:00-04 | 14.99
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWULZQwTkL6ytRdP+hsXfz78K/lkePOKETtdjL18HHCgXg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUnQreySkl5LkfTZGrhI7xPK1zaKrscahJQiH2qsdIMjA== | 2023-06-01 00:00:00-04 | 0.12
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWULZQwTkL6ytRdP+hsXfz78K/lkePOKETtdjL18HHCgXg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWW1to+dRf8MIF9uId1E4SP3CBBVhm4v+ROU1BfLb70L8A== | 2023-06-01 00:00:00-04 | 16.32
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWULZQwTkL6ytRdP+hsXfz78K/lkePOKETtdjL18HHCgXg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXOqlU+g4+58S4+Rz7GqP4MUzKQQc3uhxh0o+Sydp426g== | 2023-06-01 00:00:00-04 | 5.0
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWXosnDuK7Lq92rHz20Ox7XRk4YO8y5Vuqjpncukd4RjcA== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUPLMJkNlUOROd6se17ODhHQs4bQFtGaBwI082s9DIYJA== | 2023-06-01 00:00:00-04 | 86.37
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWXosnDuK7Lq92rHz20Ox7XRk4YO8y5Vuqjpncukd4RjcA== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWWhxsl/QV8ODtgyrYSOzjMSVlIhZIQ9RfAQZgGn3ac3qQ== | 2023-06-01 00:00:00-04 | 15.99
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWXosnDuK7Lq92rHz20Ox7XRk4YO8y5Vuqjpncukd4RjcA== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXNs4yNyJrWtKC4XrMTc0VyCw+2ynseZAfQmvO0fHdJXA== | 2023-06-01 00:00:00-04 | 20.8
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWXosnDuK7Lq92rHz20Ox7XRk4YO8y5Vuqjpncukd4RjcA== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXSuY+1NxTyPMCqewrW2/kQ3VNyN9fevoGKFCWQoyK5kg== | 2023-06-01 00:00:00-04 | 8.25
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWUh3cPRs3R28SmQa1XN5X971Q1RScocYWJTd0mfbACkBw== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWX/wCftzA7z8vYse7FJgFbaHmb0qbryK6B6J6pYcNe4SA== | 2023-05-01 00:00:00-04 | 600.0
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWUh3cPRs3R28SmQa1XN5X971Q1RScocYWJTd0mfbACkBw== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXwJoy5EAoBgTMR62U4Iei8zKo+yQ4NjjAj+MHYQbaVag== | 2023-05-01 00:00:00-04 | 3750.0
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWULZQwTkL6ytRdP+hsXfz78K/lkePOKETtdjL18HHCgXg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWU0VE9AQis0xh8uBiWJvEt9F9/21iqpklSOIKWUBLDpBA== | 2023-05-01 00:00:00-04 | 32.91
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWULZQwTkL6ytRdP+hsXfz78K/lkePOKETtdjL18HHCgXg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUDYxHQBFRwPgpj7kt25zGaMEFYEE8F603I4KpQ6LDycQ== | 2023-05-01 00:00:00-04 | 289.68
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWULZQwTkL6ytRdP+hsXfz78K/lkePOKETtdjL18HHCgXg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUPLMJkNlUOROd6se17ODhHQs4bQFtGaBwI082s9DIYJA== | 2023-05-01 00:00:00-04 | 129.56
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWULZQwTkL6ytRdP+hsXfz78K/lkePOKETtdjL18HHCgXg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWVfM2D72ml8p/aZYzCI9Lzjw0RXRFCXjDxDDRWm08x+kQ== | 2023-05-01 00:00:00-04 | 50.0
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWULZQwTkL6ytRdP+hsXfz78K/lkePOKETtdjL18HHCgXg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWVnCoAuuuynjk8xhazE3hkcb0H97OjJPdfwYPNKI87ENg== | 2023-05-01 00:00:00-04 | 40.0
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWULZQwTkL6ytRdP+hsXfz78K/lkePOKETtdjL18HHCgXg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWW1to+dRf8MIF9uId1E4SP3CBBVhm4v+ROU1BfLb70L8A== | 2023-05-01 00:00:00-04 | 28.31
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWULZQwTkL6ytRdP+hsXfz78K/lkePOKETtdjL18HHCgXg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXOqlU+g4+58S4+Rz7GqP4MUzKQQc3uhxh0o+Sydp426g== | 2023-05-01 00:00:00-04 | 54.98
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWULZQwTkL6ytRdP+hsXfz78K/lkePOKETtdjL18HHCgXg== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWXSuY+1NxTyPMCqewrW2/kQ3VNyN9fevoGKFCWQoyK5kg== | 2023-05-01 00:00:00-04 | 88.89
SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9kAAAABG5hbWXosnDuK7Lq92rHz20Ox7XRk4YO8y5Vuqjpncukd4RjcA== | SldUT0tOJDIxYmRmOWFlLTJmOWMtNDNlOC04ZjZhLTcyYjgyYjgwNGIxZQQAFJ9IAAAABG5hbWUnQreySkl5LkfTZGrhI7xPK1zaKrscahJQiH2qsdIMjA== | 2023-05-01 00:00:00-04 | 218.44You can notice how the account and category names repeat across rows, this shows the hashing function producing the same long hash string for the same names.
Now to visualize this data, we want to show the real category names to our UI, otherwise a chart would look like gibberish.
 |
|---|
| This is a very unhelpful chart |
This is where the magic of JumpWire comes in: JumpWire will swap out the token for the original value at the edge of our API through its HTTP proxy.
 |
|---|
| HTTP proxy reverses the token at the API edge |
That’s a nice looking pie:
 |
|---|
| Yup, rent |
All of this is seamless to our app! Our schema didn’t change to create tokens, we use the same indicies to enforce uniqueness constraints, overhead and scaling considerations are identical, and we calculate statistics as if we were operating on raw data!
Stay tuned for part three of this series, where we deliberately “leak” credentials of our Neon database.
*Of course, there’s no such thing as perfect security, and some hashing algorithms such as MD5 and SHA-1 are suceptible to brute-force attacks
- Ryan
