[PR #377] [MERGED] Add subscription table constraints and safe dedupe/index migration #683

New issue

Closed

opened 2026-03-13 21:03:43 +03:00 by kerem · 0 comments

kerem commented

2026-03-13 21:03:43 +03:00

Owner

📋 Pull Request Information

Original PR: https://github.com/AJaySi/ALwrity/pull/377
Author: @AJaySi
Created: 3/4/2026
Status: ✅ Merged
Merged: 3/5/2026
Merged by: @AJaySi

Base: main ← Head: codex/add-constraints-and-indexes-to-models

📝 Commits (1)

6e75f44 Add subscription usage constraints and safe index migration

📊 Changes

2 files changed (+246 additions, -1 deletions)

View changed files

📝 backend/models/subscription_models.py (+14 -1)
➕ backend/scripts/run_subscription_constraints_migration.py (+232 -0)

📄 Description

Motivation

Ensure data integrity and improve query performance for subscription usage and renewal tables by adding explicit constraints and indexes.
Provide a safe migration path for existing SQLite user databases that may contain duplicate rows before adding unique constraints.

Description

Added SQLAlchemy Index and UniqueConstraint definitions in backend/models/subscription_models.py for api_usage_logs, usage_summaries, and subscription_renewal_history, including a unique constraint on usage_summaries (user_id, billing_period).
Added composite and single-column indexes on api_usage_logs for user_id, billing_period, timestamp, provider, and common composite lookup patterns.
Added a unique renewal-event constraint and supporting indexes on subscription_renewal_history to speed renewal history lookups.
Implemented backend/scripts/run_subscription_constraints_migration.py, a safe, idempotent migration script that deduplicates existing rows and creates IF NOT EXISTS indexes/unique indexes per user database; it accepts --user_id or runs across discovered user DBs and performs transactional commits/rollbacks.

Testing

Compiled the modified modules with python -m py_compile backend/models/subscription_models.py backend/scripts/run_subscription_constraints_migration.py, which completed successfully.
Validated the migration script CLI with python backend/scripts/run_subscription_constraints_migration.py --help, which printed usage information successfully.

Codex Task

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/AJaySi/ALwrity/pull/377 **Author:** [@AJaySi](https://github.com/AJaySi) **Created:** 3/4/2026 **Status:** ✅ Merged **Merged:** 3/5/2026 **Merged by:** [@AJaySi](https://github.com/AJaySi) **Base:** `main` ← **Head:** `codex/add-constraints-and-indexes-to-models` --- ### 📝 Commits (1) - [`6e75f44`](https://github.com/AJaySi/ALwrity/commit/6e75f44ed5010f55e0cb32d0739c073eb6cbbf52) Add subscription usage constraints and safe index migration ### 📊 Changes **2 files changed** (+246 additions, -1 deletions) <details> <summary>View changed files</summary> 📝 `backend/models/subscription_models.py` (+14 -1) ➕ `backend/scripts/run_subscription_constraints_migration.py` (+232 -0) </details> ### 📄 Description ### Motivation - Ensure data integrity and improve query performance for subscription usage and renewal tables by adding explicit constraints and indexes. - Provide a safe migration path for existing SQLite user databases that may contain duplicate rows before adding unique constraints. ### Description - Added SQLAlchemy `Index` and `UniqueConstraint` definitions in `backend/models/subscription_models.py` for `api_usage_logs`, `usage_summaries`, and `subscription_renewal_history`, including a unique constraint on `usage_summaries (user_id, billing_period)`. - Added composite and single-column indexes on `api_usage_logs` for `user_id`, `billing_period`, `timestamp`, `provider`, and common composite lookup patterns. - Added a unique renewal-event constraint and supporting indexes on `subscription_renewal_history` to speed renewal history lookups. - Implemented `backend/scripts/run_subscription_constraints_migration.py`, a safe, idempotent migration script that deduplicates existing rows and creates `IF NOT EXISTS` indexes/unique indexes per user database; it accepts `--user_id` or runs across discovered user DBs and performs transactional commits/rollbacks. ### Testing - Compiled the modified modules with `python -m py_compile backend/models/subscription_models.py backend/scripts/run_subscription_constraints_migration.py`, which completed successfully. - Validated the migration script CLI with `python backend/scripts/run_subscription_constraints_migration.py --help`, which printed usage information successfully. ------ [Codex Task](https://chatgpt.com/codex/tasks/task_e_69a846970b188328abfc1a390e474619) --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>

kerem

2026-03-13 21:03:43 +03:00

closed this issue
added the
pull-request
label

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

starred/ALwrity#683

No description provided.

Rows
Columns