AI-assisted migration of DEV.to analytics from CLI to production web platform using GitHub Copilot CLI
A comprehensive analytics platform for tracking and analyzing DEV.to content performance, migrating from a SQLite-based CLI tool to a production-ready web application with PostgreSQL 18 and modern web technologies.
Challenge Entry: GitHub Copilot CLI Challenge
- Backend: FastAPI (Python 3.10+)
- Database: PostgreSQL 18
- ORM: SQLAlchemy Core (NOT ORM models)
- Advanced Features: pgvector for embeddings, JSONB, partitioning
- Analytics: Apache Superset
- API Integration: DEV.to Forem API
Phase 2 Complete: PostgreSQL 18 Schema Migration ✅
- ✅ Complete technical documentation (2,218 lines)
- ✅ 18 SQLAlchemy Core table definitions
- ✅ Business logic preservation (quality scores, attribution, sentiment)
- ✅ PostgreSQL 18 features (JSONB, ARRAY, Vector, partitioning)
- ✅ Migration guide and validation scripts
Next Phase: FastAPI REST API development
- Python 3.10+
- PostgreSQL 18
- pip
# 1. Clone repository
git clone https://github.com/YOUR_USERNAME/devto_githubcopilotcli_challenge.git
cd devto_githubcopilotcli_challenge
# 2. Install dependencies
cd app
pip install -r requirements.txt
# 3. Configure database
cp .env.example .env
# Edit .env with your PostgreSQL credentials
# 4. Initialize database
python3 init_database.py
# 5. Verify installation
python3 validate_schema.pydevto_githubcopilotcli_challenge/
├── app/ # PostgreSQL schema & backend
│ ├── db/
│ │ ├── tables.py # 18 SQLAlchemy Core tables
│ │ ├── connection.py # Connection pooling
│ │ ├── queries.py # Business logic
│ │ └── README.md # Usage guide
│ ├── init_database.py # Database setup
│ ├── validate_schema.py # Schema validation
│ └── requirements.txt # Python dependencies
│
├── TECHNICAL_DOCUMENTATION.md # Complete system analysis
├── README_SCHEMA.md # Schema migration details
├── README.md # This file
│
└── [Original CLI tools] # SQLite-based scripts
├── devto_tracker.py
├── content_collector.py
├── nlp_analyzer.py
└── ...
- Article metrics tracking
- Follower attribution analysis
- Sentiment analysis on comments
- Quality scoring system
- Content collection and NLP analysis
- RESTful API with FastAPI
- Real-time dashboards
- Apache Superset integration
- Semantic search with pgvector
- Advanced analytics and reporting
- TECHNICAL_DOCUMENTATION.md - Complete system analysis (2,218 lines)
- README_SCHEMA.md - PostgreSQL migration overview
- app/INSTALL.md - Installation guide
- app/MIGRATION_SUMMARY.md - Migration details
- app/db/README.md - Database usage examples
Core Tables: snapshots, article_metrics, follower_events, comments, followers
Analytics: daily_analytics (partitioned), referrers
Content: article_content, code_blocks, links
Analysis: comment_insights, article_history, milestones
Intelligence: author_themes, theme_mapping, stats_cache
- Follower Attribution: 7-day window with 6-hour tolerance
- Quality Score: (completion × 0.7) + (min(engagement, 20) × 1.5)
- Sentiment: Positive ≥0.3, Negative ≤-0.2
- Proximity Search: 6-hour tolerance for timestamp matching
This project was developed with assistance from GitHub Copilot CLI, demonstrating:
- Comprehensive codebase analysis (7 Python files, 13 tables)
- SQLite → PostgreSQL migration patterns
- SQLAlchemy Core table definitions
- Business logic preservation
- Complete documentation generation
MIT License - see LICENSE file for details
- Built for the GitHub Copilot CLI Challenge
- Powered by DEV.to Forem API
- Generated with GitHub Copilot CLI