README

Moves log tables from the application database into a separate log storage (typically TimescaleDB) and prints the SQL that creates that storage from your Doctrine entities.

composer require adt/log-mover

Why

Log tables in the application are only a transfer station. Logs are kept far longer than it makes sense to burden the operational database with, and an audit trail must moreover live elsewhere than the system it reports on — whoever gets into the application must not be able to rewrite the records of what they did there.

A record carries its id. It can be traced back by it, the mover recognises by it what has already been moved, and references between moved tables (request_log_body.request_log_id) keep pointing at the right row.
Write first, delete second. Only what is provably in the target is deleted from the source. When the write fails, nothing disappears. A run interrupted between the write and the delete is harmless: the next one hits the duplicate key (ON CONFLICT DO NOTHING / ON DUPLICATE KEY UPDATE id = id) and skips it.
The target is never read. The application needs only INSERT there — essential for an audit trail that must not be readable from the application.
Memory does not grow with table width. Ids are read in batches, rows one by one; a log row can be megabytes.
Time goes over in UTC with an explicit offset, so a TIMESTAMPTZ target column cannot misread it in its own zone.

Configuration

extensions:
	logMover: ADT\LogMover\DI\LogMoverExtension

logMover:
	target: @nettrine.dbal.connections.logdb.connection
	tables:
		# a body before its header - see "Order" below
		- {entity: App\Model\Entities\RequestLogBody, hot: '1 month', retention: '1 month'}
		- {entity: App\Model\Entities\RequestLog, hot: '3 months', retention: '6 months'}
		- {entity: App\Model\Entities\AuditLog, hot: '3 months', retention: '13 months', readable: false}
		- {entity: App\Model\Entities\DeviceLog, table: device_log_archive}

Key
`target`	DBAL connection to the log storage. Required.
`entityManager`	Where the entity metadata comes from. Defaults to the autowired `EntityManagerInterface`; set it when you have several.
`source`	Source connection. Defaults to the entity manager's connection. Set it when your log entities are mapped in a different manager than the one owning the source tables — e.g. they are mapped in the log storage's manager so that the administration can read them from there.
`tables[].entity`	Log entity. Its table name is the source table.
`tables[].table`	Target table name, defaults to the source one.
`tables[].hot`, `retention`	Only for `log-mover:print-schema`: TimescaleDB compression boundary and retention.
`tables[].readable`	`false` = the application gets `INSERT` only in the target (audit trail). Default `true`.

Order

The list is moved top to bottom. Where tables are bound by ON DELETE CASCADE in the source (request_log_body → request_log), put the child first: once the parent is moved and deleted, the cascade takes its body with it. A narrow window remains — a body written after its step whose parent makes it into the same run loses its payload (the request metadata stays).

A log row must be immutable

A move hits a table where a request record is written first and its response appended later in the middle — the moved row can no longer be found in the source and cannot be completed. Solve it on the write side (two separate rows linked by a correlation id), not by postponing the move.

Running

With adt/background-queue (recommended)

When the project has a BackgroundQueue service, the extension registers everything itself: the queue callback (moveLogs → LogMover::moveAllOrFail()) and the log-mover:schedule command. The project adds only a cron line - no job class, no entry in backgroundQueue.callbacks:

* * * * * php bin/console log-mover:schedule

The job is recurring: once it finishes, the queue schedules it again right away (after waitingJobExpiration, a second by default), so logs are moved within seconds. Cron only starts it - after a deploy or a permanent failure; while a job is unfinished it publishes nothing. A failed move is retried with a growing delay (1, 2, 4, 8, 16 minutes), reported by mail after notifyOnNumberOfAttempts and shown in background-queue:monitor.

Switching from a project's own job under the same name (moveLogs) needs no queue cleanup: remove the project callback from backgroundQueue.callbacks (the extension refuses to compile while both exist) and the new callback takes over the job already in the queue.

logMover:
	queue:
		enabled: true    # null (default) = when a BackgroundQueue service exists, false = never
		name: logs       # queue name of the callback, as in backgroundQueue.callbacks
		priority: null

Without a queue

php bin/console log-mover:move              # --dry-run, --batch-size, --limit

From cron every minute. Overlapping runs are safe (the duplicate key stops the second write), only wasteful. LogMover is a service, moveAllOrFail() throws when any table failed - use it from your own job runner.

An unavailable or broken table fails only its own row; the others are moved.

Creating the target storage

php bin/console log-mover:print-schema

Prints SQL to run by hand — the application has no access to the target server, and that is the whole point: the users and the database (passwords deliberately left out), CREATE TABLE for each configured table, and on PostgreSQL a TimescaleDB hypertable with compression and retention policies where hot/retention are set.

Two users. The owner creates the schema and owns the retention policies; the application gets INSERT everywhere and SELECT only where readable. No UPDATE, DELETE, DROP or ALTER — only the retention policy deletes.
Derived from the entities, so it does not drift from the source.
No foreign keys. TimescaleDB does not allow one pointing at a hypertable, and a body may arrive before its header anyway.
The id is not generated — the mover brings it.
Time is TIMESTAMP(6) WITH TIME ZONE. DBAL would declare TIMESTAMP(0) and silently round away the milliseconds a request logger writes.
JSON is always JSONB on PostgreSQL — payloads are searched during incidents and JSONB can be indexed (GIN) and queried with @>. What it drops (key order, whitespace, duplicate keys) is already gone after the logger's decode → sanitize → encode.
On a hypertable the primary key is (id, created_at) — TimescaleDB requires the partitioning column in every unique key.

One target per source

Ids come from the source database, so two sources in one target table would collide. Every project (and every stage) has its own target database.

Tests

composer test

adt / log-mover

Package info

Security

Statistics