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
