Database Schema & Persistence
Relevant source files
The following files were used as context for generating this wiki page:
ALwrity utilizes a persistent data layer designed to manage complex digital marketing entities, including AI-generated content strategies, audience personas, and platform-specific configurations. The system defaults to SQLite for development and local portability, while remaining PostgreSQL-ready for production scalability through the use of the SQLAlchemy Object-Relational Mapper (ORM).
Sources: README.md, SETUP_GUIDE.md
1. Database Architecture & Core Models
The persistence layer is structured to support a multi-tenant, JWT-based authentication system, ensuring data isolation for solopreneurs and content creators. The architecture separates core marketing logic from persistence operations through a dedicated database_service.
Sources: README.md, backend/services/CONTENT_PLANNING_MODULARITY_PLAN.md:275-298
1.1 Core Persistence Service Logic
The system implements a centralized DatabaseService to handle CRUD operations with built-in error handling and session management.
class DatabaseService:
"""Centralized database operations."""
def __init__(self, session: Session):
self.session = session
async def create_record(self, model_class, data: Dict[str, Any]):
"""Create database record with error handling."""
try:
record = model_class(**data)
self.session.add(record)
self.session.commit()
return record
except Exception as e:
self.session.rollback()
# Error handling logic
raise
Sources: backend/services/CONTENT_PLANNING_MODULARITY_PLAN.md:277-298
1.2 Entity Relationship Overview
The database schema revolves around four primary domains: Content Planning, Persona Management, Onboarding, and AI Analytics.
The diagram shows the high-level relationships between users and their strategic/persona assets.
Sources: backend/services/persona/TBD_persona_enhancements.md:70-100, backend/api/content_planning/services/content_strategy/README.md:95-105
2. Content Planning & Strategy Schema
The enhanced_strategy_models manage over 30 strategic input fields. Persistence in this module is critical for maintaining "Strategy Awareness" during AI prompt chaining.
2.1 Content Strategy Data Model
| Field Category | Key Attributes | Data Type |
|---|---|---|
| Business Context | Objectives, Budget, Timeline, Market Share | JSON/Integer |
| Audience Intelligence | Pain Points, Consumption Patterns, Buying Journey | JSON |
| Competitive Intelligence | Competitor Strategies, Market Gaps, Emerging Trends | JSON |
| Performance Metrics | Conversion Rates, ROI Targets, A/B Testing Data | Float/JSON |
Sources: backend/api/content_planning/services/content_strategy/README.md:25-50
2.2 Data Flow for Persistence
When a user generates an enhanced strategy, the data flows through a modular pipeline before being committed to the database.
The flow demonstrates how raw onboarding data is transformed and validated before reaching persistent storage.
Sources: backend/api/content_planning/services/content_strategy/README.md:10-20, backend/api/content_planning/services/content_strategy/IMPLEMENTATION_STATUS.md:14-30
3. Persona System Persistence
The persona system uses an enhanced schema to track "linguistic fingerprints" and writing style signatures. This allows the AI to mimic a user's specific brand voice consistently over time.
3.1 Enhanced Writing Persona Table
This model stores the core identity of a brand's voice.
- Archetype: The primary persona role (e.g., Expert, Storyteller).
- Linguistic Fingerprint: A complex JSON object containing sentence patterns, vocabulary sophistication, and rhetorical device detection.
- Style Signature: Specialized metrics for emotional tone and consistency.
Sources: backend/services/persona/TBD_persona_enhancements.md:15-30
3.2 Platform-Specific Specialization
Personas are further persisted in a platform_personas table, which stores constraints for LinkedIn, Facebook, and other channels.
- Engagement Patterns: Historical data on user interactions.
- Stylistic Constraints: Rules for emoji usage, sentence length, and formatting.
Sources: backend/services/persona/TBD_persona_enhancements.md:85-100
4. Caching & Performance Optimization
To reduce API latency and database load, ALwrity implements a multi-tier caching strategy.
| Cache Type | Duration (TTL) | Purpose |
|---|---|---|
| AI Analysis Cache | 1 Hour | Stores expensive AI-generated strategic insights |
| Onboarding Cache | 30 Minutes | Temporary storage for user setup data |
| Strategy Cache | 2 Hours | Frequent access for calendar generation steps |
Sources: backend/api/content_planning/services/content_strategy/README.md:65-72, backend/api/content_planning/services/content_strategy/IMPLEMENTATION_STATUS.md:75-85
5. Migration & Environment Setup
ALwrity provides scripts to initialize and migrate the database environment. For SQLite, the database files are automatically generated in the backend directory during the first run.
5.1 Environment Initialization
The start_alwrity_backend.py script ensures that the necessary directory structure for persistence (workspace, config, and research folders) exists before starting the database session.
# From start_alwrity_backend.py
# ✅ Created directory: lib/workspace/alwrity_content
# ✅ Created directory: lib/workspace/alwrity_config
Sources: backend/README.md:100-115
5.2 Schema Migration
Advanced persona features require a specific migration path from basic writing_personas to enhanced_writing_personas to preserve historical style data while adding quality tracking metrics.
Sources: backend/services/persona/TBD_persona_enhancements.md:75-110
Conclusion
Database Schema & Persistence in ALwrity is centered on the EnhancedStrategy and WritingPersona models. By leveraging SQLAlchemy and a modular service architecture, the system provides high-fidelity storage for complex marketing data while maintaining the performance required for real-time AI content generation.