npm package: @kysera/timestamps
Description: Automatic timestamp management plugin for Kysely repositories
Author: Kysera Team
Latest version: 0.8.8

# @kysera/timestamps

> Automatic timestamp management plugin for Kysera - Zero-configuration `created_at` and `updated_at` tracking through @kysera/executor's Unified Execution Layer with powerful query helpers.

[![Version](https://img.shields.io/npm/v/@kysera/timestamps.svg)](https://www.npmjs.com/package/@kysera/timestamps)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)
[![TypeScript](https://img.shields.io/badge/TypeScript-5.9-blue)](https://www.typescriptlang.org/)

## 🎯 Features

- ✅ **Zero Configuration** - Works out of the box with sensible defaults
- ✅ **Automatic Timestamps** - `created_at` on insert, `updated_at` on update via @kysera/executor
- ✅ **Batch Operations** - Efficient `createMany`, `updateMany`, `touchMany` methods
- ✅ **Custom Column Names** - Use any column names you want
- ✅ **Table Filtering** - Whitelist or blacklist specific tables
- ✅ **Date Formats** - ISO strings, Unix timestamps, or Date objects
- ✅ **Query Helpers** - 13+ methods for timestamp-based queries
- ✅ **Type-Safe** - Full TypeScript support with inference
- ✅ **Plugin Architecture** - Integrates seamlessly via @kysera/executor's Unified Execution Layer
- ✅ **Production Ready** - Battle-tested with comprehensive test coverage

## 📥 Installation

```bash
# npm
npm install @kysera/timestamps @kysera/repository kysely

# pnpm
pnpm add @kysera/timestamps @kysera/repository kysely

# bun
bun add @kysera/timestamps @kysera/repository kysely

# deno
import { timestampsPlugin } from "npm:@kysera/timestamps"
```

## 🚀 Quick Start

### 1. Add Timestamp Columns to Your Database

```sql
-- PostgreSQL / MySQL / SQLite
ALTER TABLE users ADD COLUMN created_at TIMESTAMP;
ALTER TABLE users ADD COLUMN updated_at TIMESTAMP;

-- Or include in table creation
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);
```

### 2. Setup Plugin

```typescript
import { Kysely, PostgresDialect } from 'kysely'
import { Pool } from 'pg'
import { createExecutor } from '@kysera/executor'
import { createORM, createRepositoryFactory } from '@kysera/repository'
import { timestampsPlugin } from '@kysera/timestamps'
import { z } from 'zod'

// Define database schema
interface Database {
  users: {
    id: Generated<number>
    email: string
    name: string
    created_at: Generated<Date>
    updated_at: Date | null
  }
}

// Create database connection
const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({
      /* config */
    })
  })
})

// Step 1: Register timestamps plugin with Unified Execution Layer
const executor = await createExecutor(db, [
  timestampsPlugin() // ✨ That's it!
])

// Step 2: Create ORM with plugin-enabled executor
const orm = await createORM(executor, [])

// Step 3: Create repository
const userRepo = orm.createRepository(executor => {
  const factory = createRepositoryFactory(executor)
  return factory.create<'users', User>({
    tableName: 'users',
    mapRow: row => row as User,
    schemas: {
      create: z.object({
        email: z.string().email(),
        name: z.string()
      })
    }
  })
})

// Use repository - timestamps added automatically!
const user = await userRepo.create({
  email: 'alice@example.com',
  name: 'Alice'
})

console.log(user.created_at) // ✅ 2024-01-15T10:30:00.000Z
console.log(user.updated_at) // null (only set on update)

// Update - updated_at set automatically!
const updated = await userRepo.update(user.id, {
  name: 'Alice Smith'
})

console.log(updated.updated_at) // ✅ 2024-01-15T10:35:00.000Z
```

---

## 📚 Table of Contents

1. [Configuration](#-configuration)
   - [Default Configuration](#default-configuration)
   - [Custom Column Names](#custom-column-names)
   - [Table Filtering](#table-filtering)
   - [Date Formats](#date-formats)
   - [Custom Timestamp Generator](#custom-timestamp-generator)
2. [Automatic Behavior](#-automatic-behavior)
   - [On Create](#on-create)
   - [On Update](#on-update)
   - [Bypass Timestamps](#bypass-timestamps)
3. [Query Helpers](#-query-helpers)
   - [Find by Creation Date](#find-by-creation-date)
   - [Find by Update Date](#find-by-update-date)
   - [Recently Created/Updated](#recently-createdupdated)
   - [Touch Records](#touch-records)
4. [Batch Operations](#-batch-operations)
   - [createMany](#createmany)
   - [updateMany](#updatemany)
   - [touchMany](#touchmany)
5. [Extended Methods](#-extended-methods)
6. [Advanced Usage](#-advanced-usage)
7. [Multi-Database Support](#-multi-database-support)
8. [Type Safety](#-type-safety)
9. [API Reference](#-api-reference)
10. [Best Practices](#-best-practices)
11. [Performance](#-performance)
12. [Troubleshooting](#-troubleshooting)

---

## ⚙️ Configuration

### Default Configuration

The plugin works with zero configuration using sensible defaults:

```typescript
const plugin = timestampsPlugin()

// Equivalent to:
const plugin = timestampsPlugin({
  createdAtColumn: 'created_at',
  updatedAtColumn: 'updated_at',
  setUpdatedAtOnInsert: false,
  dateFormat: 'iso',
  tables: undefined, // All tables
  excludeTables: undefined, // No exclusions
  primaryKeyColumn: 'id' // Default primary key
})
```

### Custom Column Names

Use your own column naming conventions:

```typescript
// Example: Use "created" and "modified"
const plugin = timestampsPlugin({
  createdAtColumn: 'created',
  updatedAtColumn: 'modified'
})

// Example: Use "createdDate" and "lastModified"
const plugin = timestampsPlugin({
  createdAtColumn: 'createdDate',
  updatedAtColumn: 'lastModified'
})

// Your database schema
interface Database {
  posts: {
    id: Generated<number>
    title: string
    created: Generated<Date> // ✅ Custom name
    modified: Date | null // ✅ Custom name
  }
}
```

### Table Filtering

Control which tables get timestamps:

#### Whitelist (Only Specific Tables)

```typescript
const plugin = timestampsPlugin({
  tables: ['users', 'posts', 'comments']
})

// ✅ users, posts, comments get timestamps
// ❌ config, settings, logs do NOT get timestamps
```

#### Blacklist (Exclude Specific Tables)

```typescript
const plugin = timestampsPlugin({
  excludeTables: ['config', 'migrations', 'sessions']
})

// ✅ All tables get timestamps EXCEPT
// ❌ config, migrations, sessions
```

#### When to Use Each

**Use Whitelist (`tables`) when:**

- You have many tables but only a few need timestamps
- You want explicit control over timestamp tables
- You're migrating incrementally

**Use Blacklist (`excludeTables`) when:**

- Most tables need timestamps
- Only a few system tables should be excluded
- You want timestamps by default

### Date Formats

Choose the timestamp format that matches your database:

#### ISO String (Default)

```typescript
const plugin = timestampsPlugin({
  dateFormat: 'iso'
})

// Generates: "2024-01-15T10:30:00.000Z"
// Best for: PostgreSQL TIMESTAMP, MySQL DATETIME, SQLite TEXT
```

#### Unix Timestamp

```typescript
const plugin = timestampsPlugin({
  dateFormat: 'unix'
})

// Generates: 1705318200 (seconds since epoch)
// Best for: INTEGER columns, time-series data
```

#### Date Object

```typescript
const plugin = timestampsPlugin({
  dateFormat: 'date'
})

// Generates: new Date()
// Best for: Native DATE columns, database compatibility
```

### Custom Timestamp Generator

Full control over timestamp generation:

```typescript
// Example: Use a custom time source
const plugin = timestampsPlugin({
  getTimestamp: () => {
    return customTimeService.getCurrentTime()
  }
})

// Example: Always use UTC midnight for created dates
const plugin = timestampsPlugin({
  getTimestamp: () => {
    const now = new Date()
    now.setUTCHours(0, 0, 0, 0)
    return now.toISOString()
  }
})

// Example: Use millisecond precision Unix timestamp
const plugin = timestampsPlugin({
  getTimestamp: () => Date.now()
})
```

### Set updated_at on Insert

Some applications want `updated_at` to equal `created_at` initially:

```typescript
const plugin = timestampsPlugin({
  setUpdatedAtOnInsert: true
})

const user = await userRepo.create({
  email: 'alice@example.com',
  name: 'Alice'
})

console.log(user.created_at) // 2024-01-15T10:30:00.000Z
console.log(user.updated_at) // 2024-01-15T10:30:00.000Z (same!)

// On update, updated_at changes
const updated = await userRepo.update(user.id, { name: 'Alice Smith' })
console.log(updated.updated_at) // 2024-01-15T10:35:00.000Z (different)
```

### Custom Primary Key Column

If your tables use a different primary key column name (e.g., `uuid`, `user_id`, `pk`), you can configure it:

```typescript
// Example: Using 'uuid' as primary key
const plugin = timestampsPlugin({
  primaryKeyColumn: 'uuid'
})

// Now touch() will use the configured primary key
await userRepo.touch(userUuid)

// Example: Using 'user_id' as primary key
const plugin = timestampsPlugin({
  primaryKeyColumn: 'user_id'
})

// Database schema
interface Database {
  users: {
    user_id: Generated<number> // Custom primary key
    email: string
    name: string
    created_at: Generated<Date>
    updated_at: Date | null
  }
}

// Touch uses user_id instead of id
await userRepo.touch(userId)
```

**Note:** This option affects the following methods:
- `touch()` - Uses `primaryKeyColumn` in WHERE clause
- `createMany()` - Uses `primaryKeyColumn` for MySQL/MSSQL fallback ORDER BY
- `updateMany()` - Uses `primaryKeyColumn` in WHERE ... IN clause
- `touchMany()` - Uses `primaryKeyColumn` in WHERE ... IN clause

The base `update()` method uses the repository's own primary key logic.

---

## 🤖 Automatic Behavior

The timestamps plugin works through @kysera/executor's Unified Execution Layer to automatically manage timestamps on all operations.

### On Create

When you call `repository.create()`, the plugin automatically adds `created_at`:

```typescript
const user = await userRepo.create({
  email: 'bob@example.com',
  name: 'Bob'
})

// Equivalent SQL:
// INSERT INTO users (email, name, created_at)
// VALUES ('bob@example.com', 'Bob', '2024-01-15T10:30:00.000Z')

console.log(user.created_at) // ✅ 2024-01-15T10:30:00.000Z
console.log(user.updated_at) // null (default behavior)
```

**Manual Override:**

```typescript
// Provide your own created_at (e.g., migrating data)
const user = await userRepo.create({
  email: 'charlie@example.com',
  name: 'Charlie',
  created_at: '2020-01-01T00:00:00.000Z' // ✅ Uses this instead
})

console.log(user.created_at) // 2020-01-01T00:00:00.000Z
```

### On Update

When you call `repository.update()`, the plugin automatically sets `updated_at`:

```typescript
const updated = await userRepo.update(userId, {
  name: 'New Name'
})

// Equivalent SQL:
// UPDATE users
// SET name = 'New Name', updated_at = '2024-01-15T10:35:00.000Z'
// WHERE id = 1

console.log(updated.updated_at) // ✅ 2024-01-15T10:35:00.000Z
```

**Manual Override:**

```typescript
// Provide your own updated_at
const updated = await userRepo.update(userId, {
  name: 'New Name',
  updated_at: '2024-01-01T00:00:00.000Z' // ✅ Uses this instead
})
```

### Bypass Timestamps

Sometimes you need to skip automatic timestamps:

```typescript
// Create without timestamps
const user = await userRepo.createWithoutTimestamps({
  email: 'system@example.com',
  name: 'System User'
})

console.log(user.created_at) // null (not set)
console.log(user.updated_at) // null (not set)

// Update without modifying updated_at
const updated = await userRepo.updateWithoutTimestamp(userId, {
  name: 'Silent Update'
})

console.log(updated.updated_at) // null (unchanged)
```

**Use Cases:**

- Migrating historical data with specific timestamps
- System operations that shouldn't update timestamps
- Preserving original timestamps when copying records
- Testing with fixed timestamps

---

## 🔍 Query Helpers

The plugin adds 10 powerful query methods to your repositories.

### Find by Creation Date

#### findCreatedAfter

Find records created after a specific date:

```typescript
// Find users created after Jan 1, 2024
const users = await userRepo.findCreatedAfter('2024-01-01T00:00:00.000Z')

// Find users created in the last 7 days
const weekAgo = new Date()
weekAgo.setDate(weekAgo.getDate() - 7)
const recentUsers = await userRepo.findCreatedAfter(weekAgo)

// With Unix timestamp
const users = await userRepo.findCreatedAfter(1704067200)
```

#### findCreatedBefore

Find records created before a specific date:

```typescript
// Find users created before Dec 31, 2023
const users = await userRepo.findCreatedBefore('2023-12-31T23:59:59.999Z')

// Find users created more than 30 days ago
const monthAgo = new Date()
monthAgo.setDate(monthAgo.getDate() - 30)
const oldUsers = await userRepo.findCreatedBefore(monthAgo)
```

#### findCreatedBetween

Find records created within a date range:

```typescript
// Find users created in January 2024
const users = await userRepo.findCreatedBetween(
  '2024-01-01T00:00:00.000Z',
  '2024-01-31T23:59:59.999Z'
)

// Find users created this week
const weekStart = new Date()
weekStart.setDate(weekStart.getDate() - weekStart.getDay())
const weekEnd = new Date()
const thisWeek = await userRepo.findCreatedBetween(weekStart, weekEnd)

// Find users created between two Unix timestamps
const users = await userRepo.findCreatedBetween(1704067200, 1706745599)
```

### Find by Update Date

#### findUpdatedAfter

Find records updated after a specific date:

```typescript
// Find users updated today
const today = new Date()
today.setHours(0, 0, 0, 0)
const updatedToday = await userRepo.findUpdatedAfter(today)

// Find users updated in last hour
const hourAgo = new Date(Date.now() - 60 * 60 * 1000)
const recentlyModified = await userRepo.findUpdatedAfter(hourAgo)
```

### Recently Created/Updated

#### findRecentlyCreated

Get the most recently created records:

```typescript
// Get 10 most recent users (default)
const latest = await userRepo.findRecentlyCreated()

// Get 50 most recent users
const latest50 = await userRepo.findRecentlyCreated(50)

// Get latest user
const latestUser = await userRepo.findRecentlyCreated(1)
console.log(latestUser[0]) // Most recent user
```

#### findRecentlyUpdated

Get the most recently updated records:

```typescript
// Get 10 most recently updated users (default)
const recent = await userRepo.findRecentlyUpdated()

// Get 25 most recently updated
const recent25 = await userRepo.findRecentlyUpdated(25)

// Monitor active records
setInterval(async () => {
  const active = await userRepo.findRecentlyUpdated(5)
  console.log('Recently active users:', active)
}, 5000)
```

### Touch Records

#### touch

Update a record's `updated_at` without changing any other fields:

```typescript
// Touch a user (update their updated_at timestamp)
await userRepo.touch(userId)

// Useful for:
// - "Last seen" tracking
// - Refresh TTL for cached data
// - Activity tracking
// - Session management

// Example: Track user activity
await userRepo.touch(userId)
const user = await userRepo.findById(userId)
console.log(`User last active: ${user.updated_at}`)
```

---

## 🚀 Batch Operations

The plugin provides efficient batch operations for working with multiple records at once.

### createMany

Create multiple records with timestamps in a single efficient bulk INSERT operation.

```typescript
// Create multiple users at once
const users = await userRepo.createMany([
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
  { name: 'Charlie', email: 'charlie@example.com' }
])

// All records created with the same timestamp
users.forEach(user => {
  console.log(user.created_at) // Same timestamp for all
})

// Empty arrays are handled gracefully
const empty = await userRepo.createMany([])
console.log(empty.length) // 0
```

**Performance Benefits:**

- Single database roundtrip instead of N queries
- All records get the same timestamp (consistent)
- ~10-100x faster than individual creates for large batches

**Example: Bulk Import**

```typescript
// Import 1000 users from CSV
const csvData = parseCsv('users.csv')
const inputs = csvData.map(row => ({
  name: row.name,
  email: row.email
}))

// Efficient bulk insert with timestamps
const imported = await userRepo.createMany(inputs)
console.log(`Imported ${imported.length} users`)
```

### updateMany

Update multiple records with the same data, automatically setting `updated_at` for all.

```typescript
// Update multiple users
const userIds = [1, 2, 3, 4, 5]
const updated = await userRepo.updateMany(userIds, {
  status: 'active'
})

// All records updated with same timestamp
updated.forEach(user => {
  console.log(user.status) // 'active'
  console.log(user.updated_at) // Same timestamp for all
})

// Works with string IDs too
const stringIds = ['user-1', 'user-2', 'user-3']
await userRepo.updateMany(stringIds, { verified: true })

// Empty arrays are handled gracefully
const empty = await userRepo.updateMany([], { status: 'inactive' })
console.log(empty.length) // 0
```

**Performance Benefits:**

- Single UPDATE query with WHERE id IN clause
- Single SELECT to fetch updated records
- Much faster than individual updates

**Example: Bulk Status Change**

```typescript
// Find users that haven't been updated recently
const thirtyDaysAgo = new Date()
thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30)
const active = await userRepo.findUpdatedAfter(thirtyDaysAgo)
const allUsers = await userRepo.findAll()
const inactive = allUsers.filter(u => !active.some(a => a.id === u.id))
const ids = inactive.map(u => u.id)

// Mark all as inactive in one operation
await userRepo.updateMany(ids, {
  status: 'inactive',
  reason: 'No activity for 30 days'
})
```

### touchMany

Update `updated_at` for multiple records without changing any other data.

```typescript
// Touch multiple users (update their timestamps)
const userIds = [1, 2, 3, 4, 5]
await userRepo.touchMany(userIds)

// Verify all were touched
const touched = await db.selectFrom('users').selectAll().where('id', 'in', userIds).execute()

touched.forEach(user => {
  console.log(user.updated_at) // All have same new timestamp
})

// Empty arrays are handled gracefully
await userRepo.touchMany([]) // No-op
```

**Performance Benefits:**

- Single UPDATE query setting only timestamp column
- No data fetched or returned
- Extremely fast even for large batches

**Example: Track Session Activity**

```typescript
// Track activity for all users in a session
const activeUserIds = await getActiveUserIds()

// Update their last_seen timestamp
await userRepo.touchMany(activeUserIds)

// Find recently active users
const active = await userRepo.findRecentlyUpdated(100)
console.log(`${active.length} users active recently`)
```

**Example: Refresh Cache TTL**

```typescript
// Keep cache fresh for popular items
const popularItems = await itemRepo.findPopular()
const ids = popularItems.map(i => i.id)

// Touch to refresh TTL
await itemRepo.touchMany(ids)
```

### Combining Batch Operations

All batch operations work seamlessly together:

```typescript
// 1. Create batch of users
const created = await userRepo.createMany([
  { name: 'User 1', email: 'user1@example.com' },
  { name: 'User 2', email: 'user2@example.com' },
  { name: 'User 3', email: 'user3@example.com' },
  { name: 'User 4', email: 'user4@example.com' }
])

// 2. Update some users
const idsToUpdate = created.slice(0, 2).map(u => u.id)
await userRepo.updateMany(idsToUpdate, { verified: true })

// 3. Touch others to mark as active
const idsToTouch = created.slice(2).map(u => u.id)
await userRepo.touchMany(idsToTouch)
```

---

## 🎯 Extended Methods

All repositories extended by the timestamps plugin gain these methods:

```typescript
interface TimestampMethods<T> {
  // Date range queries
  findCreatedAfter(date: Date | string | number): Promise<T[]>
  findCreatedBefore(date: Date | string | number): Promise<T[]>
  findCreatedBetween(start: Date | string | number, end: Date | string | number): Promise<T[]>
  findUpdatedAfter(date: Date | string | number): Promise<T[]>

  // Recent records
  findRecentlyCreated(limit?: number): Promise<T[]>
  findRecentlyUpdated(limit?: number): Promise<T[]>

  // Batch operations
  createMany(inputs: unknown[]): Promise<T[]>
  updateMany(ids: (number | string)[], input: unknown): Promise<T[]>
  touchMany(ids: (number | string)[]): Promise<void>

  // Bypass timestamps
  createWithoutTimestamps(input: unknown): Promise<T>
  updateWithoutTimestamp(id: number, input: unknown): Promise<T>

  // Utilities
  touch(id: number): Promise<void>
  getTimestampColumns(): { createdAt: string; updatedAt: string }
}
```

### getTimestampColumns

Get the configured column names:

```typescript
const plugin = timestampsPlugin({
  createdAtColumn: 'created',
  updatedAtColumn: 'modified'
})

const orm = await createORM(db, [plugin])
const postRepo = orm.createRepository(/* ... */)

const columns = postRepo.getTimestampColumns()
console.log(columns)
// { createdAt: 'created', updatedAt: 'modified' }

// Useful for:
// - Dynamic query building
// - Migration scripts
// - Documentation generation
// - Runtime introspection
```

---

## 🔧 Advanced Usage

### Multiple Plugins

Combine timestamps with other plugins via @kysera/executor's Unified Execution Layer:

```typescript
import { createExecutor } from '@kysera/executor'
import { timestampsPlugin } from '@kysera/timestamps'
import { softDeletePlugin } from '@kysera/soft-delete'
import { auditPlugin } from '@kysera/audit'

// Register all plugins with Unified Execution Layer
const executor = await createExecutor(db, [
  timestampsPlugin(),
  softDeletePlugin(),
  auditPlugin({ getUserId: () => currentUserId })
])

const orm = await createORM(executor, [])

// All plugins work together seamlessly:
const user = await userRepo.create({ email: 'test@example.com', name: 'Test' })
// ✅ created_at added by timestamps plugin
// ✅ deleted_at set to null by soft-delete plugin
// ✅ Audit log created by audit plugin
```

### Transaction Support

Timestamps work seamlessly with transactions:

```typescript
await db.transaction().execute(async trx => {
  const txRepo = userRepo.withTransaction(trx)

  const user = await txRepo.create({
    email: 'alice@example.com',
    name: 'Alice'
  })
  // ✅ created_at added

  await txRepo.update(user.id, {
    name: 'Alice Smith'
  })
  // ✅ updated_at added

  // If transaction fails, all changes (including timestamps) are rolled back
})
```

### Conditional Timestamps

Different timestamp strategies per environment:

```typescript
const plugin = timestampsPlugin({
  getTimestamp: () => {
    if (process.env.NODE_ENV === 'test') {
      // Fixed timestamp for tests
      return '2024-01-01T00:00:00.000Z'
    }

    if (process.env.USE_UTC_MIDNIGHT === 'true') {
      // UTC midnight for batch processing
      const now = new Date()
      now.setUTCHours(0, 0, 0, 0)
      return now.toISOString()
    }

    // Default: current timestamp
    return new Date().toISOString()
  }
})
```

### Repository Pattern with Timestamps

```typescript
// Define repository factory
function createUserRepository(executor: Executor<Database>) {
  const factory = createRepositoryFactory(executor)
  return factory.create<'users', User>({
    tableName: 'users',
    mapRow: row => ({
      id: row.id,
      email: row.email,
      name: row.name,
      createdAt: new Date(row.created_at), // Convert to Date
      updatedAt: row.updated_at ? new Date(row.updated_at) : null
    }),
    schemas: {
      create: CreateUserSchema,
      update: UpdateUserSchema
    }
  })
}

// Create plugin container with timestamps
const orm = await createORM(db, [timestampsPlugin()])

// Create repository (automatically extended)
const userRepo = orm.createRepository(createUserRepository)

// Use extended methods
const recent = await userRepo.findRecentlyCreated(10)
```

---

## 🗄️ Multi-Database Support

The plugin automatically adapts to your database.

### PostgreSQL

```typescript
import { Kysely, PostgresDialect } from 'kysely'
import { Pool } from 'pg'

const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({ /* config */ })
  })
})

// Recommended schema
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP
);

// Timestamp format: ISO string
const plugin = timestampsPlugin({
  dateFormat: 'iso'  // "2024-01-15T10:30:00.000Z"
})
```

### MySQL

```typescript
import { Kysely, MysqlDialect } from 'kysely'
import { createPool } from 'mysql2'

const db = new Kysely<Database>({
  dialect: new MysqlDialect({
    pool: createPool({ /* config */ })
  })
})

// Recommended schema
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP
);

// Timestamp format: ISO string or DATETIME
const plugin = timestampsPlugin({
  dateFormat: 'iso'  // Works with DATETIME columns
})
```

### SQLite

```typescript
import { Kysely, SqliteDialect } from 'kysely'
import Database from 'better-sqlite3'

const db = new Kysely<Database>({
  dialect: new SqliteDialect({
    database: new Database('app.db')
  })
})

// Recommended schema (use TEXT for timestamps)
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  created_at TEXT,
  updated_at TEXT
);

// Timestamp format: ISO string or Unix
const plugin = timestampsPlugin({
  dateFormat: 'iso'  // Stored as TEXT
})

// Or use Unix timestamps with INTEGER columns
const plugin = timestampsPlugin({
  dateFormat: 'unix'  // Stored as INTEGER
})
```

---

## 🎨 Type Safety

The plugin is fully type-safe with TypeScript.

### Type-Safe Repository Extensions

```typescript
import type { TimestampMethods } from '@kysera/timestamps'

// Repository automatically includes timestamp methods
type UserRepository = Repository<User, Database> & TimestampMethods<User>

const userRepo: UserRepository = orm.createRepository(/* ... */)

// ✅ Type-safe method calls
const recent: User[] = await userRepo.findRecentlyCreated(10)
const after: User[] = await userRepo.findCreatedAfter('2024-01-01')
const between: User[] = await userRepo.findCreatedBetween('2024-01-01', '2024-12-31')

// ❌ Type error: wrong argument type
await userRepo.findCreatedAfter(12345) // Error: number not assignable

// ❌ Type error: method doesn't exist
await userRepo.nonExistentMethod() // Error: method doesn't exist
```

### Database Schema Types

```typescript
import type { Generated } from 'kysely'

interface Database {
  users: {
    id: Generated<number>
    email: string
    name: string
    created_at: Generated<Date> // Auto-generated
    updated_at: Date | null // Nullable
  }
}

// TypeScript ensures correct column types
const plugin = timestampsPlugin({
  createdAtColumn: 'created_at', // ✅ Must match schema
  updatedAtColumn: 'updated_at' // ✅ Must match schema
})
```

---

## 🔒 Schema Validation (Optional)

The timestamps plugin provides optional Zod schemas for configuration validation. These are exported from a separate subpath to keep Zod as an optional dependency.

### Installation

```bash
# Zod is optional - only needed if you use schema validation
npm install zod
```

### Usage

```typescript
import { TimestampsOptionsSchema } from '@kysera/timestamps/schema'

// Validate configuration
const result = TimestampsOptionsSchema.safeParse({
  createdAtColumn: 'created_at',
  updatedAtColumn: 'updated_at',
  setUpdatedAtOnInsert: true,
  dateFormat: 'iso'
})

if (result.success) {
  console.log('Valid options:', result.data)
  const plugin = timestampsPlugin(result.data)
} else {
  console.error('Invalid configuration:', result.error.issues)
}
```

### Schema Fields

The `TimestampsOptionsSchema` validates:

| Field | Type | Description |
|-------|------|-------------|
| `createdAtColumn` | `string?` | Column name for creation timestamp |
| `updatedAtColumn` | `string?` | Column name for update timestamp |
| `setUpdatedAtOnInsert` | `boolean?` | Set updated_at on insert |
| `tables` | `string[]?` | Whitelist of tables |
| `excludeTables` | `string[]?` | Blacklist of tables |
| `getTimestamp` | `function?` | Custom timestamp generator |
| `dateFormat` | `'iso' \| 'unix' \| 'date'?` | Timestamp format |
| `primaryKeyColumn` | `string?` | Primary key column name |

### Type Inference

```typescript
import { TimestampsOptionsSchema, type TimestampsOptionsSchemaType } from '@kysera/timestamps/schema'

// Type is inferred from the schema
const options: TimestampsOptionsSchemaType = {
  createdAtColumn: 'created',
  updatedAtColumn: 'modified',
  dateFormat: 'unix'
}
```

### CLI Integration

The schema is particularly useful for CLI tools and configuration files:

```typescript
import { TimestampsOptionsSchema } from '@kysera/timestamps/schema'
import { readFileSync } from 'fs'

// Validate config file
const config = JSON.parse(readFileSync('timestamps.config.json', 'utf-8'))
const validated = TimestampsOptionsSchema.parse(config)
```

---

## 📖 API Reference

### timestampsPlugin(options?)

Creates a timestamps plugin instance.

**Parameters:**

```typescript
interface TimestampsOptions {
  createdAtColumn?: string // Default: 'created_at'
  updatedAtColumn?: string // Default: 'updated_at'
  setUpdatedAtOnInsert?: boolean // Default: false
  tables?: string[] // Default: undefined (all tables)
  excludeTables?: string[] // Default: undefined
  getTimestamp?: () => Date | string | number // Custom generator
  dateFormat?: 'iso' | 'unix' | 'date' // Default: 'iso'
  primaryKeyColumn?: string // Default: 'id'
}
```

**Returns:** `Plugin` instance

**Example:**

```typescript
const plugin = timestampsPlugin({
  createdAtColumn: 'created',
  updatedAtColumn: 'modified',
  dateFormat: 'unix'
})
```

---

### Repository Methods

#### findCreatedAfter(date)

Find records created after a date.

**Parameters:**

- `date: Date | string | number` - Date to compare against

**Returns:** `Promise<T[]>`

---

#### findCreatedBefore(date)

Find records created before a date.

**Parameters:**

- `date: Date | string | number` - Date to compare against

**Returns:** `Promise<T[]>`

---

#### findCreatedBetween(startDate, endDate)

Find records created between two dates (inclusive).

**Parameters:**

- `startDate: Date | string | number` - Start date
- `endDate: Date | string | number` - End date

**Returns:** `Promise<T[]>`

---

#### findUpdatedAfter(date)

Find records updated after a date.

**Parameters:**

- `date: Date | string | number` - Date to compare against

**Returns:** `Promise<T[]>`

---

#### findRecentlyCreated(limit?)

Get most recently created records.

**Parameters:**

- `limit?: number` - Number of records (default: 10)

**Returns:** `Promise<T[]>`

---

#### findRecentlyUpdated(limit?)

Get most recently updated records.

**Parameters:**

- `limit?: number` - Number of records (default: 10)

**Returns:** `Promise<T[]>`

---

#### createWithoutTimestamps(input)

Create a record without adding timestamps.

**Parameters:**

- `input: unknown` - Create data

**Returns:** `Promise<T>`

---

#### updateWithoutTimestamp(id, input)

Update a record without modifying updated_at.

**Parameters:**

- `id: number` - Record ID
- `input: unknown` - Update data

**Returns:** `Promise<T>`

---

#### touch(id)

Update only the updated_at timestamp.

**Parameters:**

- `id: number` - Record ID

**Returns:** `Promise<void>`

---

#### getTimestampColumns()

Get configured column names.

**Returns:** `{ createdAt: string; updatedAt: string }`

---

#### createMany(inputs)

Create multiple records with timestamps in a single bulk INSERT operation.

**Parameters:**

- `inputs: unknown[]` - Array of create data objects

**Returns:** `Promise<T[]>` - Array of created records

**Example:**

```typescript
const users = await userRepo.createMany([
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' }
])
```

---

#### updateMany(ids, input)

Update multiple records with the same data, automatically setting updated_at.

**Parameters:**

- `ids: (number | string)[]` - Array of record IDs to update
- `input: unknown` - Update data (applied to all records)

**Returns:** `Promise<T[]>` - Array of updated records

**Example:**

```typescript
const updated = await userRepo.updateMany([1, 2, 3], {
  status: 'active'
})
```

---

#### touchMany(ids)

Update only the updated_at timestamp for multiple records.

**Parameters:**

- `ids: (number | string)[]` - Array of record IDs to touch

**Returns:** `Promise<void>`

**Example:**

```typescript
await userRepo.touchMany([1, 2, 3, 4, 5])
```

---

## ✨ Best Practices

### 1. Use Nullable updated_at

```typescript
// ✅ Good: updated_at is nullable
interface Database {
  users: {
    id: Generated<number>
    created_at: Generated<Date>
    updated_at: Date | null // ✅ Null until first update
  }
}

// ❌ Bad: updated_at not nullable
interface Database {
  users: {
    updated_at: Date // ❌ What value on insert?
  }
}
```

### 2. Use Generated for created_at

```typescript
// ✅ Good: created_at is generated
interface Database {
  users: {
    created_at: Generated<Date> // ✅ Auto-generated
  }
}

// ⚠️ OK but verbose: created_at is optional
interface Database {
  users: {
    created_at: Date // Must be provided or plugin adds it
  }
}
```

### 3. Consistent Column Naming

```typescript
// ✅ Good: Consistent across all tables
const plugin = timestampsPlugin({
  createdAtColumn: 'created_at',
  updatedAtColumn: 'updated_at'
})

// ❌ Bad: Different names per table
// (Use multiple plugin instances instead)
```

### 4. Whitelist System Tables

```typescript
// ✅ Good: Exclude system/config tables
const plugin = timestampsPlugin({
  excludeTables: ['migrations', 'config', 'sessions', 'cache']
})

// ✅ Also Good: Explicit whitelist
const plugin = timestampsPlugin({
  tables: ['users', 'posts', 'comments', 'likes']
})
```

### 5. Use ISO Format for Portability

```typescript
// ✅ Good: ISO strings work everywhere
const plugin = timestampsPlugin({
  dateFormat: 'iso' // Portable across databases
})

// ⚠️ OK: Unix timestamps for performance
const plugin = timestampsPlugin({
  dateFormat: 'unix' // Good for INTEGER columns
})
```

### 6. Index Timestamp Columns

```sql
-- ✅ Good: Index for timestamp queries
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_users_updated_at ON users(updated_at);

-- Enables fast queries:
-- - findCreatedAfter
-- - findRecentlyCreated
-- - findUpdatedAfter
```

### 7. Use touch() for Activity Tracking

```typescript
// ✅ Good: Lightweight activity tracking
await userRepo.touch(userId) // Only updates timestamp

// ❌ Bad: Unnecessary data transfer
const user = await userRepo.findById(userId)
await userRepo.update(userId, user) // Fetches + updates all fields
```

### 8. Use Batch Operations for Multiple Records

```typescript
// ✅ Good: Batch operations (single query)
const users = await userRepo.createMany([
  { name: 'User 1', email: 'user1@example.com' },
  { name: 'User 2', email: 'user2@example.com' },
  { name: 'User 3', email: 'user3@example.com' }
])
// 1 INSERT query

// ❌ Bad: Individual creates (N queries)
for (const input of inputs) {
  await userRepo.create(input)
}
// 3 INSERT queries (slow!)

// ✅ Good: Batch update
await userRepo.updateMany([1, 2, 3], { status: 'active' })
// 1 UPDATE + 1 SELECT

// ❌ Bad: Individual updates
for (const id of [1, 2, 3]) {
  await userRepo.update(id, { status: 'active' })
}
// 3 UPDATE queries (slow!)

// ✅ Good: Batch touch
await userRepo.touchMany([1, 2, 3, 4, 5])
// 1 UPDATE query

// ❌ Bad: Individual touches
for (const id of [1, 2, 3, 4, 5]) {
  await userRepo.touch(id)
}
// 5 UPDATE queries (slow!)
```

---

## ⚡ Performance

### Plugin Overhead

| Operation               | Base | With Timestamps | Overhead |
| ----------------------- | ---- | --------------- | -------- |
| **create**              | 2ms  | 2.05ms          | +0.05ms  |
| **update**              | 2ms  | 2.05ms          | +0.05ms  |
| **findById**            | 1ms  | 1ms             | 0ms      |
| **findRecentlyCreated** | -    | 5ms             | N/A      |

### Timestamp Format Performance

| Format   | Generation Time | Storage Size | Query Performance |
| -------- | --------------- | ------------ | ----------------- |
| **ISO**  | ~0.001ms        | 24-27 bytes  | Medium            |
| **Unix** | ~0.0005ms       | 4-8 bytes    | Fast              |
| **Date** | ~0.001ms        | Varies       | Medium            |

**Recommendation:** Use `unix` for high-performance time-series data, `iso` for general use.

### Query Performance

```typescript
// ✅ Fast: Uses index
const recent = await userRepo.findRecentlyCreated(10)
// SELECT * FROM users ORDER BY created_at DESC LIMIT 10
// Index: idx_users_created_at

// ✅ Fast: Range query with index
const range = await userRepo.findCreatedBetween(start, end)
// SELECT * FROM users WHERE created_at >= $1 AND created_at <= $2
// Index: idx_users_created_at

// ❌ Slow: No timestamp index
const all = await userRepo.findAll()
const sorted = all.sort((a, b) => b.createdAt - a.createdAt)
```

### Bundle Size

```
@kysera/timestamps: 2.89 KB (minified)
├── timestampsPlugin: 1.5 KB
├── Query helpers: 1.0 KB
└── Type definitions: 0.39 KB
```

---

## 🔧 Troubleshooting

### Timestamps Not Being Set

**Problem:** `created_at` is `null` after creating a record.

**Solutions:**

1. **Check plugin is registered:**

```typescript
// ❌ Plugin not registered
const orm = await createORM(db, [])

// ✅ Plugin registered
const orm = await createORM(db, [timestampsPlugin()])
```

2. **Check table is not excluded:**

```typescript
// Check configuration
const plugin = timestampsPlugin({
  excludeTables: ['users'] // ❌ Users excluded!
})

// Fix: Remove from exclusions
const plugin = timestampsPlugin({
  excludeTables: [] // ✅ No exclusions
})
```

3. **Check column exists in database:**

```sql
-- Check schema
DESCRIBE users;
-- OR
SELECT column_name FROM information_schema.columns
WHERE table_name = 'users';
```

### Wrong Date Format

**Problem:** Dates stored as strings instead of timestamps.

**Solution:** Match `dateFormat` to column type:

```typescript
// For TIMESTAMP/DATETIME columns
const plugin = timestampsPlugin({
  dateFormat: 'iso' // ✅ ISO string
})

// For INTEGER columns
const plugin = timestampsPlugin({
  dateFormat: 'unix' // ✅ Unix timestamp
})

// For DATE columns
const plugin = timestampsPlugin({
  dateFormat: 'date' // ✅ Date object
})
```

### Query Methods Not Available

**Problem:** `userRepo.findRecentlyCreated` is undefined.

**Solution:** Ensure you're using the plugin container's createRepository method:

```typescript
// ❌ Wrong: Direct factory (no plugin extensions)
const factory = createRepositoryFactory(db)
const userRepo = factory.create(/* ... */)
await userRepo.findRecentlyCreated() // ❌ Undefined

// ✅ Correct: Plugin container with plugins
const orm = await createORM(db, [timestampsPlugin()])
const userRepo = orm.createRepository(executor => {
  const factory = createRepositoryFactory(executor)
  return factory.create(/* ... */)
})
await userRepo.findRecentlyCreated() // ✅ Works!
```

### Timestamps in Transactions

**Problem:** Timestamps not added in transactions.

**Solution:** Use `withTransaction`:

```typescript
await db.transaction().execute(async trx => {
  // ❌ Wrong: Using original repo
  await userRepo.create({
    /* ... */
  })

  // ✅ Correct: Use transaction repo
  const txRepo = userRepo.withTransaction(trx)
  await txRepo.create({
    /* ... */
  }) // ✅ Timestamps added
})
```

---

## 📄 License

MIT © Kysera

---

## 🔗 Links

- [GitHub Repository](https://github.com/kysera-dev/kysera)
- [@kysera/repository Documentation](../repository/README.md)
- [@kysera/core Documentation](../core/README.md)
- [Kysely Documentation](https://kysely.dev)
- [Issue Tracker](https://github.com/kysera-dev/kysera/issues)

---

**Built with ❤️ for effortless timestamp management**