[PR #326] [MERGED] Remove SQLite storage from integration services; migrate token/cache to PostgreSQL and harden monitoring #630

New issue

Closed

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

kerem commented

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

Owner

📋 Pull Request Information

Original PR: https://github.com/AJaySi/ALwrity/pull/326
Author: @AJaySi
Created: 2/9/2026
Status: ✅ Merged
Merged: 2/9/2026
Merged by: @AJaySi

Base: main ← Head: codex/remove-sqlite3-usage-from-providers-and-services

📝 Commits (1)

c701fd4 Remove sqlite storage from integrations

📊 Changes

19 files changed (+1059 additions, -696 deletions)

View changed files

📝 backend/api/subscription/routes/dashboard.py (+2 -2)
📝 backend/api/subscription/routes/logs.py (+2 -2)
📝 backend/api/subscription/routes/plans.py (+2 -2)
📝 backend/api/subscription/routes/subscriptions.py (+2 -2)
📝 backend/api/subscription/utils.py (+0 -1)
📝 backend/api/wix_routes.py (+1 -2)
📝 backend/routers/bing_oauth.py (+22 -29)
📝 backend/routers/gsc_auth.py (+22 -31)
📝 backend/services/gsc_service.py (+195 -114)
📝 backend/services/integrations/bing_oauth.py (+163 -124)
📝 backend/services/integrations/wix_oauth.py (+122 -75)
📝 backend/services/integrations/wordpress_oauth.py (+113 -83)
📝 backend/services/integrations/wordpress_publisher.py (+103 -67)
📝 backend/services/integrations/wordpress_service.py (+93 -69)
📝 backend/services/oauth_token_monitoring_service.py (+8 -14)
📝 backend/services/scheduler/executors/gsc_insights_executor.py (+7 -41)
📝 backend/services/scheduler/executors/oauth_token_monitoring_executor.py (+180 -35)
📝 backend/services/subscription/limit_validation.py (+0 -3)
📝 docs/Onboarding/DEVELOPER_GUIDE.md (+22 -0)

📄 Description

Motivation

Remove embedded SQLite usage and the hardcoded alwrity.db path from integration providers and related services and migrate token/cache storage to the PostgreSQL user-data DB session to enforce SSOT and production-grade behavior.
Remove dual-write/SQLite fallback paths that were previously used during migrations once PostgreSQL parity is established.
Improve reliability of OAuth token monitoring with tighter error handling, clearer logging and alert creation, and add Wix refresh handling.
Document the new PostgreSQL-first integration framework and the onboarding requirement for integrations in developer docs.

Description

Replaced direct sqlite3 usage and db_path="alwrity.db" constructors in integration modules (gsc_service, bing_oauth, wordpress_oauth, wix_oauth, wordpress_service, wordpress_publisher, wordpress_publisher) with a shared PostgreSQL-backed session helper services.database.get_user_data_db_session() and a local @contextmanager _db_session helper in each service.
Converted table DDL and data-access patterns to use sqlalchemy.text() SQL executed against the PostgreSQL session (e.g. BIGSERIAL, CURRENT_TIMESTAMP, INTERVAL) and replaced sqlite-specific SQL idioms with Postgres equivalents.
Updated GSC OAuth flow to return a structured dict including user_id on success, and adjusted OAuth callback routers (gsc_auth, bing_oauth) to use the returned user_id to create platform insights tasks (no filesystem DB lookups).
Hardened the OAuthTokenMonitoringExecutor: removed hardcoded sqlite paths, created more robust logging, added Wix token checking/refresh via WixOAuthService and WixService, tightened expiry parsing (use UTC), and improved failure/alert lifecycle and messages.
Migrated integration services to commit/rollback using the session context manager and removed remaining sqlite references across subscription/dashboard endpoints and utilities (replaced sqlite3 exception handling with SQLAlchemyError where appropriate).
Small API/shape changes: GSCService.handle_oauth_callback now returns Dict[str, Any] with success metadata; BingOAuthService.handle_oauth_callback includes user_id in its response.
Added get_latest_cached_analytics helper on GSCService and updated GSCInsightsExecutor to read cached analytics via that helper.
Documentation: updated docs/Onboarding/DEVELOPER_GUIDE.md with an "Integration Framework (PostgreSQL-first)" section and an "Onboarding Requirement for Integrations" note.

Testing

No automated tests were executed as part of this change; changes were limited to refactors and wiring (no CI runs in this rollout).
Manual/next steps recommended: run migration/DDL scripts against a staging PostgreSQL instance, run integration tests for OAuth flows (GSC/Bing/WordPress/Wix), and run scheduler/executor integration tests to validate monitoring, alert creation, and token refresh paths.

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/326 **Author:** [@AJaySi](https://github.com/AJaySi) **Created:** 2/9/2026 **Status:** ✅ Merged **Merged:** 2/9/2026 **Merged by:** [@AJaySi](https://github.com/AJaySi) **Base:** `main` ← **Head:** `codex/remove-sqlite3-usage-from-providers-and-services` --- ### 📝 Commits (1) - [`c701fd4`](https://github.com/AJaySi/ALwrity/commit/c701fd4a94fd374110d4bb6107f12d0a6cffe8bb) Remove sqlite storage from integrations ### 📊 Changes **19 files changed** (+1059 additions, -696 deletions) <details> <summary>View changed files</summary> 📝 `backend/api/subscription/routes/dashboard.py` (+2 -2) 📝 `backend/api/subscription/routes/logs.py` (+2 -2) 📝 `backend/api/subscription/routes/plans.py` (+2 -2) 📝 `backend/api/subscription/routes/subscriptions.py` (+2 -2) 📝 `backend/api/subscription/utils.py` (+0 -1) 📝 `backend/api/wix_routes.py` (+1 -2) 📝 `backend/routers/bing_oauth.py` (+22 -29) 📝 `backend/routers/gsc_auth.py` (+22 -31) 📝 `backend/services/gsc_service.py` (+195 -114) 📝 `backend/services/integrations/bing_oauth.py` (+163 -124) 📝 `backend/services/integrations/wix_oauth.py` (+122 -75) 📝 `backend/services/integrations/wordpress_oauth.py` (+113 -83) 📝 `backend/services/integrations/wordpress_publisher.py` (+103 -67) 📝 `backend/services/integrations/wordpress_service.py` (+93 -69) 📝 `backend/services/oauth_token_monitoring_service.py` (+8 -14) 📝 `backend/services/scheduler/executors/gsc_insights_executor.py` (+7 -41) 📝 `backend/services/scheduler/executors/oauth_token_monitoring_executor.py` (+180 -35) 📝 `backend/services/subscription/limit_validation.py` (+0 -3) 📝 `docs/Onboarding/DEVELOPER_GUIDE.md` (+22 -0) </details> ### 📄 Description ### Motivation - Remove embedded SQLite usage and the hardcoded `alwrity.db` path from integration providers and related services and migrate token/cache storage to the PostgreSQL user-data DB session to enforce SSOT and production-grade behavior. - Remove dual-write/SQLite fallback paths that were previously used during migrations once PostgreSQL parity is established. - Improve reliability of OAuth token monitoring with tighter error handling, clearer logging and alert creation, and add Wix refresh handling. - Document the new PostgreSQL-first integration framework and the onboarding requirement for integrations in developer docs. ### Description - Replaced direct `sqlite3` usage and `db_path="alwrity.db"` constructors in integration modules (`gsc_service`, `bing_oauth`, `wordpress_oauth`, `wix_oauth`, `wordpress_service`, `wordpress_publisher`, `wordpress_publisher`) with a shared PostgreSQL-backed session helper `services.database.get_user_data_db_session()` and a local `@contextmanager _db_session` helper in each service. - Converted table DDL and data-access patterns to use `sqlalchemy.text()` SQL executed against the PostgreSQL session (e.g. `BIGSERIAL`, `CURRENT_TIMESTAMP`, `INTERVAL`) and replaced `sqlite`-specific SQL idioms with Postgres equivalents. - Updated GSC OAuth flow to return a structured dict including `user_id` on success, and adjusted OAuth callback routers (`gsc_auth`, `bing_oauth`) to use the returned `user_id` to create platform insights tasks (no filesystem DB lookups). - Hardened the `OAuthTokenMonitoringExecutor`: removed hardcoded sqlite paths, created more robust logging, added Wix token checking/refresh via `WixOAuthService` and `WixService`, tightened expiry parsing (use UTC), and improved failure/alert lifecycle and messages. - Migrated integration services to commit/rollback using the session context manager and removed remaining sqlite references across subscription/dashboard endpoints and utilities (replaced `sqlite3` exception handling with `SQLAlchemyError` where appropriate). - Small API/shape changes: `GSCService.handle_oauth_callback` now returns `Dict[str, Any]` with success metadata; `BingOAuthService.handle_oauth_callback` includes `user_id` in its response. - Added `get_latest_cached_analytics` helper on `GSCService` and updated `GSCInsightsExecutor` to read cached analytics via that helper. - Documentation: updated `docs/Onboarding/DEVELOPER_GUIDE.md` with an "Integration Framework (PostgreSQL-first)" section and an "Onboarding Requirement for Integrations" note. ### Testing - No automated tests were executed as part of this change; changes were limited to refactors and wiring (no CI runs in this rollout). - Manual/next steps recommended: run migration/DDL scripts against a staging PostgreSQL instance, run integration tests for OAuth flows (GSC/Bing/WordPress/Wix), and run scheduler/executor integration tests to validate monitoring, alert creation, and token refresh paths. ------ [Codex Task](https://chatgpt.com/codex/tasks/task_e_69898b7f774483288ba4ff7ff9010bbe) --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>

kerem

2026-03-13 21:00:45 +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#630

No description provided.

Rows
Columns