Soft Delete ​

Mark records as deleted without removing them from the database. Useful for audit trails, undo functionality, or legal retention requirements.

Config ​

typescript
{
    name: 'documents',
    autoSetUid: true,
    fields: [
        ...baseFields,
        { name: 'title', type: 'text', sqlType: 'text', notNull: true },
        { name: 'content', type: 'editor', sqlType: 'text' },
        { name: 'owner_id', type: 'relation', sqlType: 'text', notNull: true,
          foreignKey: { table: 'users', column: 'id' } },
        { name: 'deleted_by', type: 'relation', sqlType: 'text',
          foreignKey: { table: 'users', column: 'id', onDelete: 'SET NULL' } },
        { name: 'deleted_at', type: 'date', sqlType: 'timestamp' },
    ],
    triggers: [createdTrigger, updatedTrigger, {
        // Auto-set deleted_at when deleted_by is set
        name: 'set_deleted_at_on_delete_by',
        seq: 'BEFORE',
        event: 'UPDATE',
        updateOf: 'deleted_by',
        body: sql`UPDATE documents SET deleted_at = CURRENT_TIMESTAMP
                  WHERE id = NEW.id
                  AND OLD.deleted_by IS NULL
                  AND NEW.deleted_by IS NOT NULL`,
    }],
    indexes: [
        { fields: 'owner_id' },
        { fields: 'deleted_by' },
    ],
    extensions: [{
        name: 'rules',
        // Hide soft-deleted records from non-admins
        listRule: '(!deleted_at | auth.role == "admin") & (auth.uid == owner_id | auth.role == "admin")',
        viewRule: '(!deleted_at | auth.role == "admin") & (auth.uid == owner_id | auth.role == "admin")',
        createRule: 'auth.uid != null & owner_id == auth.uid',
        updateRule: 'auth.uid == owner_id',
        deleteRule: null, // No hard deletes — use soft delete instead
    } as TableRulesExtensionData],
}

API calls ​

bash
# Soft-delete a document (set deleted_by to current user)
curl -X POST http://localhost:8787/api/v1/table/documents/edit/DOC_ID \
  -H 'Authorization: Bearer <token>' \
  -H 'Content-Type: application/json' \
  -d '{"setValues": {"deleted_by": "USER_ID"}}'

# The trigger auto-sets deleted_at — no extra API call needed.
# List only shows non-deleted docs (rules filter them out).

How it works ​

  1. The deleteRule: null blocks all hard deletes via the API.
  2. To "delete," you UPDATE the deleted_by field to the current user's ID.
  3. The trigger automatically sets deleted_at to the current timestamp.
  4. The listRule filters out records where deleted_at is set (for non-admins).