-- ============================================================ -- Firefly III Evaluation Seed Data -- -- Task 1: Find Tim Hortons/Starbucks transactions from -- March 1 through March 7, categorize as Coffee, -- tag as Cafe, calculate total spent. -- Expected answer: C$46.50 -- -- Task 2: Set February budgets and determine which are -- exceeded and by how much. -- Expected answer: -- Exceeded: Gas & Transportation (by C$12.10), -- Dining Out (by C$1.50), -- Shopping (by C$9.99) -- ============================================================ SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; -- ============================================================ -- Clean up existing demo data -- ============================================================ DELETE FROM `tag_transaction_journal`; DELETE FROM `category_transaction_journal`; DELETE FROM `category_transaction`; DELETE FROM `budget_transaction_journal`; DELETE FROM `budget_transaction`; DELETE FROM `auto_budgets`; DELETE FROM `budget_limits`; DELETE FROM `available_budgets`; DELETE FROM `budgets`; DELETE FROM `transactions`; DELETE FROM `transaction_journals`; DELETE FROM `transaction_groups`; DELETE FROM `account_balances`; DELETE FROM `account_meta`; DELETE FROM `accounts` WHERE `account_type_id` IN (1,7,14); DELETE FROM `categories`; DELETE FROM `tags`; DELETE FROM `transaction_currency_user_group`; DELETE FROM `transaction_currency_user`; -- ============================================================ -- Enable CAD and USD currencies -- ============================================================ UPDATE `transaction_currencies` SET `enabled`=0 WHERE `id`=1; -- disable EUR UPDATE `transaction_currencies` SET `enabled`=1 WHERE `id`=14; -- enable CAD UPDATE `transaction_currencies` SET `enabled`=1 WHERE `id`=12; -- enable USD -- ============================================================ -- Currency defaults: CAD is primary -- ============================================================ INSERT INTO `transaction_currency_user_group` (`id`,`created_at`,`updated_at`,`user_group_id`,`transaction_currency_id`,`group_default`) VALUES (1,'2026-02-01 00:00:00','2026-02-01 00:00:00',1,14,1); INSERT INTO `transaction_currency_user` (`id`,`created_at`,`updated_at`,`user_id`,`transaction_currency_id`,`user_default`) VALUES (1,'2026-02-01 00:00:00','2026-02-01 00:00:00',1,14,1); -- ============================================================ -- Update preferences for CAD -- ============================================================ UPDATE `preferences` SET `data`='[1,2,3,4]' WHERE `name`='frontpageAccounts' AND `user_id`=1; -- ============================================================ -- Accounts -- ============================================================ -- Asset accounts (account_type_id=1) INSERT INTO `accounts` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`account_type_id`,`name`,`virtual_balance`,`iban`,`active`,`encrypted`,`order`,`native_virtual_balance`) VALUES (1,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,1,'TD Chequing',0.000000000000,NULL,1,0,1,NULL), (2,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,1,'TD Savings',0.000000000000,NULL,1,0,2,NULL), (3,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,1,'USD Travel Account',0.000000000000,NULL,1,0,3,NULL), (4,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,1,'Cash Wallet',0.000000000000,NULL,1,0,4,NULL); -- Expense accounts (account_type_id=7) INSERT INTO `accounts` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`account_type_id`,`name`,`virtual_balance`,`iban`,`active`,`encrypted`,`order`,`native_virtual_balance`) VALUES (5, '2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Tim Hortons',NULL,NULL,1,0,0,NULL), (6, '2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Starbucks',NULL,NULL,1,0,0,NULL), (7, '2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Loblaws',NULL,NULL,1,0,0,NULL), (8, '2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Amazon.ca',NULL,NULL,1,0,0,NULL), (9, '2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Shell Gas Station',NULL,NULL,1,0,0,NULL), (10,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Netflix',NULL,NULL,1,0,0,NULL), (11,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Uber Eats',NULL,NULL,1,0,0,NULL), (12,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Canadian Tire',NULL,NULL,1,0,0,NULL), (13,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Shoppers Drug Mart',NULL,NULL,1,0,0,NULL), (14,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'McDonald''s',NULL,NULL,1,0,0,NULL), (15,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Costco',NULL,NULL,1,0,0,NULL), (16,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Rogers Wireless',NULL,NULL,1,0,0,NULL), (17,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Hydro Ottawa',NULL,NULL,1,0,0,NULL); -- Revenue accounts (account_type_id=14) INSERT INTO `accounts` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`account_type_id`,`name`,`virtual_balance`,`iban`,`active`,`encrypted`,`order`,`native_virtual_balance`) VALUES (18,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,14,'Employer - Payroll',NULL,NULL,1,0,0,NULL), (19,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,14,'CRA Tax Refund',NULL,NULL,1,0,0,NULL), (20,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,14,'Freelance USD Client',NULL,NULL,1,0,0,NULL); -- ============================================================ -- Account metadata -- ============================================================ INSERT INTO `account_meta` (`id`,`created_at`,`updated_at`,`account_id`,`name`,`data`) VALUES -- TD Chequing (1,'2026-02-01 00:00:00','2026-02-01 00:00:00',1,'account_role','"defaultAsset"'), (2,'2026-02-01 00:00:00','2026-02-01 00:00:00',1,'currency_id','"14"'), -- TD Savings (3,'2026-02-01 00:00:00','2026-02-01 00:00:00',2,'account_role','"savingAsset"'), (4,'2026-02-01 00:00:00','2026-02-01 00:00:00',2,'currency_id','"14"'), -- USD Travel (5,'2026-02-01 00:00:00','2026-02-01 00:00:00',3,'account_role','"defaultAsset"'), (6,'2026-02-01 00:00:00','2026-02-01 00:00:00',3,'currency_id','"12"'), -- Cash Wallet (7,'2026-02-01 00:00:00','2026-02-01 00:00:00',4,'account_role','"cashWalletAsset"'), (8,'2026-02-01 00:00:00','2026-02-01 00:00:00',4,'currency_id','"14"'), -- Expense account metadata (BIC + account_number, empty) (9, '2026-02-01 00:00:00','2026-02-01 00:00:00',5, 'BIC','""'), (10,'2026-02-01 00:00:00','2026-02-01 00:00:00',5, 'account_number','""'), (11,'2026-02-01 00:00:00','2026-02-01 00:00:00',6, 'BIC','""'), (12,'2026-02-01 00:00:00','2026-02-01 00:00:00',6, 'account_number','""'), (13,'2026-02-01 00:00:00','2026-02-01 00:00:00',7, 'BIC','""'), (14,'2026-02-01 00:00:00','2026-02-01 00:00:00',7, 'account_number','""'), (15,'2026-02-01 00:00:00','2026-02-01 00:00:00',8, 'BIC','""'), (16,'2026-02-01 00:00:00','2026-02-01 00:00:00',8, 'account_number','""'), (17,'2026-02-01 00:00:00','2026-02-01 00:00:00',9, 'BIC','""'), (18,'2026-02-01 00:00:00','2026-02-01 00:00:00',9, 'account_number','""'), (19,'2026-02-01 00:00:00','2026-02-01 00:00:00',10,'BIC','""'), (20,'2026-02-01 00:00:00','2026-02-01 00:00:00',10,'account_number','""'), (21,'2026-02-01 00:00:00','2026-02-01 00:00:00',11,'BIC','""'), (22,'2026-02-01 00:00:00','2026-02-01 00:00:00',11,'account_number','""'), (23,'2026-02-01 00:00:00','2026-02-01 00:00:00',12,'BIC','""'), (24,'2026-02-01 00:00:00','2026-02-01 00:00:00',12,'account_number','""'), (25,'2026-02-01 00:00:00','2026-02-01 00:00:00',13,'BIC','""'), (26,'2026-02-01 00:00:00','2026-02-01 00:00:00',13,'account_number','""'), (27,'2026-02-01 00:00:00','2026-02-01 00:00:00',14,'BIC','""'), (28,'2026-02-01 00:00:00','2026-02-01 00:00:00',14,'account_number','""'), (29,'2026-02-01 00:00:00','2026-02-01 00:00:00',15,'BIC','""'), (30,'2026-02-01 00:00:00','2026-02-01 00:00:00',15,'account_number','""'), (31,'2026-02-01 00:00:00','2026-02-01 00:00:00',16,'BIC','""'), (32,'2026-02-01 00:00:00','2026-02-01 00:00:00',16,'account_number','""'), (33,'2026-02-01 00:00:00','2026-02-01 00:00:00',17,'BIC','""'), (34,'2026-02-01 00:00:00','2026-02-01 00:00:00',17,'account_number','""'), -- Revenue account metadata (35,'2026-02-01 00:00:00','2026-02-01 00:00:00',18,'BIC','""'), (36,'2026-02-01 00:00:00','2026-02-01 00:00:00',18,'account_number','""'), (37,'2026-02-01 00:00:00','2026-02-01 00:00:00',19,'BIC','""'), (38,'2026-02-01 00:00:00','2026-02-01 00:00:00',19,'account_number','""'), (39,'2026-02-01 00:00:00','2026-02-01 00:00:00',20,'BIC','""'), (40,'2026-02-01 00:00:00','2026-02-01 00:00:00',20,'account_number','""'); -- ============================================================ -- Categories (deliberately NO "Coffee" category) -- ============================================================ INSERT INTO `categories` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`name`,`encrypted`) VALUES (1,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Groceries',0), (2,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Gas & Transportation',0), (3,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Entertainment',0), (4,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Utilities & Bills',0), (5,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Dining Out',0), (6,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Shopping',0), (7,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Health & Personal Care',0); -- ============================================================ -- Tags (deliberately NO "Cafe" tag) -- ============================================================ INSERT INTO `tags` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`tag`,`tag_mode`,`date`,`date_tz`,`description`,`latitude`,`longitude`,`zoomLevel`) VALUES (1,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'essential','nothing',NULL,NULL,NULL,NULL,NULL,NULL), (2,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'recurring','nothing',NULL,NULL,NULL,NULL,NULL,NULL), (3,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'impulse-buy','nothing',NULL,NULL,NULL,NULL,NULL,NULL); -- ============================================================ -- Transaction Groups (one per transaction, 31 total) -- ============================================================ INSERT INTO `transaction_groups` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`title`) VALUES (1, '2026-02-15 00:00:00','2026-02-15 00:00:00',NULL,1,1,NULL), (2, '2026-02-18 00:00:00','2026-02-18 00:00:00',NULL,1,1,NULL), (3, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,1,1,NULL), (4, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,1,1,NULL), (5, '2026-02-22 00:00:00','2026-02-22 00:00:00',NULL,1,1,NULL), (6, '2026-02-23 00:00:00','2026-02-23 00:00:00',NULL,1,1,NULL), (7, '2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,1,1,NULL), (8, '2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,1,1,NULL), (9, '2026-02-26 00:00:00','2026-02-26 00:00:00',NULL,1,1,NULL), (10,'2026-02-27 00:00:00','2026-02-27 00:00:00',NULL,1,1,NULL), (11,'2026-02-28 00:00:00','2026-02-28 00:00:00',NULL,1,1,NULL), (12,'2026-03-01 00:00:00','2026-03-01 00:00:00',NULL,1,1,NULL), (13,'2026-03-01 00:00:00','2026-03-01 00:00:00',NULL,1,1,NULL), (14,'2026-03-02 00:00:00','2026-03-02 00:00:00',NULL,1,1,NULL), (15,'2026-03-02 00:00:00','2026-03-02 00:00:00',NULL,1,1,NULL), (16,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,1,1,NULL), (17,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,1,1,NULL), (18,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,1,1,NULL), (19,'2026-03-04 00:00:00','2026-03-04 00:00:00',NULL,1,1,NULL), (20,'2026-03-04 00:00:00','2026-03-04 00:00:00',NULL,1,1,NULL), (21,'2026-03-05 00:00:00','2026-03-05 00:00:00',NULL,1,1,NULL), (22,'2026-03-05 00:00:00','2026-03-05 00:00:00',NULL,1,1,NULL), (23,'2026-03-06 00:00:00','2026-03-06 00:00:00',NULL,1,1,NULL), (24,'2026-03-06 00:00:00','2026-03-06 00:00:00',NULL,1,1,NULL), (25,'2026-03-07 00:00:00','2026-03-07 00:00:00',NULL,1,1,NULL), (26,'2026-03-08 00:00:00','2026-03-08 00:00:00',NULL,1,1,NULL), (27,'2026-03-09 00:00:00','2026-03-09 00:00:00',NULL,1,1,NULL), (28,'2026-03-10 00:00:00','2026-03-10 00:00:00',NULL,1,1,NULL), (29,'2026-03-10 00:00:00','2026-03-10 00:00:00',NULL,1,1,NULL), (30,'2026-03-11 00:00:00','2026-03-11 00:00:00',NULL,1,1,NULL), (31,'2026-03-12 00:00:00','2026-03-12 00:00:00',NULL,1,1,NULL); -- ============================================================ -- Transaction Journals -- Types: 1=Deposit, 6=Transfer, 7=Withdrawal -- Currency: 14=CAD, 12=USD -- tag_count: number of tags on that journal -- ============================================================ INSERT INTO `transaction_journals` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`transaction_type_id`,`transaction_group_id`,`bill_id`,`transaction_currency_id`,`description`,`date`,`date_tz`,`interest_date`,`book_date`,`process_date`,`order`,`tag_count`,`encrypted`,`completed`) VALUES -- February 2026 (1, '2026-02-15 00:00:00','2026-02-15 00:00:00',NULL,1,1,1, 1, NULL,14,'Salary - first half of February', '2026-02-15 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (2, '2026-02-18 00:00:00','2026-02-18 00:00:00',NULL,1,1,7, 2, NULL,14,'Weekly groceries', '2026-02-18 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1), (3, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,1,1,7, 3, NULL,14,'Gas fill-up', '2026-02-20 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (4, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,1,1,1, 4, NULL,12,'Freelance web design payment', '2026-02-20 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (5, '2026-02-22 00:00:00','2026-02-22 00:00:00',NULL,1,1,7, 5, NULL,14,'Netflix monthly subscription', '2026-02-22 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1), (6, '2026-02-23 00:00:00','2026-02-23 00:00:00',NULL,1,1,7, 6, NULL,14,'Rogers phone bill', '2026-02-23 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1), (7, '2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,1,1,7, 7, NULL,14,'Flat white and scone', '2026-02-25 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (8, '2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,1,1,7, 8, NULL,14,'Medium double double', '2026-02-25 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (9, '2026-02-26 00:00:00','2026-02-26 00:00:00',NULL,1,1,7, 9, NULL,14,'Bluetooth headphones', '2026-02-26 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1), (10,'2026-02-27 00:00:00','2026-02-27 00:00:00',NULL,1,1,7,10, NULL,14,'Costco bulk shopping', '2026-02-27 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1), (11,'2026-02-28 00:00:00','2026-02-28 00:00:00',NULL,1,1,1,11, NULL,14,'Salary - second half of February', '2026-02-28 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), -- March 1-7: FIRST WEEK OF MARCH (Tim Hortons/Starbucks have NO category, NO tag) (12,'2026-03-01 09:15:00','2026-03-01 09:15:00',NULL,1,1,7,12, NULL,14,'Double double and muffin', '2026-03-01 09:15:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (13,'2026-03-01 00:00:00','2026-03-01 00:00:00',NULL,1,1,7,13, NULL,14,'Netflix monthly subscription', '2026-03-01 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1), (14,'2026-03-02 08:30:00','2026-03-02 08:30:00',NULL,1,1,7,14, NULL,14,'Grande latte', '2026-03-02 08:30:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (15,'2026-03-02 00:00:00','2026-03-02 00:00:00',NULL,1,1,7,15, NULL,14,'Weekly groceries', '2026-03-02 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1), (16,'2026-03-03 07:45:00','2026-03-03 07:45:00',NULL,1,1,7,16, NULL,14,'Large coffee and bagel BELT', '2026-03-03 07:45:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (17,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,1,1,7,17, NULL,14,'Gas fill-up', '2026-03-03 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (18,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,1,1,6,18, NULL,14,'Transfer to savings', '2026-03-03 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (19,'2026-03-04 08:00:00','2026-03-04 08:00:00',NULL,1,1,7,19, NULL,14,'Venti caramel macchiato', '2026-03-04 08:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (20,'2026-03-04 00:00:00','2026-03-04 00:00:00',NULL,1,1,7,20, NULL,14,'Dinner delivery', '2026-03-04 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (21,'2026-03-05 09:00:00','2026-03-05 09:00:00',NULL,1,1,7,21, NULL,14,'Large dark roast', '2026-03-05 09:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (22,'2026-03-05 00:00:00','2026-03-05 00:00:00',NULL,1,1,7,22, NULL,14,'Lunch combo', '2026-03-05 12:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (23,'2026-03-06 08:15:00','2026-03-06 08:15:00',NULL,1,1,7,23, NULL,14,'Pike Place and cake pop', '2026-03-06 08:15:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (24,'2026-03-06 00:00:00','2026-03-06 00:00:00',NULL,1,1,7,24, NULL,14,'Vitamins and toiletries', '2026-03-06 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (25,'2026-03-07 07:30:00','2026-03-07 07:30:00',NULL,1,1,7,25, NULL,14,'Iced capp and donut', '2026-03-07 07:30:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), -- After first week of March (26,'2026-03-08 00:00:00','2026-03-08 00:00:00',NULL,1,1,7,26, NULL,14,'Windshield wipers', '2026-03-08 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (27,'2026-03-09 00:00:00','2026-03-09 00:00:00',NULL,1,1,7,27, NULL,14,'Weekly groceries', '2026-03-09 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1), (28,'2026-03-10 09:00:00','2026-03-10 09:00:00',NULL,1,1,7,28, NULL,14,'Roll Up the Rim coffee', '2026-03-10 09:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (29,'2026-03-10 08:45:00','2026-03-10 08:45:00',NULL,1,1,7,29, NULL,14,'Matcha latte', '2026-03-10 08:45:00','America/Toronto',NULL,NULL,NULL,0,0,0,1), (30,'2026-03-11 00:00:00','2026-03-11 00:00:00',NULL,1,1,7,30, NULL,14,'Electricity bill', '2026-03-11 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1), (31,'2026-03-12 00:00:00','2026-03-12 00:00:00',NULL,1,1,7,31, NULL,12,'Amazon US order - Programming books', '2026-03-12 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1); -- ============================================================ -- Transactions (2 rows per journal: source and destination) -- balance_dirty=1 so Firefly recalculates balances -- ============================================================ INSERT INTO `transactions` (`id`,`created_at`,`updated_at`,`deleted_at`,`reconciled`,`account_id`,`transaction_journal_id`,`description`,`transaction_currency_id`,`amount`,`balance_before`,`balance_after`,`balance_dirty`,`foreign_amount`,`foreign_currency_id`,`identifier`,`native_amount`,`native_foreign_amount`) VALUES -- TJ1: Salary deposit Feb 15 (Employer->Chequing, CAD 3500) (1, '2026-02-15 00:00:00','2026-02-15 00:00:00',NULL,0,18,1,NULL,14,-3500.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (2, '2026-02-15 00:00:00','2026-02-15 00:00:00',NULL,0,1, 1,NULL,14, 3500.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ2: Groceries Feb 18 (Chequing->Loblaws, CAD 87.43) (3, '2026-02-18 00:00:00','2026-02-18 00:00:00',NULL,0,1, 2,NULL,14,-87.430000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (4, '2026-02-18 00:00:00','2026-02-18 00:00:00',NULL,0,7, 2,NULL,14, 87.430000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ3: Gas Feb 20 (Chequing->Shell, CAD 62.10) (5, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,0,1, 3,NULL,14,-62.100000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (6, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,0,9, 3,NULL,14, 62.100000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ4: Freelance deposit Feb 20 (Freelance->USD Acct, USD 750) (7, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,0,20,4,NULL,12,-750.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (8, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,0,3, 4,NULL,12, 750.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ5: Netflix Feb 22 (Chequing->Netflix, CAD 16.99) (9, '2026-02-22 00:00:00','2026-02-22 00:00:00',NULL,0,1, 5,NULL,14,-16.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (10,'2026-02-22 00:00:00','2026-02-22 00:00:00',NULL,0,10,5,NULL,14, 16.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ6: Rogers Feb 23 (Chequing->Rogers, CAD 75.00) (11,'2026-02-23 00:00:00','2026-02-23 00:00:00',NULL,0,1, 6,NULL,14,-75.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (12,'2026-02-23 00:00:00','2026-02-23 00:00:00',NULL,0,16,6,NULL,14, 75.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ7: Starbucks Feb 25 (Chequing->Starbucks, CAD 9.35) -- HAS category (Dining Out) (13,'2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,0,1, 7,NULL,14,-9.350000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (14,'2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,0,6, 7,NULL,14, 9.350000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ8: Tim Hortons Feb 25 (Chequing->Tim Hortons, CAD 2.15) -- HAS category (Dining Out) (15,'2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,0,1, 8,NULL,14,-2.150000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (16,'2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,0,5, 8,NULL,14, 2.150000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ9: Amazon headphones Feb 26 (Chequing->Amazon, CAD 49.99) (17,'2026-02-26 00:00:00','2026-02-26 00:00:00',NULL,0,1, 9,NULL,14,-49.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (18,'2026-02-26 00:00:00','2026-02-26 00:00:00',NULL,0,8, 9,NULL,14, 49.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ10: Costco Feb 27 (Chequing->Costco, CAD 156.82) (19,'2026-02-27 00:00:00','2026-02-27 00:00:00',NULL,0,1, 10,NULL,14,-156.820000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (20,'2026-02-27 00:00:00','2026-02-27 00:00:00',NULL,0,15,10,NULL,14, 156.820000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ11: Salary deposit Feb 28 (Employer->Chequing, CAD 3500) (21,'2026-02-28 00:00:00','2026-02-28 00:00:00',NULL,0,18,11,NULL,14,-3500.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (22,'2026-02-28 00:00:00','2026-02-28 00:00:00',NULL,0,1, 11,NULL,14, 3500.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- ====== FIRST WEEK OF MARCH: TARGET TRANSACTIONS (no category, no tag) ====== -- TJ12: Tim Hortons Mar 1 - "Double double and muffin" CAD 5.47 (23,'2026-03-01 09:15:00','2026-03-01 09:15:00',NULL,0,1, 12,NULL,14,-5.470000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (24,'2026-03-01 09:15:00','2026-03-01 09:15:00',NULL,0,5, 12,NULL,14, 5.470000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ13: Netflix Mar 1 (Chequing->Netflix, CAD 16.99) (25,'2026-03-01 00:00:00','2026-03-01 00:00:00',NULL,0,1, 13,NULL,14,-16.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (26,'2026-03-01 00:00:00','2026-03-01 00:00:00',NULL,0,10,13,NULL,14, 16.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ14: Starbucks Mar 2 - "Grande latte" CAD 6.45 (27,'2026-03-02 08:30:00','2026-03-02 08:30:00',NULL,0,1, 14,NULL,14,-6.450000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (28,'2026-03-02 08:30:00','2026-03-02 08:30:00',NULL,0,6, 14,NULL,14, 6.450000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ15: Loblaws Mar 2 (Chequing->Loblaws, CAD 93.21) (29,'2026-03-02 00:00:00','2026-03-02 00:00:00',NULL,0,1, 15,NULL,14,-93.210000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (30,'2026-03-02 00:00:00','2026-03-02 00:00:00',NULL,0,7, 15,NULL,14, 93.210000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ16: Tim Hortons Mar 3 - "Large coffee and bagel BELT" CAD 8.23 (31,'2026-03-03 07:45:00','2026-03-03 07:45:00',NULL,0,1, 16,NULL,14,-8.230000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (32,'2026-03-03 07:45:00','2026-03-03 07:45:00',NULL,0,5, 16,NULL,14, 8.230000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ17: Shell gas Mar 3 (Chequing->Shell, CAD 58.40) (33,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,0,1, 17,NULL,14,-58.400000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (34,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,0,9, 17,NULL,14, 58.400000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ18: Transfer to savings Mar 3 (Chequing->Savings, CAD 500) (35,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,0,1, 18,NULL,14,-500.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (36,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,0,2, 18,NULL,14, 500.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ19: Starbucks Mar 4 - "Venti caramel macchiato" CAD 7.20 (37,'2026-03-04 08:00:00','2026-03-04 08:00:00',NULL,0,1, 19,NULL,14,-7.200000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (38,'2026-03-04 08:00:00','2026-03-04 08:00:00',NULL,0,6, 19,NULL,14, 7.200000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ20: Uber Eats Mar 4 (Chequing->Uber Eats, CAD 32.15) (39,'2026-03-04 00:00:00','2026-03-04 00:00:00',NULL,0,1, 20,NULL,14,-32.150000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (40,'2026-03-04 00:00:00','2026-03-04 00:00:00',NULL,0,11,20,NULL,14, 32.150000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ21: Tim Hortons Mar 5 - "Large dark roast" CAD 2.35 (41,'2026-03-05 09:00:00','2026-03-05 09:00:00',NULL,0,1, 21,NULL,14,-2.350000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (42,'2026-03-05 09:00:00','2026-03-05 09:00:00',NULL,0,5, 21,NULL,14, 2.350000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ22: McDonald's Mar 5 (Chequing->McDonald's, CAD 12.49) (43,'2026-03-05 12:00:00','2026-03-05 12:00:00',NULL,0,1, 22,NULL,14,-12.490000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (44,'2026-03-05 12:00:00','2026-03-05 12:00:00',NULL,0,14,22,NULL,14, 12.490000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ23: Starbucks Mar 6 - "Pike Place and cake pop" CAD 8.95 (45,'2026-03-06 08:15:00','2026-03-06 08:15:00',NULL,0,1, 23,NULL,14,-8.950000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (46,'2026-03-06 08:15:00','2026-03-06 08:15:00',NULL,0,6, 23,NULL,14, 8.950000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ24: Shoppers Drug Mart Mar 6 (Chequing->Shoppers, CAD 34.67) (47,'2026-03-06 00:00:00','2026-03-06 00:00:00',NULL,0,1, 24,NULL,14,-34.670000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (48,'2026-03-06 00:00:00','2026-03-06 00:00:00',NULL,0,13,24,NULL,14, 34.670000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ25: Tim Hortons Mar 7 - "Iced capp and donut" CAD 7.85 (49,'2026-03-07 07:30:00','2026-03-07 07:30:00',NULL,0,1, 25,NULL,14,-7.850000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (50,'2026-03-07 07:30:00','2026-03-07 07:30:00',NULL,0,5, 25,NULL,14, 7.850000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- ====== AFTER FIRST WEEK (distractors) ====== -- TJ26: Canadian Tire Mar 8 (Chequing->Can Tire, CAD 28.99) (51,'2026-03-08 00:00:00','2026-03-08 00:00:00',NULL,0,1, 26,NULL,14,-28.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (52,'2026-03-08 00:00:00','2026-03-08 00:00:00',NULL,0,12,26,NULL,14, 28.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ27: Loblaws Mar 9 (Chequing->Loblaws, CAD 71.33) (53,'2026-03-09 00:00:00','2026-03-09 00:00:00',NULL,0,1, 27,NULL,14,-71.330000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (54,'2026-03-09 00:00:00','2026-03-09 00:00:00',NULL,0,7, 27,NULL,14, 71.330000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ28: Tim Hortons Mar 10 - DISTRACTOR (outside first week, no category) (55,'2026-03-10 09:00:00','2026-03-10 09:00:00',NULL,0,1, 28,NULL,14,-3.450000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (56,'2026-03-10 09:00:00','2026-03-10 09:00:00',NULL,0,5, 28,NULL,14, 3.450000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ29: Starbucks Mar 10 - DISTRACTOR (outside first week, no category) (57,'2026-03-10 08:45:00','2026-03-10 08:45:00',NULL,0,1, 29,NULL,14,-6.750000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (58,'2026-03-10 08:45:00','2026-03-10 08:45:00',NULL,0,6, 29,NULL,14, 6.750000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ30: Hydro Ottawa Mar 11 (Chequing->Hydro, CAD 142.50) (59,'2026-03-11 00:00:00','2026-03-11 00:00:00',NULL,0,1, 30,NULL,14,-142.500000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (60,'2026-03-11 00:00:00','2026-03-11 00:00:00',NULL,0,17,30,NULL,14, 142.500000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), -- TJ31: Amazon US order Mar 12 (USD Acct->Amazon, USD 45.99) (61,'2026-03-12 00:00:00','2026-03-12 00:00:00',NULL,0,3, 31,NULL,12,-45.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL), (62,'2026-03-12 00:00:00','2026-03-12 00:00:00',NULL,0,8, 31,NULL,12, 45.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL); -- ============================================================ -- Category <-> Transaction Journal links -- Only for non-target transactions -- ============================================================ INSERT INTO `category_transaction_journal` (`id`,`category_id`,`transaction_journal_id`) VALUES -- Feb transactions with categories (1, 1, 2), -- TJ2: Groceries -> Weekly groceries (2, 2, 3), -- TJ3: Gas & Transportation -> Gas fill-up (3, 3, 5), -- TJ5: Entertainment -> Netflix (4, 4, 6), -- TJ6: Utilities & Bills -> Rogers (5, 5, 7), -- TJ7: Dining Out -> Starbucks Feb 25 (6, 5, 8), -- TJ8: Dining Out -> Tim Hortons Feb 25 (7, 6, 9), -- TJ9: Shopping -> Bluetooth headphones (8, 1, 10), -- TJ10: Groceries -> Costco -- March transactions with categories (NOT the Tim Hortons/Starbucks first-week ones) (9, 3, 13), -- TJ13: Entertainment -> Netflix Mar 1 (10, 1, 15), -- TJ15: Groceries -> Loblaws Mar 2 (11, 2, 17), -- TJ17: Gas & Transportation -> Shell Mar 3 (12, 5, 20), -- TJ20: Dining Out -> Uber Eats Mar 4 (13, 5, 22), -- TJ22: Dining Out -> McDonald's Mar 5 (14, 7, 24), -- TJ24: Health & Personal Care -> Shoppers Mar 6 (15, 6, 26), -- TJ26: Shopping -> Canadian Tire Mar 8 (16, 1, 27), -- TJ27: Groceries -> Loblaws Mar 9 (17, 4, 30), -- TJ30: Utilities & Bills -> Hydro Ottawa Mar 11 (18, 6, 31); -- TJ31: Shopping -> Amazon US Mar 12 -- ============================================================ -- Tag <-> Transaction Journal links -- Only for non-target transactions -- ============================================================ INSERT INTO `tag_transaction_journal` (`id`,`tag_id`,`transaction_journal_id`) VALUES (1, 1, 2), -- essential -> Weekly groceries Feb (2, 2, 5), -- recurring -> Netflix Feb (3, 2, 6), -- recurring -> Rogers Feb (4, 3, 9), -- impulse-buy -> Bluetooth headphones (5, 1, 10), -- essential -> Costco Feb (6, 2, 13), -- recurring -> Netflix Mar (7, 1, 15), -- essential -> Loblaws Mar (8, 1, 27), -- essential -> Loblaws Mar 9 (9, 2, 30); -- recurring -> Hydro Ottawa Mar SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; -- ============================================================ -- TASK 1 VERIFICATION: -- Target transactions (Tim Hortons/Starbucks, March 1-7): -- TJ12: Tim Hortons Mar 1 C$5.47 -- TJ14: Starbucks Mar 2 C$6.45 -- TJ16: Tim Hortons Mar 3 C$8.23 -- TJ19: Starbucks Mar 4 C$7.20 -- TJ21: Tim Hortons Mar 5 C$2.35 -- TJ23: Starbucks Mar 6 C$8.95 -- TJ25: Tim Hortons Mar 7 C$7.85 -- Total: C$46.50 -- -- Distractors (should NOT be included): -- TJ7: Starbucks Feb 25 C$9.35 (before March) -- TJ8: Tim Hortons Feb 25 C$2.15 (before March) -- TJ28: Tim Hortons Mar 10 C$3.45 (after first week) -- TJ29: Starbucks Mar 10 C$6.75 (after first week) -- ============================================================ -- -- ============================================================ -- TASK 2 VERIFICATION: -- February spending by category: -- Groceries: C$87.43 + C$156.82 = C$244.25 -- Gas & Transportation: C$62.10 -- Entertainment: C$16.99 -- Utilities & Bills: C$75.00 -- Dining Out: C$9.35 + C$2.15 = C$11.50 -- Shopping: C$49.99 -- -- Budgets vs Actual: -- Groceries: Budget C$300 | Spent C$244.25 | UNDER by C$55.75 -- Gas & Transportation: Budget C$50 | Spent C$62.10 | EXCEEDED by C$12.10 -- Entertainment: Budget C$25 | Spent C$16.99 | UNDER by C$8.01 -- Utilities & Bills: Budget C$100 | Spent C$75.00 | UNDER by C$25.00 -- Dining Out: Budget C$10 | Spent C$11.50 | EXCEEDED by C$1.50 -- Shopping: Budget C$40 | Spent C$49.99 | EXCEEDED by C$9.99 -- ============================================================