95 lines
3.5 KiB
Python
95 lines
3.5 KiB
Python
"""
|
|
Migration: add Plaid tables.
|
|
|
|
Run once after deploying the Plaid integration:
|
|
python scripts/add_plaid_tables.py
|
|
|
|
Creates:
|
|
plaid_items — one row per connected bank (Item in Plaid terminology)
|
|
plaid_accounts — one row per Plaid account, mapped to a PFM account
|
|
plaid_sync_previews — temporary storage for transaction sync previews
|
|
"""
|
|
|
|
import sys
|
|
import os
|
|
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
|
|
|
|
from app import create_app
|
|
from app.extensions import db
|
|
|
|
|
|
TABLES = [
|
|
# plaid_items
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS `plaid_items` (
|
|
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
`item_id` VARCHAR(100) NOT NULL UNIQUE,
|
|
`access_token` TEXT NOT NULL,
|
|
`institution_id` VARCHAR(50) DEFAULT NULL,
|
|
`institution_name` VARCHAR(100) DEFAULT NULL,
|
|
`cursor` VARCHAR(500) DEFAULT NULL,
|
|
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`last_synced_at` DATETIME DEFAULT NULL,
|
|
INDEX `ix_plaid_items_item_id` (`item_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
""",
|
|
|
|
# plaid_accounts
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS `plaid_accounts` (
|
|
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
`item_id` INT NOT NULL,
|
|
`plaid_account_id` VARCHAR(100) NOT NULL UNIQUE,
|
|
`pfm_account_id` INT DEFAULT NULL,
|
|
`account_name` VARCHAR(100) DEFAULT NULL,
|
|
`account_type` VARCHAR(50) DEFAULT NULL,
|
|
`account_subtype` VARCHAR(50) DEFAULT NULL,
|
|
`mask` VARCHAR(10) DEFAULT NULL,
|
|
`last_sync_date` DATE DEFAULT NULL,
|
|
`cc_due_date` DATE DEFAULT NULL,
|
|
`cc_minimum_payment` DECIMAL(12,2) DEFAULT NULL,
|
|
`cc_last_statement_balance` DECIMAL(12,2) DEFAULT NULL,
|
|
`cc_is_overdue` TINYINT(1) NOT NULL DEFAULT 0,
|
|
`cc_updated_at` DATETIME DEFAULT NULL,
|
|
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`item_id`) REFERENCES `plaid_items`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`pfm_account_id`) REFERENCES `accounts`(`id`) ON DELETE SET NULL,
|
|
INDEX `ix_plaid_accounts_plaid_account_id` (`plaid_account_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
""",
|
|
|
|
# plaid_sync_previews
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS `plaid_sync_previews` (
|
|
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
`item_id` INT NOT NULL UNIQUE,
|
|
`data_json` TEXT NOT NULL,
|
|
`next_cursor` VARCHAR(500) DEFAULT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`item_id`) REFERENCES `plaid_items`(`id`) ON DELETE CASCADE,
|
|
INDEX `ix_plaid_sync_previews_item_id` (`item_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
""",
|
|
]
|
|
|
|
|
|
def run():
|
|
app = create_app()
|
|
with app.app_context():
|
|
conn = db.engine.raw_connection()
|
|
cur = conn.cursor()
|
|
for ddl in TABLES:
|
|
name = ddl.strip().split('`')[1]
|
|
cur.execute(ddl)
|
|
print(f' ✓ {name}')
|
|
conn.commit()
|
|
cur.close()
|
|
conn.close()
|
|
print('Done.')
|
|
|
|
|
|
if __name__ == '__main__':
|
|
run()
|