Relevant source files

The following files were used as context for generating this wiki page: - [CONTRIBUTING.md](https://github.com/llun/activities.next/blob/main/CONTRIBUTING.md) - [AGENTS.md](https://github.com/llun/activities.next/blob/main/AGENTS.md) - [lib/database/sql/index.test.ts](https://github.com/llun/activities.next/blob/main/lib/database/sql/index.test.ts) - [lib/database/sql/status.test.ts](https://github.com/llun/activities.next/blob/main/lib/database/sql/status.test.ts) - [lib/database/sql/fitnessFile.test.ts](https://github.com/llun/activities.next/blob/main/lib/database/sql/fitnessFile.test.ts) - [lib/actions/createNote.ts](https://github.com/llun/activities.next/blob/main/lib/actions/createNote.ts) - [package.json](https://github.com/llun/activities.next/blob/main/package.json)

# Adding New Database Models Database models in Activities.next provide the structured representation of data for the ActivityPub server, covering actors, statuses, notifications, and specialized content like fitness files. The system uses a modular "Mixin" architecture for SQL databases, allowing the core database object to be composed of specialized logic for each domain entity. This architecture ensures that the application remains compatible with multiple SQL backends, specifically SQLite and PostgreSQL, while providing a unified interface for server actions and services. Adding a new model requires updates across several layers: the domain model definitions, the database interface types, the SQL implementation (Mixins), and Knex-based migrations. This process ensures type safety via TypeScript and consistent data persistence across the distributed environment. ## Model Development Workflow The project follows a specific sequence of steps when introducing new data structures to the persistence layer. This ensures that the implementation is testable, type-safe, and compatible across different SQL engines. ```mermaid flowchart TD Start[Identify Requirement] --> Step1[Define Domain Model in lib/models/] Step1 --> Step2[Add DB Interface in lib/database/types/] Step2 --> Step3[Implement Mixin in lib/database/sql/] Step3 --> Step4[Create Knex Migration] Step4 --> Step5[Run Migration & Add Tests] Step5 --> End[Integrate with Services/Actions] ``` The standard workflow for adding a new model includes: 1. **Define the Model**: Create the domain-level model in `lib/models/`. 2. **Define the Interface**: Add the database-specific method signatures in `lib/database/types/`. 3. **Implement Logic**: Create or update a Mixin in `lib/database/sql/` to handle raw query building using Knex. 4. **Schema Update**: Use `yarn migrate:make ` to create a new SQL migration file. 5. **Validation**: Add co-located tests (e.g., `model.test.ts`) to verify CRUD operations. Sources: [CONTRIBUTING.md:214-220](), [AGENTS.md:14-17]() ## Database Architecture: The Mixin Pattern Activities.next uses a composition-based approach to build its database handler. Instead of one monolithic class, the SQL database is assembled from multiple "Mixins," each responsible for a specific domain. ```mermaid classDiagram class Database { <> +migrate() +destroy() +getStatus() +createActor() +createNote() } class AccountMixin { +isAccountExists() } class ActorMixin { +getActorFromId() } class StatusMixin { +getStatus() } class MediaMixin { +createMedia() } Database <|-- AccountMixin Database <|-- ActorMixin Database <|-- StatusMixin Database <|-- MediaMixin Note: Database is composed of all these mixins via getSQLDatabase ``` ### Composed SQL Database The database instance is generated by passing a Knex configuration to `getSQLDatabase`. This function wires dependencies between different mixins. For example, the `StatusSQLDatabaseMixin` requires access to `Actor`, `Like`, and `Media` logic to fully reconstruct a status object. | Component | Responsibility | Source | | :--- | :--- | :--- | | **Knex Instance** | Core query builder and lifecycle management (migrate/destroy). | [lib/database/sql/index.test.ts:153-159]() | | **Mixins** | Domain-specific logic (e.g., `ActorSQLDatabaseMixin`, `TimelineSQLDatabaseMixin`). | [lib/database/sql/index.test.ts:14-41]() | | **Database Interface** | Unified TypeScript interface exposing all mixin methods. | [lib/database/sql/index.test.ts:133-146]() | Sources: [lib/database/sql/index.test.ts:88-150](), [AGENTS.md:79-81]() ## Schema Management and Migrations Schema changes are managed through Knex migrations, located in the `migrations/` directory. These migrations must be compatible with both SQLite and PostgreSQL. ### Creating a Migration To create a new table or modify an existing one, use the following command: ```bash yarn migrate:make descriptive_migration_name ``` Sources: [CONTRIBUTING.md:231-233](), [package.json:11]() ### Compatibility Guidelines All database operations must use the Knex query builder rather than raw SQL to maintain cross-database compatibility. Developers should avoid vendor-specific features (e.g., PostgreSQL-specific arrays) and prefer standard SQL types like `text`. Sources: [AGENTS.md:78-85]() ## Implementation Example: Note and Fitness Models New models often involve complex relationships. For example, a `Status` might have multiple `Attachments` and optional `FitnessFile` metadata. ```mermaid erDiagram ACTOR ||--o{ STATUS : posts STATUS ||--o{ ATTACHMENT : contains STATUS ||--o| FITNESS_FILE : links ACTOR ||--o{ FITNESS_FILE : owns STATUS ||--o{ TAG : has ``` ### Model Operations Implementation in the Mixin layer involves defining methods for CRUD operations. As seen in `lib/database/sql/status.test.ts`, a model implementation must support: * **Retrieval**: Fetching with related entities (e.g., `getStatus` with attachments). * **Counters**: Managing counts for replies and reblogs. * **Visibility**: Handling recipients (`to`, `cc`) for ActivityPub compliance. | Method | Purpose | Key Parameters | | :--- | :--- | :--- | | `createNote` | Persists a new Note status. | `id`, `actorId`, `text`, `to`, `cc` | | `createAttachment` | Links a media file to a status. | `statusId`, `url`, `mediaType` | | `createFitnessFile` | Tracks physical activity data. | `actorId`, `path`, `bytes`, `fileType` | | `updateNote` | Handles status edits and history. | `statusId`, `text`, `summary` | Sources: [lib/database/sql/status.test.ts:470-492](), [lib/database/sql/fitnessFile.test.ts:32-50](), [lib/actions/createNote.ts:182-205]() ## Testing Database Models Testing is a critical part of adding a model. Tests are co-located with the implementation (e.g., `lib/database/sql/status.test.ts`). ### Test Environment * **Isolation**: Each test suite runs against an isolated, in-memory SQLite database for speed and parallelism. * **Lifecycle**: Tests use `beforeAll` to run migrations and `afterAll` to destroy the database instance. * **Seed Data**: Use `seedDatabase` to populate the environment with standard actors and statuses for consistent testing. ```typescript describe('MyNewModelDatabase', () => { const table = getTestDatabaseTable() beforeAll(async () => { await databaseBeforeAll(table) }) // Test implementation... }) ``` Sources: [CONTRIBUTING.md:144-148](), [lib/database/sql/status.test.ts:25-37](), [AGENTS.md:69-71]() ## Summary Adding a database model in Activities.next involves defining domain-level types and implementing them through SQL Mixins using Knex. By adhering to the Mixin pattern, developers ensure the code is modular and testable. All schema changes must be implemented via migrations that maintain compatibility between SQLite (used for local development and testing) and PostgreSQL (used for production). Accuracy is maintained by co-locating unit tests that verify both the logic within the Mixins and the resulting data structure in the database.