bank-statement-api / auth /seed_rules.py
pratik-befree's picture
Update seed_rules.py
7ef099d
Raw History Blame Contribute Delete
57.4 kB
"""
auth/seed_rules.py β€” One-time migration: YAML rules β†’ database.
Run once after adding the new tables:
python -m auth.seed_rules
It is idempotent: existing categories/rules (matched by name) are skipped.
"""
from __future__ import annotations
import os
import sys
from pathlib import Path
from dotenv import load_dotenv
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
# Allow running from the project root
sys.path.insert(0, str(Path(__file__).parent.parent))
# Load .env file variables
load_dotenv()
from auth.database import engine as default_engine, Base, SessionLocal as DefaultSessionLocal
from auth.rule_models import ClassificationCategory, ClassificationRule # noqa: F401 – registers models
# Override engine and session factories if DATABASE_URL is present in the environment
database_url = os.getenv("DATABASE_URL")
if database_url:
# If using async drivers or specific database dialacts, tweak arguments if necessary
engine = create_engine(database_url, pool_pre_ping=True)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
else:
engine = default_engine
SessionLocal = DefaultSessionLocal
# Ensure tables exist
Base.metadata.create_all(bind=engine)
# ── Source data ───────────────────────────────────────────────────────────────
# Inline the CATEGORY_TO_SHEET map so this script has no dependency on
# the old rule_schema.py being present.
CATEGORY_TO_SHEET: dict[str, tuple[str, str] | None] = {
"Groceries": ("Food & Dining", "Groceries"),
"Dining Out": ("Food & Dining", "Dining Out"),
"Alcohol & Liquor": ("Food & Dining", "Alcohol & Liquor"),
"Food Delivery": ("Food & Dining", "Food Delivery"),
"Clothing & Apparel": ("Shopping & Lifestyle", "Clothing & Apparel"),
"Electronics & Tech": ("Shopping & Lifestyle", "Electronics & Tech"),
"Home & Garden": ("Shopping & Lifestyle", "Home & Garden"),
"Personal Care & Beauty": ("Shopping & Lifestyle", "Personal Care & Beauty"),
"General Retail": ("Shopping & Lifestyle", "General Retail"),
"Fuel & Petrol": ("Transport & Automotive", "Fuel & Petrol"),
"Public Transport": ("Transport & Automotive", "Public Transport"),
"Parking": ("Transport & Automotive", "Parking"),
"Rideshare & Taxi": ("Transport & Automotive", "Rideshare & Taxi"),
"Vehicle Expenses": ("Transport & Automotive", "Vehicle Expenses"),
"Medical & Dental": ("Health & Wellbeing", "Medical & Dental"),
"Pharmacy & Chemist": ("Health & Wellbeing", "Pharmacy & Chemist"),
"Fitness & Sport": ("Health & Wellbeing", "Fitness & Sport"),
"Health Insurance": ("Health & Wellbeing", "Health Insurance"),
"Utilities": ("Bills & Services", "Utilities"),
"Telecommunications": ("Bills & Services", "Telecommunications"),
"Streaming & Subscriptions": ("Bills & Services", "Streaming & Subscriptions"),
"General Insurance": ("Bills & Services", "General Insurance"),
"Bank Fees & Charges": ("Bills & Services", "Bank Fees & Charges"),
"Government & Council": ("Bills & Services", "Government & Council"),
"Private Education": ("Family & Education", "Private Education"),
"Childcare": ("Family & Education", "Childcare"),
"Children & Family": ("Family & Education", "Children & Family"),
"Entertainment": ("Entertainment & Leisure", "Entertainment"),
"Gambling": ("Entertainment & Leisure", "Gambling"),
"Travel & Accommodation": ("Entertainment & Leisure", "Travel & Accommodation"),
"Pet Expenses": ("Entertainment & Leisure", "Pet Expenses"),
"Online Marketplace": ("Online & Marketplace", "Online Marketplace"),
"Buy Now Pay Later": ("Online & Marketplace", "Buy Now Pay Later"),
"Wages & Salary": None,
"Internal Transfer": None,
"External Transfer": None,
"Refunds & Returns": None,
"Other Income": None,
"Financial Services": None,
"Debt Collection": None,
"Third Party Payment": None,
# New categories added from updated_categorization_tree.xlsx
"Clothing & Personal Care": ("Clothing & Personal Care", "Clothing & Personal Care"),
"Miscellaneous": ("Miscellaneous", "Miscellaneous"),
"Government Education": ("Public Education", "Government Education"),
"Credit Card": ("Tax / Loans / Financial", "Credit Card"),
"Superannuation": ("Tax / Loans / Financial", "Superannuation"),
}
# Inline all rules from default_rules.yaml (keywords trimmed for brevity but complete)
RULES_DATA: list[dict] = [
# Groceries
{"name": "supermarkets", "category": "Groceries", "keywords": ["woolworths","ww metro","coles","coles online","aldi","iga","costco","spar","ap super sunnybank hil","au.vita sunnybank","australian way eagle farm","ezymart byron bay byron bay","fashionable flavors","fresco calamvale pty l","kim and chien foods pty l sunnybank hil","landa enterprises pt","news travels eagle farm","oasis on the mall","p&n pty ltd","produce of brisbane","sunlit asian suprmrkt upper mount g","yongping pty ltd","shri lakshmi pl kingswood aus"], "priority": 10, "match_on": "description"},
{"name": "specialty_grocers", "category": "Groceries", "keywords": ["harris farm","stanhope fruit","my indian bazaar","enrich ganesha","el dadoun","s&e halal meat","ktm meat house","baraka cookies","abumansour","s patel","reddy express","youfoodz","7-eleven"], "priority": 10, "match_on": "description"},
{"name": "osko_groceries", "category": "Groceries", "keywords": ["groceries","butcher","meat","fruit shop","deli"], "priority": 9, "match_on": "osko_reference"},
# Dining Out
{"name": "fast_food_chains", "category": "Dining Out", "keywords": ["mcdonalds","kfc","hungry jacks","mad mex","boost juice","subway","dominos","pizza hut","gyg","nandos","oporto","grill'd","schnitz","roll'd"], "priority": 10, "match_on": "description"},
{"name": "restaurants_cafes", "category": "Dining Out", "keywords": ["wishbone rotisserie","bella beaumont","pappaflock","chizuru sushi","mango coco","lee's dumpling","eat at robs","mitzu","ls tasty manoush","rivareno gelato","mochi donuts","cinnabon","breadtop","bakers delight","grand cafe","lexi's cafe","leaf cafe","cafe parco","osso rouse hill","xs espresso","jz & cc pty ltd\\north lakes qld au","ben & jerry's byron bay byron bay","cocofreshteacalamvale","foster & black p/l","majestea sunnybank","triple food pty ltd","shawarma stop mount druitt ns aus"], "priority": 8, "match_on": "description"},
{"name": "pubs_bars", "category": "Dining Out", "keywords": ["aust htl n brewery","htl n brewery","zlr*wynyard","urbanone groups","uber *eats","lbc north lakes north lakes","nightowl northlakes north lakes","ikea northlakes north lakes","ws up cafe burpengary","cjs pastries caboolture","roll'd northlakes north lakes","sushi tamashii 5a caboolture","veggie heaven dec eption bay","nightowl northlakes","degani capestone 1800595310","bill's burgers \\albion a","coopers plains carvery coopers plain","jackpot dining","lu xiang shi fang","mottomotto","nene chicken","pho an eatery","sip spice","touch of india","the summit","windmill and co","woka woka mar ket sq","xiangwei sunnybank hil","durks erskine park","frango penrith jamisontown aus","mr manooshie mt druit mount druitt ns au","xs expresso minchinbury au","yumyum concord au","burgerque minchinbury au","chophouse sydney au","bosphorus turkish rs wentworth poi aus","grilld blacktown aus","gold season pty ltd indooroopillyau","bettys burgers chadstone vi aus","sahara eats hamilton ql aus","brooklyndonutswfcherms chermside ql aus","cellar toowong au aus","king of cakes taringa ql aus","fish & chips corinda ql","ltd punchbowl aus","st helens par ns aus","large kuitpo sa aus","ice cr mortdale ns aus","souv punchbowl ns aus","lamannadirectplessendonfielaus","sydneymeatcoptyltdwetherillparau","www.yomg.com.au fountain gate vi aus","tsg casey central narre warren","good food emporium p narre warren aus","sq *dale & moors pty l carlton vi aus","bonbons narre warren vi aus","charlie s kebabs lat bundoora","beach ql aus","pty l may s hill ns aus","eftpos wdl hjs green valley green vall au","eftpos wdl wendy's narellan town narellan au","krispy kreme liverpool 20 orange groau","tan viet cabramatta","bonni and co sans souci nsw au","paypal *medovikptyl","sq *cherry's goods homebush au","sq *rodriguez bros homebush au","tony luu pty ltd cabramatta au","pattisons patisserie blacktown nsw","hills d castle hill","luxury thai & remedial noble park","dominics nw pty ltd","waverley ns aus","kellyville aus","tappoo departure nadi fji","netbank wedding","cellar toowong au","kogarah town centrei","kogarah aus","melbourne skydeck reta southbank vi aus","the trustee for prot pakenham vi aus","good food emporium p narre warren","sq *dale & moors pty l carlton vi","bonbons narre warren","charlie s kebabs lat bundoora vi aus","krispy kreme liverpool","estde pty limited miranda aus","sq *sarah drummond nambour ql","sq *charlotte davies-c wynnum ql","sq *piano tuning brisb taringa ql","kenissy pty ltd st lucia aus","zlr*s2brass mascot au","sydney south ns aus","aus card xx1328","pl berkeley vale aus","north richmon ns aus","north sydney au","surry hills ns aus","malaga wa aus","castle hill ns","ii bella vista ns aus","mar onite croydon","pty l liverpool ns aus","abbotsford vi","gr safety beach vi","pty lt russell lea ns","sydn lidcombe ns aus","group pty ltd sentra secuirty group","silverstone pty ltd cranbourne nt vi","pty ltd sans souci aus","ltd cronulla ns aus","eftpos wdl golden forest trading minto nswau","storage king kgv sydney aus","nguyen phan pty ltd canley height nsw au","typo indooropilly","sp simplyforstrings red hill","gold season pty ltd","sum*h selwyn sydney au","auburn ns aus","par ns aus","fo sydney ns","smithfield ns","gb gbr card","hill aus card","vi aus card","unclesamtobacconistbrightonlesau","the trustee for prot pakenham vi","burraneer ns aus","nguyen phan pty ltd","revesby liquor stax revesby au","*beppin","*kingwu's","*miroku cairns city","*pho eatery","acacia","afanti","algester","andonis cafe","angelo","austral","authentic","balgowlah rsl","bar merlo","bistro","bluefire grill","buena vista hotel","burleigh","casa cibo","castletown","chargrilled neutral bay","chatterbox cafe","chinese","chocolateria san churro ospringfield","copc","cuisine","dancing bear","degani","dh and gh hospitality","donana","doodee boran","dumpling","earlville","eat sushi","emu hall bar","ewingsdale","famous food","fish magic","gold bamboo","gravatt","grounds cbd","haeduri","hikari ramen","hokkaido garden city","hushang","izakaya","jackpot dining cityau","jacobs ladder cafe","japanese","jimmy's falafel","kazi beach","kazzi beach","kitchen","la queen dessert and d","local tap house","luo charcoal hse","malatang","merci","merivale","mika two cairns","mogom fitzgerald avau","mount waverle","noodle","origin kebabs","pancake manor","parklands by the sea","persian rug house","pho ba nga","rende vous vietn","restaura","roast hil","ros niyom park","sakeichiban","sakuraya","samesame fortitude valau","sapporo","satay boss","sizzling","soho bar","sq *a la bonne franquette","sq *archie dai dien","sq *banh mi muoi 2 vie","sq *clayfield cantina","sq *essence coffee","sq *khang khang","sq *platform pho darra","sq *saigon bbq","sq *vietgarden","sq *yes way jose","steyne hotel","surfers kebab","sushi hiroba","sushi shed park","sushi star","sushicraft drewvale","sushitrain burpengary burpengary","temptation","terrific whsunnybank","that viet place","the braised bowl","tokoton","topokki hil","touch india","ts healthy cup pty l tdinala","ts healthy cup pty ltd","viet ha wholesale darra","vietnamese","western sydney kebabs","yamas","yat tiu rice","zeng s bbq","zushi california"], "priority": 9, "match_on": "description"},
{"name": "osko_dining", "category": "Dining Out", "keywords": ["lunch","dinner","breakfast","coffee","cafe","restaurant","takeaway"], "priority": 9, "match_on": "osko_reference"},
# Alcohol & Liquor
{"name": "bottle_shops", "category": "Alcohol & Liquor", "keywords": ["bws liquor","bws","liquorland","cellarbrations","dan murphy","dan murphys","1st choice liquor","bottlemart","tallawong cellars","harry's liquor","the vineyard"], "priority": 10, "match_on": "description"},
{"name": "osko_liquor", "category": "Alcohol & Liquor", "keywords": ["alcohol","beer","wine","spirits","bottle shop","liquor"], "priority": 9, "match_on": "osko_reference"},
# Food Delivery
{"name": "food_delivery_apps", "category": "Food Delivery", "keywords": ["uber eats","uber *eats","ubereats","doordash","menulog","deliveroo"], "priority": 10, "match_on": "description"},
{"name": "osko_food_delivery", "category": "Food Delivery", "keywords": ["food delivery","uber eats","doordash","menulog","mcdonalds","woolworths/167 station rd burpengary","coles 4091 caboolture","coles 4545 north lake","180 popcorn mt gra","aaravices ice crea \\wishart au","bay kebabs","boostjgc/shop k153 westfi","braised snack masters runcorn","brooklyn donuts indooroop322","freshhema pty ltd sunnybank","gong cha calamvale","home ice cream seventeen mil","hot star chicken","ichiba - warrigal squ eight mile pl","just coconut","ooh sushi garden c","chargrill woollahra woollahra ns aus","mrs fields domestic a mascot","licks ice cream the entrance ns aus","sushihubwetherillwetherillpar","sq *arie benesh \u2013 milton","paul\u2019s premium produce \u2013 bulimba","the great ozzy bakehouse \u2013 granville","dan murphy\u2019s 1467 \u2013 wolli creek","sq *cedary bakehouse \u2013 fairfield east","frango fairfield east \u2013 fairfield east","harris farm markets \u2013 aus","sq *cedary bakehouse \u2013 greystanes","relay tenancy \u2013 aus","fpb chadstone \u2013 chadstone","bws liquor 3970 \u2013 southbank","sp best of the bone \u2013 mount waverley","harris farm markets \u2013 marrickville","the jerky co nsw2 pty \u2013 wetherill park","sq *noble organics \u2013 somersby","yung lee fresh l0934 \u2013 canley heights","sq *brooklyn boy bagel \u2013 marrickville","aldi stores \u2013 villawood","metro \u2013 canley heights","rami\u2019s pizza & manoosh \u2013 cabramatta","noble kebab \u2013 noble park","sq *kats catch fish & chips \u2013 clayton south","sq *tiffin box \u2013 clayton south","marketplace fresh \u2013 fountain gate","holy macro pty ltd \u2013 endeavour hills","krispy kreme \u2013 fountain gate / narre warren","meats \u2013 caringbah","pty ltd \u2013 caringbah","eftpos withdrawal \u2013 al hana mart","eftpos withdrawal \u2013 yumo foods pty ltd","campbelltown","eftpos withdrawal \u2013 foodify brands pty ltd","leumeah","eftpos withdrawal \u2013 villa mart","villawood","bite *bb soul burger \u2013 sancrox","vending direct \u2013 auckland","wholefarms \u2013 enfield","romeo\u2019s foodhall \u2013 south eveleigh","sq *rodriguez bros \u2013 homebush","ls kuertosh \u2013 darling quarter","lily\u2019s chicken \u2013 stanhope gardens","little india groceries \u2013 stanhope gardens","jalu fragrances \u2013 bexley","ltd \u2013 hawthorn east","eftpos withdrawal \u2013 suzy\u2019s store pty ltd","kogarah vet \u2013 kogarah","big w 0158 \u2013 wetherill park","kikki.k \u2013 indooroopilly","st paul\u2019s book centre \u2013 brisbane","candy shop \u2013 strathfield","*bay kebabs","*bill's burgers","*ispa kebab mitchelton","*viet fresh rolls","balance thai","banh mi","boostjgc/shop","carl's jr","carls jr","chargrill","chicken","collins restaurants","coopers","crisp creek","crust","domino's","dominos","eastern delight","grill'd","guzman","gyg","half time tea bar","hanaichi","hannaichi","haven","hungry jack's","hungry jacks","hungrypanda","jerry's","jianbing club parkinson","kanzo fresh sushi","kebab palace","kfc","korean chicke","lemongrass","mad mex","mcdonald","messina","mitsuki sushi","mixue","nando's","nandos","narangba","noodle box","oporto","pepperlunch","pie face","pineapple pantry","pizza hut","red rooster","rocheda\\unit","roll'd","schnitz","season sushi","smz*pizza","sora sushi","soul origin","subway","sumo salad","surfside snax","sushi sapporo","sushi shed","taco bell","takeaway","tastopia","think asia","waffle time cairns","xiangwei","yo-chi","youfoodz","zambreros","zlr*"], "priority": 9, "match_on": "osko_reference"},
# Clothing
{"name": "clothing_stores", "category": "Clothing & Apparel", "keywords": ["industrie clothing","cotton on","cherry georgiou","h&m","zara","uniqlo","forever new","country road","david jones","myer","bonds","just jeans","glassons","sp costumebox","shoegrab pty ltd guildford ns aus","intersport australia l gardenvale"], "priority": 9, "match_on": "description"},
{"name": "osko_clothing", "category": "Clothing & Apparel", "keywords": ["clothing","dress","shoes","outfit","fashion","uniform","sp purebaby pty ltd collingwood","s nagindas damodar cit labasa fji","alis eden pty ltd jamboree heig","best and less pty limited leichhardt","ls plot and co pty lt mount druitt au","industrie clothing pty bankstown","mar ttini by lana north ryde ns aus","scotch and soda indooroopilly","cwhkogarahkogarahnsaus","deuscustoms sydney au","rita lira spa santiago","polish toowong","honey birdette","perfect potion banyo","footgear dfo brisbane","inditexaustraliaptyltd","spnakedtallowcoescreekql","mermade hair north perthwa au","spkimbo.official coomeraac","jalufragrancesbexley ns aus","mister minit fountain narre warren","valleygirl fountain","lca fountain gate pt","the-iconic","meshki st peters","aje au redfern","lovisa australia hawthorn","mar cs alexandria au","sp fayt the label adamstown au","best and less pty","jcs hair artistry","fresh soul","shoe warehouse homebush nsw","valleygirl castle hill","connor clothing pty blacktown","best and less blacktown","pendless","prouds 435 indooroopill au","salvos stores indooroopilly ql aus","minto mall nsw","love me again mar k milton ql aus","castle hill aus","prouds273wetherillpaau","-the-iconic-","best & less","carindale","cettire internet","city chic","cos.com","crossroads","dangerfield","decjuba","factorie","freepeople.com","general pants","globale /jaded london","jeanswest","karrinyup","leichhardt","lowes","millers","noni b","princess polly","redfern","review","rivers","rochedale","rockmans","seed heritage","sheike","sportsgirl","supre","the iconic","witchery"], "priority": 9, "match_on": "osko_reference"},
# Electronics
{"name": "electronics_stores", "category": "Electronics & Tech", "keywords": ["jb hi-fi","jb hi fi","jb hifi","apple store","apple retail","harvey norman","the good guys","officeworks","netoo store"], "priority": 10, "match_on": "description"},
{"name": "osko_electronics", "category": "Electronics & Tech", "keywords": ["electronics","laptop","phone","router","appliance","gadget","tech","jm&jm qld pty ltd burpengary","accesso","area 51 garden city","brisane","rundle"], "priority": 9, "match_on": "osko_reference"},
# Home & Garden
{"name": "home_garden_stores", "category": "Home & Garden", "keywords": ["bunnings","ikea","temple & webster","bed bath n table","howards storage","spotlight","lincraft","anko","sp lagrace home","h2 australia pty ltd chermside","lc plus pty ltd \\sunnybank au","sydneypesties st mar ys au","sooperations austptyparramatta","adairs","artisan ornaments","bentleigh","burlington","daiso","dusk","florist","harvey norman beddin","homes kilburn kilburn","house","hsw mt gravatt","illusions","luxtralia","miniso","mvr retailers pl mount gravatt","oaklands","pillow talk","purahome","taobao.com","tk maxx","tree of life","vivid dollar","waregami"], "priority": 9, "match_on": "description"},
{"name": "osko_home_garden", "category": "Home & Garden", "keywords": ["homewares","rug","bookshelf","cushion","furniture","garden","curtains","bedding","tools"], "priority": 9, "match_on": "osko_reference"},
# Personal Care
{"name": "salons_beauty", "category": "Personal Care & Beauty", "keywords": ["rouse salon","silk laser","sweet touch nails","smp*sweet touch nails","beauty at the towers","salon","nails","hair ","hairdresser","barbershop","waxing","laser clinic","simon curwood jeweller parramatta ns","pline ph calamvale calamvale - beauty treatment","the trustee for sydn fairfield ns aus","passion 2 perfection blacktown au"], "priority": 10, "match_on": "description"},
{"name": "osko_beauty", "category": "Personal Care & Beauty", "keywords": ["haircut","nails","salon","beauty","wax","facial"], "priority": 9, "match_on": "osko_reference"},
# General Retail
{"name": "general_retail_stores", "category": "General Retail", "keywords": ["kmart","target","big w","presents of mind indooroopilly ql aus","ls pigeonhole chippendale ns aus","roselands ns aus","harvey norman online homebush west ns au","pagewood ns aus","eftpos wdl mr cheap yagoona au","sp burchgrove home braemer","eds mowing & m. mortdale nsw","flower power pty ltd enfield aus","a and h trading pl roselands ns aus","victoria's basement castle hill nsw","hot dollar pty ltd stanhope garnsw","yparho pty ltd kingsgrove au","netbank home bills"], "priority": 8, "match_on": "description"},
{"name": "osko_general_retail", "category": "General Retail", "keywords": ["shopping","gift card","voucher"], "priority": 7, "match_on": "osko_reference"},
# Fuel
{"name": "petrol_stations", "category": "Fuel & Petrol", "keywords": ["otr ","ampol","bp ","shell","caltex","united petroleum","7-eleven fuel","metro petroleum","puma energy","liberty oil","riverstone petroleum","speedway woodpark woodpark nsw"], "priority": 10, "match_on": "description"},
{"name": "osko_fuel", "category": "Fuel & Petrol", "keywords": ["petrol","fuel","diesel"], "priority": 9, "match_on": "osko_reference"},
# Public Transport
{"name": "public_transport", "category": "Public Transport", "keywords": ["transportfornsw","transportfornsw tap","opal","nsw trains","sydney buses","sydney ferries","translink","metrocard","myway"], "priority": 10, "match_on": "description"},
{"name": "osko_public_transport", "category": "Public Transport", "keywords": ["train","bus","ferry","opal","transport","incheon int","bus pass","milton station milton","skybus","tram","translink brisbane","transportmainrds"], "priority": 9, "match_on": "osko_reference"},
# Parking
{"name": "parking", "category": "Parking", "keywords": ["wilson parking","cityofsydney parking","international parking","pointparking","secure parking","care park","parking","parkingaustralia"], "priority": 10, "match_on": "description"},
{"name": "osko_parking", "category": "Parking", "keywords": ["parking","car park"], "priority": 9, "match_on": "osko_reference"},
# Rideshare
{"name": "rideshare_taxi", "category": "Rideshare & Taxi", "keywords": ["uber ","didi","ola cabs","ingogo","13cabs","silver top taxi","gocatch","rides 1 yarrabilba","eximbay*","lyft","sp skipbuddy tingalpa","uber *trip","uber trip","uberdirectau","www.grab.com bangkok thb"], "priority": 9, "match_on": "description"},
{"name": "osko_rideshare", "category": "Rideshare & Taxi", "keywords": ["uber","taxi","cab","ride","rideshare"], "priority": 9, "match_on": "osko_reference"},
# Vehicle
{"name": "vehicle_expenses", "category": "Vehicle Expenses", "keywords": ["toll ","linkt","e-toll","target automotive","seven star window","car wash","supercheap auto","autobarn","repco","midas","ultratune","queen street car w revesby au","flamingo's car was merrylands au","tyres excel merrylands aus","complete carcare seven hills ns aus"], "priority": 9, "match_on": "description"},
{"name": "osko_vehicle", "category": "Vehicle Expenses", "keywords": ["toll","rego","registration","car service","car wash","tint","service","camry","uber *one","johal and josan pty lt","transport for ns etoll","m4 west eastern creekau","99bikes-kogarahcarlton","mascot ns aus","hills ns aus","tap sydney aus","truc sydney ns aus","pass carnegie vi aus","p6 sydney olympi ns aus","transport for nsw tap sydney aus","car park moray st southbank vi aus","searoad ferries queenscliff vi aus","cellopark australia matraville","eftpos wdl speedway woodpark woodpark au","eftpos wdl interpark aus","instamove au ferntree gull vi aus","opuspark sydney nsw","pointparking stanhope stanhope garaus","auburn ns aus","lane cove nor ns","waikeire sa","avis","boat","bp","bursons","caltex","car rental","caravan","citylink","eastlink","eg group","europcar","ezyreg","fitmycar","go card","hertz","infringement","kanahooka petroleum","liberty oil","lpo","marina","mechanic","metro petroleum","mr.bodykits","myki","ola","opal","parking fine","penalty notice","point parking","ptv","puma energy","queensland transport caboolture","queensland transport redcliffe","recreational vehicle","silver top","sunset petroleum","super cheap auto","thrifty","tmr reg renew","trailer","translink","transport for nsw","transurban","uber","united petroleum","vic roads","vicroads","wilson parking","www.vonbibra.com.au"], "priority": 9, "match_on": "osko_reference"},
# Medical
{"name": "medical_dental", "category": "Medical & Dental", "keywords": ["medicare","bulk billing","medical centre","specsavers","dental","dentist","physio","physiotherapy","hills district speech","pathology","radiology","specialist","healthpoint"], "priority": 10, "match_on": "description"},
{"name": "osko_medical", "category": "Medical & Dental", "keywords": ["medical","doctor","hospital","gp","dentist","physio","therapy","specialist","prescription"], "priority": 9, "match_on": "osko_reference"},
# Pharmacy
{"name": "pharmacy_chemist", "category": "Pharmacy & Chemist", "keywords": ["chemist warehouse","priceline","terry white","blooms the chemist","amcal","chemist","pharmacy"], "priority": 10, "match_on": "description"},
{"name": "osko_pharmacy", "category": "Pharmacy & Chemist", "keywords": ["pharmacy","chemist","prescription","medication"], "priority": 9, "match_on": "osko_reference"},
# Fitness
{"name": "fitness_sport", "category": "Fitness & Sport", "keywords": ["f45 training","draig jiujitsu","jiu jitsu sydney","carlileswimming","carlile swimming","sp insport","pilates","yoga","fitness first","anytime fitness","goodlife health","planet fitness","snap fitness","western districts","the reco lab","gjc bella vista","lumus imaging indooroopilly","lca toowong pty ltd","of our kellyville aus","the light house psych moore bankns aus","nathanjohngreensanssoucinsaus","lifeathletehealthburleighheadqlaus","spnutritionwarehouseuppercoomeraql","lifeathletehealth","myhealthmirandamirandansaus","cwh fountain gate narre warren vi aus","cwh casey central narre warren vi aus","supplement mar t narre warren","pline ph waverley gar mulgrave","muscle city cn","cwh fountain gate","eftpos wdl pennas edensorpk phar edensor pa au","mater poche north sydney","vitamins ashfield au","sp jc instruments rocklea ql aus","australian wellness","msd sans souci sans souci","pline ph town hall","paypal *painreliefa","paypal *oxy energy","soul pattinson bh baulkham hilnsw","nswh swslhd bnk cp bankstown nsw","gymshark solihull en gbr","msdsanssoucisanssoucins aus","youngcare south brisban ql aus","onite croydon aus","summitcare baulkham h baulkham hilnsw"], "priority": 10, "match_on": "description"},
{"name": "osko_fitness", "category": "Fitness & Sport", "keywords": ["gym","bjj","jiu jitsu","jiujitsu","fitness","sport","swimming","training","pilates","yoga"], "priority": 9, "match_on": "osko_reference"},
# Health Insurance
{"name": "health_insurance", "category": "Health Insurance", "keywords": ["bupa","medibank","nib ","hcf ","ahm","frank health"], "priority": 10, "match_on": "description"},
{"name": "osko_health_insurance", "category": "Health Insurance", "keywords": ["health insurance","health cover","hospital cover","extras cover","aia aust ltd","hcfhealth","aga ovhc wollongong","aust unity d/dbt","australian unity health","cbhs corporate","cbhs health","defence health","gmhba","hbf","hcf","health fund","nib","nurses health","phoenix health","police health","teachers health"], "priority": 9, "match_on": "osko_reference"},
# Utilities
{"name": "utilities", "category": "Utilities", "keywords": ["agl","origin energy","red energy","alinta energy","energy australia","sydney water","icon water","suez water","jemena","endeavour energy","water bill","electricity bill","gas bill","power bill","energy bill"], "priority": 10, "match_on": "description"},
{"name": "osko_utilities", "category": "Utilities", "keywords": ["electricity","gas bill","water bill","energy bill","power bill","utility"], "priority": 9, "match_on": "osko_reference"},
# Telco
{"name": "telco_internet", "category": "Telecommunications", "keywords": ["optus","telstra","vodafone","superloop","aussie broadband","tpg","iinet","internode","spintel","boost mobile","amaysim","lebara"], "priority": 10, "match_on": "description"},
{"name": "osko_telco", "category": "Telecommunications", "keywords": ["phone bill","internet","broadband","wifi","mobile plan","sim","pristine mobile de niddrie","ls australia pty ltd","xero au","google one barangaroo aus.","amzn prime au membership","primevideochannelssydney","telcopay","internet aus","onepass membership perth wa","activ8me","adobe","aldi mobile","apple.com","canva","dropbox","icloud","kogan mobile","microsoft","msbill","nbn","paramount","southern phone","swoop","youtube premium"], "priority": 9, "match_on": "osko_reference"},
# Streaming
{"name": "streaming_subscriptions", "category": "Streaming & Subscriptions", "keywords": ["netflix","spotify","apple.com/bill","apple.com","disney+","disney plus","stan ","binge ","youtube premium","amazon prime","amznprimea","prime vide","microsoft","adobe","dropbox","icloud","google one","ezypay","ls australia pty ltd","xero au","adobe systems","amznprimea*","amznprimeau membership","apple com bill","apple tv","audible","binge","britbox","fetch tv","foxtel","g.co/helppay#","google *youtube","google paramount","google viki","google youtubepremium","hayu","hubbl","kayo sports","paramount plus","paramount+","paypal *apple.com","paypal *gagaoolala","paypal *hbomax","payto amazon mktplc","prime video","stan","viki asian drama"], "priority": 9, "match_on": "description"},
{"name": "osko_streaming", "category": "Streaming & Subscriptions", "keywords": ["subscription","netflix","spotify","streaming"], "priority": 9, "match_on": "osko_reference"},
# General Insurance
{"name": "general_insurance", "category": "General Insurance", "keywords": ["aami","nrma","youi","budget direct","allianz","gi insurance","real insurance","woolworths insurance","coles insurance"], "priority": 10, "match_on": "description"},
{"name": "osko_general_insurance", "category": "General Insurance", "keywords": ["car insurance","home insurance","contents insurance","insurance premium","cover"], "priority": 9, "match_on": "osko_reference"},
# Bank Fees
{"name": "bank_fees", "category": "Bank Fees & Charges", "keywords": ["summary of interest","fees and charges","account fee","interest charge","monthly fee","overdrawn","dishonour fee","late payment fee","bonniandcosanssouciaus","fee redirected","unpaid payment fee","cba other cash adv fee","account servicing fee","adj increase","annual package fee","exceeding","interest","loan a/c fee","overdraw fee","overlimit fee","transaction","world"], "priority": 10, "match_on": "description"},
# Government
{"name": "government_council", "category": "Government & Council", "keywords": ["council rates","services australia","rms.nsw","service nsw","ato","tax office","centrelink","medicare levy","road and maritime","byron shire council mullumbimby"], "priority": 10, "match_on": "description"},
{"name": "osko_government", "category": "Government & Council", "keywords": ["council rates","fine","rego","government","rates","nsw police force parramatta","bscy pty ltd banksia","gibb accountants","land tax","property tax","stamp duty"], "priority": 9, "match_on": "osko_reference"},
# Education
{"name": "private_education", "category": "Private Education", "keywords": ["rouse hill anglican","school fees","tutoring","school","college","university","tafe","udemy","coursera","educational fun","i s s p and c sho st lucia ql","indooroopilly high pc indooroopilly","p.skool.com/qmjswelsegundocausa"], "priority": 10, "match_on": "description"},
{"name": "osko_education", "category": "Private Education", "keywords": ["school fees","tuition","tutoring","education","uniform","ahpra","catholic education","christian school","ezypay*kumon","grammar school","independent school","international school","paypal *acloudguru","private college","private school","professional development","qbs international","reifytraining","school books","school sport","school uniform","sports fees","wellington","whop*under450k","www..com"], "priority": 9, "match_on": "osko_reference"},
# Childcare
{"name": "childcare", "category": "Childcare", "keywords": ["kindy","childcare","daycare","oshc","nanny","stretton early years & ki"], "priority": 10, "match_on": "description"},
{"name": "osko_childcare", "category": "Childcare", "keywords": ["childcare","daycare","kindy","nanny","oshc"], "priority": 9, "match_on": "osko_reference"},
# Children & Family
{"name": "children_family", "category": "Children & Family", "keywords": ["baby bunting","baby kingdom","mothercare","toys r us","smiggle","kidstuff"], "priority": 9, "match_on": "description"},
{"name": "osko_children_family", "category": "Children & Family", "keywords": ["child","kids","baby","toy","noah"], "priority": 8, "match_on": "osko_reference"},
# Entertainment
{"name": "entertainment_venues", "category": "Entertainment", "keywords": ["hoyts","event cinemas","village cinemas","reading cinemas","ticketek","ticketmaster","humanitix","moshtix","cricket australia","merivale","rhg pty ltd","team zero","wise wife pty ltd","ce imaging hassall grove nsw au","city of blacktown rsl blacktown aus","tatts online pty ltd","johanna haas the entrance"], "priority": 10, "match_on": "description"},
{"name": "osko_entertainment", "category": "Entertainment", "keywords": ["entertainment","movie","concert","event","tickets","outing","birthday","party","gift","celebration","easter","christmas","valentines","flowers"], "priority": 9, "match_on": "osko_reference"},
# Gambling
{"name": "gambling", "category": "Gambling", "keywords": ["sportsbet","tabcorp","tab ","oz lotteries","tatts","tatterslotto","lotterywest","the lott","bet365","palmerbet","pointsbet","lotto"], "priority": 10, "match_on": "description"},
# Travel
{"name": "travel_accommodation", "category": "Travel & Accommodation", "keywords": ["qantas","jetstar","virgin australia","rex airlines","airbnb","booking.com","expedia","hotels.com","wotif","trivago","agoda","accorhotels","hilton","marriott","holiday inn","travelex"], "priority": 10, "match_on": "description"},
{"name": "osko_travel", "category": "Travel & Accommodation", "keywords": ["flight","hotel","holiday","accommodation","travel","airbnb"], "priority": 9, "match_on": "osko_reference"},
# Pets
{"name": "pet_services", "category": "Pet Expenses", "keywords": ["rufftrack","sq *rufftrack","petbarn","petstock","paws n claws","animates","greencross vet","vet ","veterinaryspecialistsmirandaau","spbestofthebonemountwaverleviaus","hurlstoneprk dulwich hill ns aus","petfood plus punchbowl aus"], "priority": 10, "match_on": "description"},
{"name": "osko_pet", "category": "Pet Expenses", "keywords": ["pet","dog","cat","vet","grooming","kennel","boarding"], "priority": 9, "match_on": "osko_reference"},
# Online Marketplace
{"name": "online_marketplace", "category": "Online Marketplace", "keywords": ["amazon au","amazon reta","amazon marketplace","ebay","etsy","shein","temu","catch.com","kogan","mighty ape"], "priority": 9, "match_on": "description"},
{"name": "osko_online_marketplace", "category": "Online Marketplace", "keywords": ["amazon","ebay","online","marketplace"], "priority": 8, "match_on": "osko_reference"},
# BNPL
{"name": "bnpl", "category": "Buy Now Pay Later", "keywords": ["afterpay","humm","zip ","zippay","klarna","laybuy","openpay","ezi failpay"], "priority": 9, "match_on": "description"},
# Excluded
{"name": "wages_salary", "category": "Wages & Salary", "keywords": ["direct credit","salary","payroll"], "priority": 10, "match_on": "description", "tx_type_filter": "credit"},
{"name": "internal_transfers", "category": "Internal Transfer", "keywords": ["transfer from","transfer to","own account","self transfer"], "priority": 8, "match_on": "description"},
{"name": "external_transfers", "category": "External Transfer", "keywords": ["osko","npp payment","fast transfer","pay anyone","bpay"], "priority": 5, "match_on": "description"},
{"name": "osko_transfer_generic", "category": "External Transfer", "keywords": ["return","repay","lend","thanks","thank you","living expenses","expenses","general expenses"], "priority": 5, "match_on": "osko_reference"},
{"name": "refunds_returns", "category": "Refunds & Returns", "keywords": ["refund","return","reversal","cashback","credit adjustment"], "priority": 9, "match_on": "description", "tx_type_filter": "credit"},
{"name": "other_income", "category": "Other Income", "keywords": ["interest earned","term deposit","dividend","rental income"], "priority": 9, "match_on": "description", "tx_type_filter": "credit"},
{"name": "financial_services", "category": "Financial Services", "keywords": ["kraken","coinspot","coinbase","independent reserve","chess","commsec","stake","superhero"], "priority": 9, "match_on": "description"},
{"name": "debt_collection", "category": "Debt Collection", "keywords": ["state debt","debt recovery","collection agency","collection service"], "priority": 10, "match_on": "description"},
{"name": "third_party_payment", "category": "Third Party Payment", "keywords": ["paypal"], "priority": 4, "match_on": "description"},
# ── OSKO purpose rules ──────────────────────────────────────────────────
# match_on = "osko_purpose" β†’ matched against the extracted free-text
# purpose field (the human note between account number and REFERENCE
# in OSKO payments). Distinct from osko_reference rules which match
# the sender's name.
{"name": "osko_purpose_home_loan", "category": "External Transfer", "keywords": ["home loan", "mortgage", "loan repayment", "loan payment", "offset account", "homeloan"], "priority": 10, "match_on": "osko_purpose"},
{"name": "osko_purpose_rent", "category": "External Transfer", "keywords": ["rent", "rental", "landlord", "lease payment", "bond"], "priority": 10, "match_on": "osko_purpose"},
{"name": "osko_purpose_utilities", "category": "Utilities", "keywords": ["water bill", "electricity", "gas bill", "power bill", "energy bill", "utility bill", "utilities"], "priority": 10, "match_on": "osko_purpose"},
{"name": "osko_purpose_insurance", "category": "General Insurance", "keywords": ["insurance", "insure", "car insurance", "home insurance", "contents insurance", "health insurance", "cover"], "priority": 10, "match_on": "osko_purpose"},
{"name": "osko_purpose_medical", "category": "Medical & Dental", "keywords": ["medical","doctor","hospital","dental","dentist","physio","pharmacy","chemist","hmo","health fund","specialist","therapy","*knockout nutrition","australian hearing","buderim sportsphys","clearly","clinic","coast joint care","counselling","discount drug","dresden vision","dsouza dias","eye care","family planning nsw","glasses","health","iherb iherb.com iherb.com","mcare benefits","myllocaldoc","mylocaldoc","nswh nbmlhd nep","opsm","optical superstore","optometrist","optometry","oscar wylee","psychiatry","psychologist","the knee joint","updoc.com.au","xray & imag marooch"], "priority": 10, "match_on": "osko_purpose"},
{"name": "osko_purpose_childcare", "category": "Childcare", "keywords": ["childcare", "child care", "daycare", "day care", "kindy", "kindergarten", "nanny", "oshc"], "priority": 10, "match_on": "osko_purpose"},
{"name": "osko_purpose_family_maintenance", "category": "Children & Family", "keywords": ["family maintenance", "child support", "maintenance", "family support"], "priority": 10, "match_on": "osko_purpose"},
{"name": "osko_purpose_school", "category": "Private Education", "keywords": ["school fees", "school fee", "tuition", "tutoring", "uniform", "education", "college fees"], "priority": 9, "match_on": "osko_purpose"},
{"name": "osko_purpose_groceries", "category": "Groceries", "keywords": ["groceries", "grocery", "supermarket", "food shop", "food shopping"], "priority": 9, "match_on": "osko_purpose"},
{"name": "osko_purpose_vehicle", "category": "Vehicle Expenses", "keywords": ["rego", "registration", "car service", "vehicle service", "car repair", "tyre", "toll", "car wash"], "priority": 9, "match_on": "osko_purpose"},
{"name": "osko_purpose_gym_fitness", "category": "Fitness & Sport", "keywords": ["gym", "fitness", "pilates", "yoga", "swimming", "sport", "training", "bjj", "jiu jitsu"], "priority": 9, "match_on": "osko_purpose"},
{"name": "osko_purpose_dining", "category": "Dining Out", "keywords": ["lunch", "dinner", "breakfast", "coffee", "cafe", "restaurant", "takeaway", "takeout"], "priority": 9, "match_on": "osko_purpose"},
{"name": "osko_purpose_travel", "category": "Travel & Accommodation", "keywords": ["flight", "hotel", "holiday", "accommodation", "travel", "airbnb", "booking"], "priority": 9, "match_on": "osko_purpose"},
{"name": "osko_purpose_pet", "category": "Pet Expenses", "keywords": ["vet", "pet", "dog", "cat", "grooming", "kennel", "boarding"], "priority": 9, "match_on": "osko_purpose"},
{"name": "osko_purpose_entertainment", "category": "Entertainment", "keywords": ["birthday", "party", "event", "concert", "movie", "tickets", "outing", "celebration", "christmas", "easter", "flowers", "gift"], "priority": 8, "match_on": "osko_purpose"},
{"name": "osko_purpose_living_expenses", "category": "General Retail", "keywords": ["living expenses", "expenses", "general expenses", "household", "shopping"], "priority": 5, "match_on": "osko_purpose"},
# ── New rules added from updated_categorization_tree.xlsx ──────────────
# Third Party Payment
{"name": "rule_third_party_payment", "category": "Third Party Payment", "keywords": ["paypal australia","thanh bui buddha richard marx","trinh pham"], "priority": 10, "match_on": "description"},
# Childcare
{"name": "extracurricular", "category": "Childcare", "keywords": ["au pair","baby","child","extracurricular","family support","kids","maintenance","mothercare","noah","plains state hredbank","pre-school","preschool","smiggle","toy"], "priority": 10, "match_on": "description"},
# Clothing & Apparel
{"name": "rule_activewear", "category": "Clothing & Apparel", "keywords": ["lorna jane","lornajaneau_pos mt gravatt","lululemon"], "priority": 10, "match_on": "description"},
{"name": "shoes_footwear", "category": "Clothing & Apparel", "keywords": ["athlete's foot","athletes foot","betts","billini","crocs","diana ferrari","foot locker","footwear","hype dc","mercer","platypus","sneakers","williams","wittner"], "priority": 10, "match_on": "description"},
{"name": "rule_underwear_and_lingerie", "category": "Clothing & Apparel", "keywords": ["bras n things","bras'n'things"], "priority": 10, "match_on": "description"},
# Personal Care & Beauty
{"name": "beauty_treatments", "category": "Personal Care & Beauty", "keywords": ["beauty salon","beauty spa","cosmetic","day spa","endota","golden tree thai mas","massage","nail","retreat","sarah j dean","stacey hampshire","t chua surgical"], "priority": 10, "match_on": "description"},
{"name": "cosmetics_toiletries", "category": "Personal Care & Beauty", "keywords": ["aesop","cosmetics","fleur","lancome loreal","lush","mecca","sephora","skinkandy north lakes","toiletries"], "priority": 10, "match_on": "description"},
# Clothing & Personal Care
{"name": "auto_learned_paypal_brandcollec", "category": "Clothing & Personal Care", "keywords": ["paypal *brandcollec"], "priority": 10, "match_on": "description"},
{"name": "accessories", "category": "Clothing & Personal Care", "keywords": ["accessories","anaconda","barbershop","bcf","beauty","charli and kate","city beach","dotti","dusk australasia","facial","fantastic hairdresser","jewellery","kathmandu","laser clinic","lucky charm","nails","oroton","peter alexander","purebaby collingwood","salon","shein","silk laser","sussans","swarovski","wax","simon curwood jeweller parramatta ns"], "priority": 10, "match_on": "description"},
# Groceries
{"name": "rule_convenience_store", "category": "Groceries", "keywords": ["*the local snack","departuau","incheon","northlakes","vending simplicity wakerley"], "priority": 10, "match_on": "description"},
{"name": "rule_fruit_and_veg_market", "category": "Groceries", "keywords": ["blunder road country markets","fresh food burpengary","fruit","jindalee","produce airp","spuds"], "priority": 10, "match_on": "description"},
# General Insurance
{"name": "home_contents_insurance", "category": "General Insurance", "keywords": ["aust unity","bingle insurance","building insurance","cgu","gio","raa","racq","suncorp","third party motor"], "priority": 10, "match_on": "description"},
# Fitness & Sport
{"name": "rule_gym_and_swimming", "category": "Fitness & Sport", "keywords": ["*brisbane","aquatic centre","bravo fit","city aquatics morningside","crossfit","dunlop park swimming c","ezypay*power moves austra","f45","goodlife","jetts","orange theory","orangetheory","sq *knockout nutrition","swim lessons","swimming lessons","swimming pool"], "priority": 10, "match_on": "description"},
{"name": "rule_sportswear", "category": "Fitness & Sport", "keywords": ["mizuno mulgrave","rebel","intersport australia l gardenvale"], "priority": 10, "match_on": "description"},
# Entertainment
{"name": "rule_collectibles_and_hobbies", "category": "Entertainment", "keywords": ["arcade trader","pop mart","vivace music"], "priority": 10, "match_on": "description"},
{"name": "movies_events", "category": "Entertainment", "keywords": ["amigo entertainment","bne concert sound northgate","catering","fever*balloon story","function"], "priority": 10, "match_on": "description"},
{"name": "rule_ticketing", "category": "Entertainment", "keywords": ["qtix"], "priority": 10, "match_on": "description"},
# Miscellaneous
{"name": "rule_infrequent_other", "category": "Miscellaneous", "keywords": ["bargain central","hot bargain orion pt"], "priority": 10, "match_on": "description"},
{"name": "rule_additional_expenses", "category": "Miscellaneous", "keywords": ["abc investment","accommodation","airport","best western","bonza","bookshop","booktopia","ibis","luxury escapes","newsagent","oaks hotels","payment thankyou","peppers","quest apartments","rydges","stationery","together","webjet","pline ph calamvale calamvale - beauty treatment","the trustee for sydn fairfield ns aus","passion 2 perfection blacktown au"], "priority": 10, "match_on": "description"},
{"name": "misc_excluded_credits", "category": "Miscellaneous", "keywords": ["salary","wages","sushi tamashii","transfer from","payment from"], "priority": 10, "match_on": "description"},
{"name": "misc_excluded_debits", "category": "Miscellaneous", "keywords": ["fund transfer","national crime check p north sydney","honcho-businessservice","qatt development pty l north lakes","account servicing fee","dishonour fee","brd and associates pty"], "priority": 10, "match_on": "description"},
{"name": "misc_emi_and_loan_repayments", "category": "Miscellaneous", "keywords": ["taurus motor fin - if the debit is recurring","westpac equipmen","help repayments","lease payments","interest repayments on loans"], "priority": 10, "match_on": "description"},
{"name": "misc_non_hem_included_expenses", "category": "Miscellaneous", "keywords": ["private school fees","private life insurance","sickness and personal accident insurance","alimony or maintenance payments"], "priority": 10, "match_on": "description"},
# Government & Council
{"name": "rule_council_rates", "category": "Government & Council", "keywords": ["bcc rates","body corporate","city & regional council","ipswich city council","landata","logan city rates","strata","sunshine coast council"], "priority": 10, "match_on": "description"},
# General Retail
{"name": "rule_department_store", "category": "General Retail", "keywords": ["amzn.com/bill","catch docklands","dept.(central","myer","westfield"], "priority": 10, "match_on": "description"},
{"name": "retail_general", "category": "General Retail", "keywords": ["amazon","catch.com","ebay","etsy","kakabuy","kogan","marketplace","mighty ape","online","taobao","temu","vcs_westfieldmtgravatt","westfield garden cit upper mt gra"], "priority": 10, "match_on": "description"},
# Government Education
{"name": "rule_government_education", "category": "Government Education", "keywords": ["department of educatio","educational fun","i s s p and c sho st lucia ql","indooroopilly high pc indooroopilly","p.skool.com/qmjswelsegundocausa"], "priority": 10, "match_on": "description"},
# Dining Out
{"name": "rule_bar_and_pub", "category": "Dining Out", "keywords": ["beach house bar","darra snack barloc thien darra","eatons hill hotel","felons brewing corinda","lucky tree tavern","sq *courtside brew","sq *sunshine bar & gri","www.beachhousebargrill"], "priority": 10, "match_on": "description"},
{"name": "rule_cafe_and_coffee", "category": "Dining Out", "keywords": ["*butter","*grind roast master","*juicy","*little dessert","acai","addictea","ashgrove bakehouse","bagel boys","ben and jerrys","bisou bakery","cappuccino","charleys","chatime townsville pimlico","dts patisseries","espresso","foster black","gelatissimo","gelato","gloria jeans","hallowed","latte","le bon choix","ls luv beans","michel's patisserie","monday cafe","mountview cafe","muffin break","nxt*cream house shop1 sunnybank hilql","papas pancakes","papparoti at vinacafe ina","seasoned cafe","single origin","smp*cafe 63 ascot ascot04","sq *kenny s bakery","sq *lord pretzel","sq *mountview bakehous","starbucks","torrefazion","www.muddyscafe.com","yogost","zlr*bloom patisserie gc","zlr*tea"], "priority": 10, "match_on": "description"},
# Other Income
{"name": "rule_rental_income", "category": "Other Income", "keywords": ["and mngmnt mosman","prime place prop"], "priority": 10, "match_on": "description"},
# Wages & Salary
{"name": "rule_salary_credit", "category": "Wages & Salary", "keywords": ["ausbuild pty ltd ausbuild pty ltd","minh pham:","salary ausbuild pty ltd","salary ausbuild:"], "priority": 10, "match_on": "description"},
# Credit Card
{"name": "rule_credit_card", "category": "Credit Card", "keywords": ["cc payment","commbank"], "priority": 10, "match_on": "description"},
# Superannuation
{"name": "rule_superannuation", "category": "Superannuation", "keywords": ["super chain pty ltd rochedale","zlr*the"], "priority": 10, "match_on": "description"},
# Telecommunications
{"name": "internet_broadband", "category": "Telecommunications", "keywords": ["dish","dodo","rush broadband\\"], "priority": 10, "match_on": "description"},
{"name": "phone_mobile", "category": "Telecommunications", "keywords": ["belong","catch connect","circles life","felix mobile","med*aldimobile","rs rewards club","woolies mobile","wooliesmobile","woolworths mobile"], "priority": 10, "match_on": "description"},
# Streaming & Subscriptions
{"name": "rule_software_subscription", "category": "Streaming & Subscriptions", "keywords": ["francisco","istock.com internet","livingai","xero inv-\\","xero.com"], "priority": 10, "match_on": "description"},
# Internal Transfer
{"name": "rule_inter_account_transfer", "category": "Internal Transfer", "keywords": ["bank transfer","credit from","internet banking transfer","internet transfer","offset transfer","online transfer","out branch hills","outward d/e","savings transfer","tfr westpac bus"], "priority": 10, "match_on": "description"},
# Vehicle Expenses
{"name": "rule_car_rental", "category": "Vehicle Expenses", "keywords": ["cars booking london","redspot moorooka moorooka"], "priority": 10, "match_on": "description"},
{"name": "rule_car_wash_and_service", "category": "Vehicle Expenses", "keywords": ["smithfield carwash","sparkletown car wash","sparkletown operations"], "priority": 10, "match_on": "description"},
# Fuel & Petrol
{"name": "fuel_petrol", "category": "Fuel & Petrol", "keywords": ["arundel","challenge","chevron australia downstream","liberty","otr","pearl","reddy express","united"], "priority": 10, "match_on": "description"},
]
def seed(actor: str = "system") -> None:
db = SessionLocal()
try:
created_cats = skipped_cats = created_rules = skipped_rules = 0
# 1. Seed categories
cat_by_name: dict[str, ClassificationCategory] = {}
for cat_name, mapping in CATEGORY_TO_SHEET.items():
existing = db.query(ClassificationCategory).filter_by(name=cat_name).first()
if existing:
sheet, sub = (mapping[0], mapping[1]) if mapping else (None, None)
if existing.sheet_name != sheet or existing.sub_column != sub:
existing.sheet_name = sheet
existing.sub_column = sub
existing.updated_by = actor
db.flush()
cat_by_name[cat_name] = existing
skipped_cats += 1
else:
sheet, sub = (mapping[0], mapping[1]) if mapping else (None, None)
cat = ClassificationCategory(
name=cat_name,
sheet_name=sheet,
sub_column=sub,
created_by=actor,
updated_by=actor,
)
db.add(cat)
db.flush() # get the id
cat_by_name[cat_name] = cat
created_cats += 1
db.commit()
# 2. Seed rules
for rule_data in RULES_DATA:
existing = db.query(ClassificationRule).filter_by(name=rule_data["name"]).first()
if existing:
skipped_rules += 1
continue
cat_obj = cat_by_name.get(rule_data["category"])
if not cat_obj:
print(f" ⚠️ Unknown category '{rule_data['category']}' for rule '{rule_data['name']}' β€” skipping")
continue
rule = ClassificationRule(
name=rule_data["name"],
category_id=cat_obj.id,
priority=rule_data.get("priority", 5),
match_on=rule_data.get("match_on", "description"),
tx_type_filter=rule_data.get("tx_type_filter"),
created_by=actor,
updated_by=actor,
)
rule.keywords = rule_data.get("keywords", [])
db.add(rule)
created_rules += 1
db.commit()
print(
f"βœ… Seed complete β€” "
f"categories: {created_cats} created, {skipped_cats} skipped | "
f"rules: {created_rules} created, {skipped_rules} skipped"
)
except Exception as exc:
db.rollback()
print(f"❌ Seed failed: {exc}")
raise
finally:
db.close()
if __name__ == "__main__":
seed()