PostgreSQL schema snapshot

yaza db snapshot [output.json] connects to the PostgreSQL database selected by DATABASE_URL and writes a canonical catalog snapshot. The default path is yaza.schema.json. The connection uses the YAZA PostgreSQL v3 client and requires verified TLS.

The snapshot is versioned JSON rather than pg_dump output. Format version 2 records the PostgreSQL major version that produced the catalog and a SHA-256 fingerprint of the project's migration set. For PostgreSQL versions before 10, the version field retains the 9.6 form because PostgreSQL used minor releases as feature releases.

{
  "format_version": 2,
  "postgres_major_version": "17",
  "migration_fingerprint": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855",
  "tables": [
    {
      "schema": "public",
      "name": "items",
      "columns": [
        {
          "name": "id",
          "type": "bigint",
          "nullable": false,
          "default": null
        }
      ],
      "constraints": [
        {
          "name": "items_pkey",
          "kind": "primary_key",
          "definition": "PRIMARY KEY (id)",
          "deferrable": false,
          "initially_deferred": false,
          "validated": true
        }
      ]
    }
  ]
}

Tables are ordered by schema and name using the PostgreSQL C collation. Columns retain table declaration order. Constraints are ordered by name, kind, and definition using C collation. The catalog query fixes search_path to pg_catalog, uses PostgreSQL format_type for SQL type names, and requests non-pretty constraint definitions. PostgreSQL documents that non-pretty decompiled definitions are more likely to remain interpretable across server versions; ordered aggregates only have guaranteed element order when ORDER BY appears inside the aggregate call, which the generator does for both column and constraint arrays. The catalog includes regular and partitioned tables, column type/nullability/default metadata, and primary, unique, foreign-key, check, and exclusion constraints, including deferrability and validation flags.

The fingerprint is SHA-256 over the sorted migration records filename|sha256(contents) joined by line feeds. A migrations/ directory is required when generating or validating snapshots. Run yaza db migrate first so the migration ledger exists; yaza db snapshot then verifies that each local migration is recorded with the same checksum in yaza_schema_migrations before it writes the snapshot. It also recomputes the migration fingerprint after catalog inspection and aborts if the local files changed while snapshotting. Snapshot output is written to a unique temporary file in the destination directory, flushed, and atomically renamed so a failed write does not replace the previous complete snapshot. Replacing an existing regular file preserves its permission bits; a newly created snapshot is private to its owner.

Run yaza db validate-snapshot [file.json] to validate a snapshot without a database connection. The command defaults to yaza.schema.json; yaza check, yaza build, and yaza run validate that default file automatically when it exists in the current project directory. Validation checks strict JSON syntax, format version 2, PostgreSQL version text, the required table/column/constraint fields, and whether the migration fingerprint matches the current local files. Changing, adding, removing, or renaming a valid migration makes the snapshot stale and blocks these compiler commands until it is regenerated. Format-version-1 snapshots must be regenerated before use with this format.

This does not inspect the live database during offline compilation and cannot detect manual schema changes made outside the migration chain. tests/postgresql-live-smoke.sh now applies migrations twice, checks the ledger, generates a snapshot from live PostgreSQL catalogs, validates the migration fingerprint offline, and confirms the recorded PostgreSQL major version. This live snapshot path passed against PostgreSQL 17.6 on 2026-10-25. The runtime atomic text-write primitive was exercised locally for replacement and permission preservation. Typed SQL-to-shape declarations are not implemented. Standalone indexes, views, triggers, policies, privileges, identity/generated-column flags, domain definitions, and extension metadata are not included. Catalog SQL and output have not yet been verified across PostgreSQL major versions; the recorded version allows later tooling to select compatible validation rules.

← Kembali ke semua dokumentasiVersi dan status rilis →