dev #1998
Updated by Ayat Rahman about 1 year ago
Here is the Redmine issue for defining the database schema and migrations using Drizzle ORM:
---
### **Subject:** Define Database Schema & Migrations (Drizzle ORM)
### **Estimated Time:** 4 hours
### **Assigned To:** [Developer Name]
### **Priority:** High
### **Status:** New
---
### **Description:**
Create and define the database schema and migrations for the Aiaxio platform using Drizzle ORM. This includes setting up the `ai_tools` table and its dependent tables, such as `tags`, `categories`, `pricing_plans`, and related linking tables.
---
### **Tasks:**
1. **Define `ai_tools` Table:**
- Create the `ai_tools` table with nullable fields, as no field is mandatory at this stage.
- Ensure proper field types, constraints, and default values are applied.
2. **Create Dependent Tables:**
- `Tags`, `Categories`, `Pricing Plans`, `Tool Social Links`, `Tool Verifications`, `AI Tool Tags`.
- Establish relationships and cascading actions (`delete: cascade` where applicable).
3. **Implement Indexes and Foreign Keys:**
- Add necessary indexes to optimize queries.
- Define foreign key constraints to maintain data integrity.
4. **Create Migrations:**
- Generate migration files using Drizzle ORM.
- Ensure all tables and relationships are included.
- Test migration up/down operations to ensure reliability.
5. **Documentation:**
- Document the schema and relationships in the project wiki or README.
- Provide examples of how to query the schema using Drizzle ORM.
---
### **Acceptance Criteria:**
- [ ] The `ai_tools` and related tables are created successfully.
- [ ] All fields are set as nullable where applicable.
- [ ] Migrations run without errors (up/down).
- [ ] Relationships and foreign keys are correctly defined.
- [ ] Documentation is complete and accessible to the team.
---
### **Notes:**
Refer to the existing schema and PRD documents for field details and relationships. Use UUIDs for primary keys and set up default values where applicable. Ensure compliance with the platform's data structure and business logic requirements.
---
If you need any additional details or if there are specific guidelines for the naming conventions and coding standards, please let me know!