IdeaScore Data Architecture

This document outlines the data architecture for the IdeaScore application, with a particular focus on preventing data conflicts with other applications in the charBuildSuperapp ecosystem.

All tables in the IdeaScore application will follow these naming conventions:

1. Prefix all tables with ideascore_ to prevent naming conflicts with other applications
2. Include a version suffix (e.g., _v1) to support schema evolution
3. Examples: ideascore_ideas_v1, ideascore_tags_v1, etc.

-- Example of proper table naming
CREATE TABLE IF NOT EXISTS ideascore_ideas_v1 (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  -- other fields
);

All queries must include user email filtering to ensure users only see their own data:

1. Every table must include a user_email column
2. Every query must filter by the user's email
3. Composite indexes should include user_email for performance

-- Example of proper user isolation in a query
SELECT * FROM ideascore_ideas_v1 
WHERE user_email = ? 
ORDER BY created_at DESC;

To ensure consistent data access patterns, we will create database utility functions that:

1. Always include appropriate table prefixes
2. Always include user isolation
3. Implement proper error handling
4. Use parameterized queries to prevent SQL injection

// Example of a proper database function
async function getUserIdeas(email, sortBy = 'created_at', order = 'DESC') {
  const validSortFields = ['created_at', 'title', 'effort_score', 'payoff_score', 'ratio'];
  const sortField = validSortFields.includes(sortBy) ? sortBy : 'created_at';
  
  try {
    return await sqlite.execute(
      `SELECT * FROM ideascore_ideas_v1 
       WHERE user_email = ? AND is_deleted = 0
       ORDER BY ${sortField} ${order === 'ASC' ? 'ASC' : 'DESC'}`,
      [email]
    );
  } catch (error) {
    console.error('Database error in getUserIdeas:', error);
    throw new Error('Failed to retrieve ideas');
  }
}

Database tables should be initialized in a modular way that doesn't interfere with other applications:

async function initializeIdeaScoreDatabase() {
  console.log("Initializing IdeaScore database...");
  const SCHEMA_VERSION = 1;

  try {
    // Create ideas table
    await sqlite.execute(`
      CREATE TABLE IF NOT EXISTS ideascore_ideas_v1 (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        effort_score INTEGER NOT NULL CHECK(effort_score BETWEEN 1 AND 10),
        payoff_score INTEGER NOT NULL CHECK(payoff_score BETWEEN 1 AND 10),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        user_email TEXT NOT NULL,
        is_deleted BOOLEAN DEFAULT 0
      )
    `);
    
    // Create index for user isolation
    await sqlite.execute(`
      CREATE INDEX IF NOT EXISTS idx_ideascore_ideas_user_email 
      ON ideascore_ideas_v1(user_email)
    `);
    
    console.log("IdeaScore database initialized successfully");
    return SCHEMA_VERSION;
  } catch (error) {
    console.error("IdeaScore database initialization error:", error);
    throw error;
  }
}

The context object passed from the main handler provides access to shared resources. We should use it consistently:

// Example handler showing proper context usage
export default async function(req, context) {
  const { email, sqlite, basePath, relativePath } = context;
  
  // Initialize our specific database tables
  const SCHEMA_VERSION = await initializeIdeaScoreDatabase();
  
  // Continue with request handling...
}

1. Never modify shared tables: Only modify tables that belong to the IdeaScore application
2. Use transactions for operations that modify multiple records
3. Validate all input data before database operations
4. Implement soft deletes using is_deleted flags rather than actual DELETE operations
5. Log database errors with enough context to debug but without exposing sensitive data
6. Implement periodic data backup mechanisms
7. Version your queries along with your schema

Before deploying any changes, test that:

1. Data from one application doesn't appear in another
2. One user's data doesn't appear for another user
3. Schema changes don't affect other applications
4. Error handling properly captures and reports database issues

As the application grows, consider:

1. Implementing a more robust ORM-like layer
2. Adding database migration tools
3. Considering data partitioning strategies
4. Implementing more advanced access control
5. Adding audit logging for sensitive operations

By following these guidelines, we can ensure that the IdeaScore application maintains proper data isolation while still benefiting from the shared infrastructure of the charBuildSuperapp ecosystem.