5c9b4333da
- Add .shared/prompts/ with tool-neutral prompts - Add .opencode/config/project.md, rules/, skills/ from opencode-kit - Add .codex/config/project.md from codex-kit - Copy .shared/prompts/ to both .opencode/prompts/ and .codex/prompts/ - Add features/INDEX.md and features/README.md - Update README with unified structure The two original starter kits are now redundant.
81 lines
2.0 KiB
Markdown
81 lines
2.0 KiB
Markdown
# Database Rules
|
|
|
|
SQLite for development, MariaDB/PostgreSQL for production. Write portable SQL.
|
|
|
|
## Development Database
|
|
|
|
- Use SQLite for local development (zero-config, no server)
|
|
- Database file: `database.sqlite` in project root
|
|
- Create with: `touch database.sqlite`
|
|
|
|
## SQL Portability
|
|
|
|
### Use Standard SQL Only
|
|
|
|
- `CURRENT_TIMESTAMP` — works on SQLite, MariaDB, PostgreSQL
|
|
- `INTEGER PRIMARY KEY` — portable (mapped to SERIAL/BIGSERIAL)
|
|
- `TEXT` — portable (avoid VARCHAR length constraints)
|
|
- `DATETIME` — portable across all three databases
|
|
|
|
### Avoid
|
|
|
|
| Pattern | Why | Alternative |
|
|
|---------|-----|-------------|
|
|
| `REPLACE INTO` | Deletes then inserts (data loss) | `UPDATE ...; INSERT IF 0 rows affected` |
|
|
| `NOW()` | SQLite/MariaDB specific | `CURRENT_TIMESTAMP` |
|
|
| `GROUP_CONCAT()` | Not in SQLite | Application-level aggregation |
|
|
| `IFNULL()` | SQLite syntax | `COALESCE()` (standard) |
|
|
| Database-specific functions | Locks you in | Do it in application code |
|
|
|
|
### Boolean Fields
|
|
|
|
SQLite has no BOOLEAN — use INTEGER (0/1):
|
|
|
|
```sql
|
|
CREATE TABLE settings (
|
|
dark_mode INTEGER DEFAULT 0,
|
|
notifications INTEGER DEFAULT 1
|
|
);
|
|
```
|
|
|
|
### String Concatenation
|
|
|
|
Do string concatenation in PHP, not SQL.
|
|
|
|
### Upsert Pattern
|
|
|
|
```php
|
|
// Safe upsert (works on all databases)
|
|
$affected = $pdo->prepare(
|
|
'UPDATE users SET name = ? WHERE id = ?'
|
|
)->execute([$name, $id]);
|
|
|
|
if ($affected === 0) {
|
|
$pdo->prepare(
|
|
'INSERT INTO users (id, name) VALUES (?, ?)'
|
|
)->execute([$id, $name]);
|
|
}
|
|
```
|
|
|
|
## Migrations
|
|
|
|
- Store migrations in `migrations/` directory
|
|
- Name format: `001_description.sql`, `002_description.sql`
|
|
- Each migration must be idempotent (use `IF NOT EXISTS`)
|
|
- Run migrations on every environment
|
|
|
|
## Indexes
|
|
|
|
- Index columns used in `WHERE`, `JOIN`, `ORDER BY`
|
|
- Composite indexes: put most selective column first
|
|
- Don't over-index (write performance cost)
|
|
|
|
## No N+1 Queries
|
|
|
|
- Use JOINs or batch queries
|
|
- Eager load relationships when needed
|
|
|
|
## Transactions
|
|
|
|
Use transactions for any operation that modifies more than one table or row.
|