Billing migration 009 duplicate-column cleanup results Date: 2026-11-05 Scope ----- This report records the requested cleanup of duplicate billing columns introduced by migration 009. No tables or features were added in this turn. Existing migrations 005-009 were not edited. Duplicate column pairs identified --------------------------------- subscriptions.period -> subscriptions.billing_cycle subscriptions.started_at -> subscriptions.starts_at subscriptions.current_period_end -> subscriptions.ends_at payments.period -> payments.billing_cycle payments.utr -> payments.utr_number payments.screenshot_path -> payments.screenshot invoices.invoice_no -> invoices.invoice_number invoices.amount -> invoices.subtotal invoices.tax_percent -> invoices.tax_rate Canonical names retained ------------------------ subscriptions: billing_cycle, starts_at, ends_at payments: billing_cycle, utr_number, screenshot invoices: invoice_number, subtotal, tax_rate Migration --------- Added database/migrations/010_remove_duplicate_billing_columns.sql. It preserves non-empty alias-only values into canonical columns where canonical values were empty/default, then drops the duplicate columns. Applied command: php scripts\migrate.php Output: Database migrations completed. Migrations recorded in the live database: 004_part3b.sql 005_billing_payments_invoices.sql 006_razorpay_webhooks.sql 007_superadmin.sql 008_analytics_settings.sql 009_subscription_billing_schema.sql 010_remove_duplicate_billing_columns.sql Before cleanup, live data check: subscriptions: 1 row; no period/date values existed only in aliases payments: 0 rows invoices: 0 rows No alias-only data needed copying in this database. Final live DESCRIBE subscriptions --------------------------------- Field Type Null Key Default Extra id bigint(20) unsigned NO PRI NULL auto_increment store_id bigint(20) unsigned NO MUL NULL plan_id bigint(20) unsigned NO MUL NULL pending_plan_id bigint(20) unsigned YES MUL NULL pending_billing_cycle enum('monthly','yearly') YES NULL status enum('pending','trial','active','grace','expired','cancelled') NO MUL pending trial_ends_at datetime YES NULL billing_cycle enum('monthly','yearly') NO monthly starts_at datetime YES NULL ends_at datetime YES NULL pending_plan_at datetime YES NULL cancel_at_period_end datetime YES NULL grace_ends_at datetime YES NULL reminder_7_sent_at datetime YES NULL reminder_1_sent_at datetime YES NULL created_at timestamp NO current_timestamp() updated_at timestamp NO current_timestamp() on update current_timestamp() Final live DESCRIBE payments ---------------------------- Field Type Null Key Default Extra id bigint(20) unsigned NO PRI NULL auto_increment subscription_id bigint(20) unsigned YES MUL NULL plan_id bigint(20) unsigned YES MUL NULL billing_cycle enum('monthly','yearly') NO monthly period_start datetime YES NULL period_end datetime YES NULL store_id bigint(20) unsigned NO MUL NULL amount decimal(10,2) NO NULL currency char(3) NO INR method enum('razorpay','manual') NO NULL status enum('pending','paid','failed','rejected','refunded') NO MUL pending razorpay_order_id varchar(120) YES UNI NULL razorpay_payment_id varchar(120) YES UNI NULL razorpay_signature varchar(255) YES NULL utr_number varchar(100) YES NULL screenshot varchar(255) YES NULL reviewed_by bigint(20) unsigned YES MUL NULL reviewed_at datetime YES NULL paid_email_sent_at datetime YES NULL reject_reason text YES NULL reason text YES NULL created_at timestamp NO current_timestamp() updated_at timestamp NO current_timestamp() on update current_timestamp() Final live DESCRIBE invoices ---------------------------- Field Type Null Key Default Extra id bigint(20) unsigned NO PRI NULL auto_increment invoice_number varchar(40) NO UNI NULL payment_id bigint(20) unsigned NO UNI NULL store_id bigint(20) unsigned NO MUL NULL plan_id bigint(20) unsigned YES MUL NULL billing_cycle enum('monthly','yearly') NO monthly currency char(3) NO INR subtotal decimal(10,2) NO NULL tax_rate decimal(7,4) NO 0.0000 tax_amount decimal(10,2) NO 0.00 total decimal(10,2) NO NULL tax_identifier varchar(100) YES NULL seller_name varchar(190) YES NULL seller_address text YES NULL seller_tax_identifier varchar(100) YES NULL customer_name varchar(190) NO NULL customer_email varchar(190) NO NULL customer_address text YES NULL customer_tax_identifier varchar(100) YES NULL period_start datetime YES NULL period_end datetime YES NULL pdf_path varchar(255) YES NULL issued_at datetime NO NULL created_at timestamp NO current_timestamp() updated_at timestamp NO current_timestamp() on update current_timestamp() Grep findings ------------- Before: targeted search found 26 matches across 9 files. Hits involving "period" and "utr" were user-facing billing cycle labels, variables, or form parameters; application code was already using the canonical database names. No application references to started_at, current_period_end, screenshot_path, invoice_no, or tax_percent were found. After: no references to the removed database aliases remain in app, tests, or scripts. The remaining "utr" occurrences are the manual-payment form/request parameter and related PHP variables; the database column remains utr_number. There is no standalone views or cron directory; views are under app/Views. Retained 009 columns without old equivalents -------------------------------------------- subscriptions.trial_ends_at subscriptions.cancel_at_period_end Both remain in the database. Neither currently has an explicit application-code reference; their behavior was not implemented in this cleanup-only turn. Existing invoice columns invoice_number, subtotal, tax_rate, tax_amount, total, pdf_path, and issued_at are used by invoice code. Test output ----------- Command: php tests\subscription_service_test.php Output: subscription_service_test: 24 assertions passed Command: php tests\analytics_settings_test.php Output: PASS: analytics metrics include at least one order PASS: analytics metrics include real revenue PASS: sales trend returns real daily data PASS: whatsapp click funnel is counted PASS: advanced analytics feature is unlocked for Growth PASS: orders CSV export has content PASS: analytics data endpoint is registered PASS: analytics tracking endpoint is registered analytics_settings_test: 8 checks passed Command: php tests\smoke.php Output: FAIL - Installer endpoint: Set SMOKE_BASE_URL to a dedicated test installation. FAIL - Owner login: Set SMOKE_OWNER_EMAIL and SMOKE_OWNER_PASSWORD. FAIL - Create store: Login prerequisite failed. FAIL - Add product: Login prerequisite failed. FAIL - View /{slug}: Set SMOKE_BASE_URL to a dedicated test installation. FAIL - Add to cart: Set SMOKE_BASE_URL to a dedicated test installation. FAIL - Checkout: Set SMOKE_BASE_URL to a dedicated test installation. FAIL - Invoice PDF download (pre-generated invoice): Login and SMOKE_INVOICE_ID from a settled payment are required. FAIL - Manual plan upgrade to pending payment: Login, SMOKE_PLAN_ID, and SMOKE_PAYMENT_SCREENSHOT are required. Smoke test status: not verified. The dedicated test URL, owner credentials, settled invoice ID, and payment screenshot were not configured. PHP syntax checks ----------------- No syntax errors detected in app\Core\Migrator.php No syntax errors detected in tests\smoke.php No syntax errors detected in tests\subscription_service_test.php No syntax errors detected in tests\analytics_settings_test.php Files changed ------------- database/migrations/010_remove_duplicate_billing_columns.sql all_modifications_summary.txt