pkg/database/postgres/pguint64.go (1)

121-129: Nit: wrong error text in EncodeBinary.

Message says “encode text” in a binary encoder.

Apply:

 case pgtype.Undefined:
-  return nil, errors.New("encode text status undefined status")
+  return nil, errors.New("encode binary status undefined status")

internal/storage/postgres/watch_test.go (1)

151-156: Brittle assertion on exact error text.

Tests matching “encode text status undefined status” couple to an internal message from Value(); any cleanup will break them. Prefer checking error type/category or substring from your error mapping layer.

Also applies to: 174-180

internal/storage/postgres/migrations/20250913120000_fix_xid_wraparound.sql (1)

1-19: Migration does a full-table UPDATE; consider batching to reduce locks and bloat.

On large tables this can be heavy. Optional: update in chunks (ctid or id ranges) and VACUUM after.

pkg/database/postgres/pguint64_test.go (3)

63-84: Add overflow/large-input DecodeText case

Include a DecodeText case that attempts to parse a value > math.MaxUint64 to ensure it errors predictably.

Example to add:

It("Case 4: Invalid input - overflow", func() {
	var p pguint64
	err := p.DecodeText(nil, []byte("18446744073709551616")) // MaxUint64 + 1
	Expect(err).Should(HaveOccurred())
})

---

109-147: Scan negative int64 should error

Mirror the Set(-1) behavior by asserting that Scan(int64(-1)) fails with an informative error.

Example to add:

It("Case 6: Error with negative int64", func() {
	var p pguint64
	err := p.Scan(int64(-1))
	Expect(err).Should(HaveOccurred())
})

---

149-170: Boundary test for Value/Present near MaxInt64

Given Value() returns int64, add a Present case at math.MaxInt64 to verify no truncation and to document the chosen sentinel domain.

Example to add:

It("Case 4: Present at MaxInt64 boundary", func() {
	p := pguint64{Uint: uint64(math.MaxInt64), Status: pgtype.Present}
	v, err := p.Value()
	Expect(err).ShouldNot(HaveOccurred())
	Expect(v).Should(Equal(int64(math.MaxInt64)))
})

internal/storage/postgres/dataWriter.go (1)

437-439: Prefer table name constants for inserts

Hardcoding "relation_tuples"/"attributes" risks drift with consts.go. Consider using the constants for inserts too.

Example:

// e.g., fmt.Sprintf("INSERT INTO %s (...", RelationTuplesTable)

If switching, add fmt to imports.

Also applies to: 501-503

pkg/database/postgres/xid8_test.go (2)

74-88: Add EncodeText Undefined-status error case

Parity with pguint64 tests helps ensure consistent behavior.

Example to add:

It("Case 3: Undefined status", func() {
	x := XID8{Status: pgtype.Undefined}
	_, err := x.EncodeText(nil, nil)
	Expect(err).Should(HaveOccurred())
})

---

123-137: Add Value Undefined-status error case

Covers final missing branch.

Example to add:

It("Case 3: Undefined status", func() {
	x := XID8{Status: pgtype.Undefined}
	_, err := x.Value()
	Expect(err).Should(HaveOccurred())
})

pkg/database/postgres/postgres_test.go (3)

104-110: Avoid asserting panics for Close()

Panic on Close with nil pools is brittle; prefer a no-op or clear error. Consider making Close nil-safe and updating the test accordingly.

---

23-27: Brittle struct-shape assertions on XID8

Asserting fields (Uint, Status) ties tests to internal representation. Prefer compile-time interface/behavior checks (e.g., that XID8 satisfies required interfaces) or method-level behavior tests.

---

123-137: Hard-coding internal map keys is fragile

Validating exact keys in queryExecModes/planCacheModes may fail on harmless internal changes. Consider testing presence of only the modes your code actually uses, or validating via exported accessors.

Also applies to: 141-155

internal/storage/postgres/utils/common.go (3)

168-175: Backoff: prefer NewTimer over time.After to avoid timer leaks

When ctx cancels first, time.After’s timer isn’t stopped. Use NewTimer.

- select {
- case <-time.After(nextBackoff):
- case <-ctx.Done():
- }
+ timer := time.NewTimer(nextBackoff)
+ defer timer.Stop()
+ select {
+ case <-timer.C:
+ case <-ctx.Done():
+ }

Also applies to: 176-181

---

186-193: Comment/code mismatch in secureRandomFloat64

Comment says “bit shifting,” code uses division. Either adjust comment or implement shift-based scaling.

Comment-only fix:

- // Use bit shifting instead of division for better performance
- return float64(binary.BigEndian.Uint64(b[:])) / (1 << 63) / 2.0
+ // Scale to [0,1) using a 64-bit denominator
+ return float64(binary.BigEndian.Uint64(b[:])) / (1 << 63) / 2.0

Or implement shift-based normalization if you prefer.

---

28-31: De-duplicate sentinel source of truth

ActiveRecordTxnID and MaxXID8Value encode the same magnitude twice. Derive the SQL literal from the uint64 to prevent drift.

- ActiveRecordTxnID = uint64(9223372036854775807)
- MaxXID8Value      = "'9223372036854775807'::xid8"
+ ActiveRecordTxnID = uint64(9223372036854775807)
+ // Note: var (not const) to derive from ActiveRecordTxnID
+)
+
+var MaxXID8Value = fmt.Sprintf("'%d'::xid8", ActiveRecordTxnID)

pkg/database/postgres/xid8.go (2)

12-15: pgSnapshot/interface and codec usage unclear

• Embedding pgtype.Value doesn’t make pgSnapshot implement pgtype.Value (see failing test).
• The standalone codec types only expose FormatCode; ensure they’re actually used by pgtype/pgx, or remove them.

Options:

• If a concrete pgx/pgtype snapshot type exists, alias it:

- type pgSnapshot struct {
-   pgtype.Value
- }
+ // If available in your pgtype version:
+ type pgSnapshot = pgtype.Snapshot

• Or drop pgSnapshot entirely and the test asserting its conformance.

Also applies to: 17-22, 68-76

---

23-66: Forwarders on XID8 look fine; consider compile-time interface checks

Add static assertions to catch regressions if pguint64 changes.

// Optional (in this file):
var _ driver.Valuer = XID8{}
var _ interface{ Scan(interface{}) error } = (*XID8)(nil)

Configuration used: CodeRabbit UI

Review profile: CHILL

Plan: Pro

internal/storage/postgres/dataReader.go (1)

32-39: Comment mismatches actual isolation level.

Doc says “Repeatable Read,” code sets ReadCommitted + ReadOnly. Update the comment.

-// It initializes a new DataReader with a given database, a logger, and sets transaction options to be read-only with Repeatable Read isolation level.
+// It initializes a new DataReader with a given database and sets transaction options to ReadCommitted + ReadOnly.

internal/storage/postgres/watch.go (2)

285-309: Check iterator errors after loop (relation tuples).

Add rows.Err() handling to catch driver-level iteration errors.

 for trows.Next() {
   ...
 }
+if err := trows.Err(); err != nil {
+	slog.ErrorContext(ctx, "iteration error for relation tuples", slog.Any("error", err))
+	return nil, err
+}

---

312-347: Check iterator errors after loop (attributes).

Same for attributes iteration.

 for arows.Next() {
   ...
 }
+if err := arows.Err(); err != nil {
+	slog.ErrorContext(ctx, "iteration error for attributes", slog.Any("error", err))
+	return nil, err
+}

internal/storage/postgres/dataWriter.go (1)

444-449: Expire-by-sentinel updates are correct; consider aiding them with an index.

Using utils.ActiveRecordTxnID in the WHERE ensures only active records get expired. Consider adding/supporting a partial index to keep these updates fast:

• relation_tuples: (tenant_id, entity_type, entity_id, relation, subject_type, subject_id, subject_relation) WHERE expired_tx_id = ACTIVE
• attributes: (tenant_id, entity_type, entity_id, attribute) WHERE expired_tx_id = ACTIVE

If migrations already add these, ignore.

Also applies to: 508-513

Configuration used: CodeRabbit UI

Review profile: CHILL

Plan: Pro

internal/storage/postgres/utils/common.go (1)

154-160: min() is undefined; clamp retries explicitly

This won’t compile in Go. Clamp with simple int logic.

-		shift := min(retries, 5) // Cap at 2^5 = 32, so max backoff is 640ms
-		baseBackoff = baseBackoff << shift
+		shift := retries
+		if shift > 5 {
+			shift = 5 // Cap at 2^5 = 32, so max backoff is 640ms
+		}
+		baseBackoff = baseBackoff << shift

internal/storage/postgres/dataWriter.go (1)

20-20: LGTM: Clean import with proper aliasing

The import correctly uses the db alias for the postgres package, resolving the duplicate import issue flagged in previous reviews.

pkg/database/postgres/postgres_test.go (2)

29-33: Compilation issue resolved

This test was previously flagged for compilation issues, but the current implementation correctly assigns a pgSnapshot to a pgtype.Value interface, which should work if pgSnapshot properly implements the interface.

---

65-121: LGTM: Struct method tests with panic expectations

The tests correctly validate:

• Getter methods return expected configuration values
• Close and IsReady methods properly panic when pools are nil

The panic expectations align with the current implementation behavior.

internal/storage/postgres/watch.go (1)

20-20: LGTM: Import cleanup completed

The duplicate import issue has been resolved with only the aliased import remaining.

internal/storage/postgres/migrations/20250913120000_fix_xid_wraparound.sql (2)

14-19: Heads-up: ALTER COLUMN DEFAULT takes AccessExclusive lock

ALTER TABLE … SET DEFAULT will block writers briefly. Plan to run off-peak and monitor lock wait. If you want, I can provide a zero-downtime-ish rollout plan.

---

1-11: Optional: batch the backfill on large tables

If relation_tuples/attributes are big, consider chunked updates to reduce bloat/locks.

I can draft a CTE-based batched UPDATE if desired.

Also applies to: 14-19

docs/operations/repair.mdx (3)

8-20: Add Requirements note (PostgreSQL version and privileges)

Clarify that xid8 and pg_current_xact_id() require PostgreSQL ≥ 13 and suitable privileges.

Apply this diff:

  # Database Repair
@@
- The `permify repair datastore` command helps prevent PostgreSQL XID wraparound issues by safely advancing the transaction ID counter. This is essential after database migrations or when dealing with XID-related problems.
+ The `permify repair datastore` command helps prevent PostgreSQL XID wraparound issues by safely advancing the transaction ID counter. This is essential after database migrations or when dealing with XID-related problems.
+
+ > Requirements
+ > - PostgreSQL 13+ (xid8 support and pg_current_xact_id()).
+ > - A user with rights to start/commit transactions and read the `transactions` table.

---

78-92: Call out primary-only and replication/WAL impact

Recommend running on primary, expect WAL growth; avoid replicas.

Apply this diff:

 ## When to Use
@@
 - When encountering XID-related errors
+
+## Operational Notes
+
+- Run on the primary (not a hot standby).
+- Expect increased WAL; ensure disk headroom and monitor replication lag.
+- Prefer off-peak windows in busy systems.

---

73-77: Minor wording tweak

“transactions table” → “Permify transactions table” to avoid ambiguity.

-2. **Reference Analysis**: Finds the maximum transaction ID referenced in the transactions table
+2. **Reference Analysis**: Finds the maximum transaction ID referenced in the Permify `transactions` table

docs/package.json (1)

1-24: Make docs package private and fix placeholder metadata

Prevents accidental publish and aligns license/description with the repo.

Apply this diff:

 {
   "name": "docs",
   "version": "1.0.0",
-  "description": "Click on `Use this template` to copy the Mintlify starter kit. The starter kit contains examples including",
+  "description": "Permify documentation site (Mintlify).",
+  "private": true,
   "main": "gurubase.js",
@@
-  "author": "",
-  "license": "ISC"
+  "author": "",
+  "license": "Apache-2.0"
 }

internal/storage/postgres/utils/common.go (4)

28-31: Single source of truth for sentinel

Keep ActiveRecordTxnID (uint64) as the source of truth and avoid embedding the quoted string where possible.

I’m proposing placeholder-based SQL below to remove dependence on MaxXID8Value in this file.

---

172-175: Reduce log level to avoid noisy retries

Warn on every retry can spam logs; suggest Debug.

-	slog.WarnContext(ctx, "waiting before retry",
+	slog.DebugContext(ctx, "waiting before retry",

---

191-193: Fix comment to match implementation

You’re dividing by 2^64; there’s no bit shifting here.

-	// Use bit shifting instead of division for better performance
-	return float64(binary.BigEndian.Uint64(b[:])) / (1 << 63) / 2.0
+	// Normalize to [0,1) by dividing by 2^64 (avoids math package)
+	return float64(binary.BigEndian.Uint64(b[:])) / (1 << 63) / 2.0

---

34-60: Optional: unit tests for SnapshotQuery/GC builders

Add tests asserting generated SQL and args (Dollar placeholders) to prevent regressions when changing sentinels.

Also applies to: 62-100

internal/storage/postgres/utils/common_test.go (3)

31-33: Use shared sentinel constant to avoid drift.

Hardcoding '9223372036854775807'::xid8 in expectedSQL is brittle. Consider formatting the string with the package constant (e.g., utils.MaxXID8Value) to keep tests aligned with code.

-expectedSQL := "SELECT column FROM table WHERE (pg_visible_in_snapshot(created_tx_id, (select snapshot from transactions where id = '42'::xid8)) = true OR created_tx_id = '42'::xid8) AND ((pg_visible_in_snapshot(expired_tx_id, (select snapshot from transactions where id = '42'::xid8)) = false OR expired_tx_id = '9223372036854775807'::xid8) AND expired_tx_id <> '42'::xid8)"
+expectedSQL := fmt.Sprintf(
+  "SELECT column FROM table WHERE (pg_visible_in_snapshot(created_tx_id, (select snapshot from transactions where id = '42'::xid8)) = true OR created_tx_id = '42'::xid8) AND ((pg_visible_in_snapshot(expired_tx_id, (select snapshot from transactions where id = '42'::xid8)) = false OR expired_tx_id = %s) AND expired_tx_id <> '42'::xid8)",
+  utils.MaxXID8Value,
+)

---

42-44: Reduce brittleness in SQL assertions.

Same suggestion here—compose with the constant to avoid hardcoded sentinel repetition.

-expectedSQL := "DELETE FROM relation_tuples WHERE expired_tx_id <> '9223372036854775807'::xid8 AND expired_tx_id < '100'::xid8"
+expectedSQL := fmt.Sprintf(
+  "DELETE FROM relation_tuples WHERE expired_tx_id <> %s AND expired_tx_id < '100'::xid8",
+  utils.MaxXID8Value,
+)

---

51-53: Apply the same constant-driven formatting for tenant-aware GC.

-expectedSQL := "DELETE FROM relation_tuples WHERE tenant_id = ? AND expired_tx_id <> '9223372036854775807'::xid8 AND expired_tx_id < '100'::xid8"
+expectedSQL := fmt.Sprintf(
+  "DELETE FROM relation_tuples WHERE tenant_id = ? AND expired_tx_id <> %s AND expired_tx_id < '100'::xid8",
+  utils.MaxXID8Value,
+)

pkg/database/postgres/repair.go (3)

9-14: Avoid duplicating XID sentinel constants across packages.

Defining ActiveRecordTxnID/MaxXID8Value here risks divergence from internal/storage/postgres/utils. Consider moving them to a common exported package (e.g., pkg/xid) and importing from both places.

---

56-63: Populate RepairResult.Duration.

Capture elapsed time for observability.

+ start := time.Now()
 ...
 slog.InfoContext(ctx, "PostgreSQL transaction ID counter repair completed",
   slog.Int("transactions_advanced", result.CreatedTxIdFixed),
   slog.Int("errors", len(result.Errors)))
 
+ result.Duration = time.Since(start).String()
 return result, nil

Additionally, add:

 import (
   "context"
   "fmt"
   "log/slog"
+  "time"
 )

Also applies to: 105-110

---

86-93: Guard against int overflow in counterDelta.

Compute in uint64 and clamp to MaxInt to avoid overflow on very large deltas.

- counterDelta := int(maxReferencedXID - currentXID + 1000) // Add safety buffer
+ delta := maxReferencedXID - currentXID + 1000
+ if delta > uint64(^int(0)) {
+   return result, fmt.Errorf("delta too large: %d", delta)
+ }
+ counterDelta := int(delta)

pkg/database/postgres/repair_test.go (2)

95-117: Config fields MaxRetries/RetryDelay are unused.

Either implement simple retry/backoff in advanceXIDCounterByDelta or remove these fields to avoid confusion.

---

29-53: Result.Duration is never set by Repair.

If you keep Duration, add an assertion in a Repair integration test (dry-run path) once duration is populated.

pkg/database/postgres/pguint64.go (1)

121-128: Nit: fix error text in EncodeBinary

Message says “encode text” in binary path.

 case pgtype.Undefined:
-  return nil, errors.New("encode text status undefined status")
+  return nil, errors.New("encode binary status undefined status")

pkg/database/postgres/xid8.go (1)

3-7: Add compile-time interface assertions for safety

Assert XID8 implements sql.Scanner and driver.Valuer. Catches regressions early.

 import (
+	"database/sql"
 	"database/sql/driver"

 	"github.com/jackc/pgtype"
 )
@@
 func (x XID8) Value() (driver.Value, error) {
 	return (pguint64)(x).Value()
 }
+
+// Interface assertions
+var (
+	_ driver.Valuer = XID8{}
+	_ sql.Scanner   = (*XID8)(nil)
+)

Also applies to: 66-67

pkg/database/postgres/xid8_test.go (7)

11-41: Add boundary coverage for Set (zero, MaxUint64) and avoid brittle error assertions.

• Add success cases for 0 and math.MaxUint64.
• Current error checks rely on substrings; prefer sentinel errors or MatchError(ContainSubstring(...)).

Apply:

@@
 	Context("Set", func() {
@@
 		It("Case 2: Success with uint64", func() {
@@
 		})
+
+		It("Case 5: Success with zero value", func() {
+			var x XID8
+			err := x.Set(uint64(0))
+			Expect(err).ShouldNot(HaveOccurred())
+			Expect(x.Uint).Should(Equal(uint64(0)))
+			Expect(x.Status).Should(Equal(pgtype.Present))
+		})
+
+		It("Case 6: Success with MaxUint64", func() {
+			var x XID8
+			err := x.Set(^uint64(0))
+			Expect(err).ShouldNot(HaveOccurred())
+			Expect(x.Uint).Should(Equal(^uint64(0)))
+			Expect(x.Status).Should(Equal(pgtype.Present))
+		})

---

43-55: Assert the type of Get() result to catch accidental int64 returns.

Add a type assertion for the present case.

 		It("Case 1: Present status", func() {
 			x := XID8{Uint: 12345, Status: pgtype.Present}
 			result := x.Get()
+			Expect(result).Should(BeAssignableToTypeOf(uint64(0)))
 			Expect(result).Should(Equal(uint64(12345)))
 		})

---

57-72: Add negative DecodeText tests (non-numeric, overflow).

Covers common parsing failures.

 	Context("DecodeText", func() {
@@
 		})
 
+		It("Case 3: Error with non-numeric", func() {
+			var x XID8
+			err := x.DecodeText(nil, []byte("abc"))
+			Expect(err).Should(HaveOccurred())
+		})
+
+		It("Case 4: Error with overflow", func() {
+			var x XID8
+			// 18446744073709551616 = MaxUint64 + 1
+			err := x.DecodeText(nil, []byte("18446744073709551616"))
+			Expect(err).Should(HaveOccurred())
+		})
 	})

---

74-88: Optional: verify EncodeText appends to provided buffer.

Minor perf guard; pgtype encoders typically append to buf.

 		It("Case 1: Present status", func() {
 			x := XID8{Uint: 12345, Status: pgtype.Present}
-			result, err := x.EncodeText(nil, nil)
+			buf := make([]byte, 0, 8)
+			result, err := x.EncodeText(nil, buf)
 			Expect(err).ShouldNot(HaveOccurred())
 			Expect(string(result)).Should(Equal("12345"))
+			Expect(cap(result)).Should(BeNumerically(">=", 8))
 		})

---

90-121: Strengthen Scan() coverage with invalid inputs and edge cases.

Add cases for unsupported types, invalid strings, negative ints.

 	Context("Scan", func() {
@@
 		})
 
+		It("Case 5: Error with unsupported type", func() {
+			var x XID8
+			err := x.Scan(12.34)
+			Expect(err).Should(HaveOccurred())
+		})
+
+		It("Case 6: Error with invalid numeric string", func() {
+			var x XID8
+			err := x.Scan("not-a-number")
+			Expect(err).Should(HaveOccurred())
+		})
+
+		It("Case 7: Error with negative int64", func() {
+			var x XID8
+			err := x.Scan(int64(-1))
+			Expect(err).Should(HaveOccurred())
+			Expect(x.Status).ShouldNot(Equal(pgtype.Present))
+		})
 	})

---

123-137: Clarify Value() behavior for values > MaxInt64 (driver.Value cannot carry uint64).

Either return a string for out-of-range values or error deterministically; add a test to lock behavior.

 	Context("Value", func() {
@@
 		})
 
+		It("Case 3: Out-of-range value strategy", func() {
+			x := XID8{Uint: ^uint64(0), Status: pgtype.Present} // MaxUint64
+			_, err := x.Value()
+			// Decide and enforce one of:
+			// Expect(err).To(HaveOccurred()) // if Value() errors on overflow
+			// OR:
+			// s, ok := result.(string); Expect(ok).To(BeTrue()); Expect(s).To(Equal("18446744073709551615"))
+			_ = err
+		})
 	})

If you want, I can align the implementation and tests to your chosen strategy.

---

45-48: Prefer black-box setup to reduce coupling to struct fields.

Instead of struct literals with Uint/Status, set state via exported methods like Set to avoid brittle tests if internals change.

-			x := XID8{Uint: 12345, Status: pgtype.Present}
+			var x XID8
+			Expect(x.Set(uint64(12345))).To(Succeed())

(Apply similarly in EncodeText and Value contexts.)

Also applies to: 76-80, 125-129

Configuration used: CodeRabbit UI

Review profile: CHILL

Plan: Pro

pkg/database/postgres/repair.go (3)

113-120: LGTM! Proper handling of xid8 to uint64 conversion.

The implementation correctly casts xid8 to bigint and scans into int64 before converting to uint64, avoiding the truncation issue from previous versions.

---

133-187: LGTM! Proper implementation of XID advancement using separate transactions.

The implementation correctly advances XIDs using individual transactions with proper context cancellation checks and error handling. This addresses the issue from previous versions where a DO block was incorrectly used.

---

123-130: Handle NULL values from empty tables.

The MAX(id) query returns NULL on empty tables, which will cause a scan error. Use COALESCE to handle this case gracefully.

 func (p *Postgres) getMaxReferencedXID(ctx context.Context) (uint64, error) {
-	query := "SELECT (MAX(id)::text)::bigint FROM transactions"
+	query := "SELECT (COALESCE(MAX(id), '0'::xid8)::text)::bigint FROM transactions"
 	var x int64
 	if err := p.ReadPool.QueryRow(ctx, query).Scan(&x); err != nil {
 		return 0, err
 	}
 	return uint64(x), nil
 }

pkg/database/postgres/repair.go (2)

16-34: Consider adding upper bounds validation for configuration parameters.

While the code validates that BatchSize is positive, consider adding upper bounds to prevent potential resource exhaustion or performance issues.

Add validation for reasonable upper bounds:

 func DefaultRepairConfig() *RepairConfig {
 	return &RepairConfig{
 		BatchSize:  1000, // default batch size for XID advancement
 		MaxRetries: 3,
 		RetryDelay: 100,
 		DryRun:     false,
 		Verbose:    true,
 	}
 }
+
+// ValidateRepairConfig validates and normalizes the repair configuration
+func (c *RepairConfig) Validate() {
+	if c.BatchSize <= 0 || c.BatchSize > 10000 {
+		c.BatchSize = 1000
+	}
+	if c.MaxRetries < 0 || c.MaxRetries > 10 {
+		c.MaxRetries = 3
+	}
+	if c.RetryDelay < 0 || c.RetryDelay > 5000 {
+		c.RetryDelay = 100
+	}
+}

---

86-100: Consider making the safety buffer configurable.

The hardcoded safety buffer of 1000 XIDs might not be suitable for all scenarios. Consider making it configurable through RepairConfig.

 type RepairConfig struct {
 	BatchSize  int  // batch size for XID advancement
 	MaxRetries int  // maximum number of retries
 	RetryDelay int  // milliseconds
 	DryRun     bool // perform a dry run without making changes
 	Verbose    bool // enable verbose logging
+	SafetyBuffer int // XIDs to advance beyond the maximum referenced XID
 }

 func DefaultRepairConfig() *RepairConfig {
 	return &RepairConfig{
 		BatchSize:  1000, // default batch size for XID advancement
 		MaxRetries: 3,
 		RetryDelay: 100,
 		DryRun:     false,
 		Verbose:    true,
+		SafetyBuffer: 1000,
 	}
 }

And in the repair logic:

 if maxReferencedXID > currentXID {
-	counterDelta := int(maxReferencedXID - currentXID + 1000) // Add safety buffer
+	safetyBuffer := config.SafetyBuffer
+	if safetyBuffer <= 0 {
+		safetyBuffer = 1000
+	}
+	counterDelta := int(maxReferencedXID - currentXID + uint64(safetyBuffer))

Configuration used: CodeRabbit UI

Review profile: CHILL

Plan: Pro

internal/storage/postgres/utils/common.go (2)

144-151: min() is undefined in Go; build will fail

Go has no built-in min for ints. Replace with simple capping logic.

Apply:

-		shift := min(retries, 5) // Cap at 2^5 = 32, so max backoff is 640ms
-		baseBackoff = baseBackoff << shift
+		shift := retries
+		if shift > 5 {
+			shift = 5 // Cap at 2^5 = 32, so max backoff is 640ms
+		}
+		baseBackoff = baseBackoff << shift

---

37-53: Avoid passing a Sqlizer as a bound value; inline the subquery into the expression

Using squirrel.Expr(snapshotQuery, value) as an argument to another Expr’s “?” is fragile. Inline the subquery and bind only scalars to placeholders.

Apply:

-	snapshotQuery := "(select snapshot from transactions where id = ?::xid8)"
+	snapshotQuery := "(select snapshot from transactions where id = ?::xid8)"

-	createdWhere := squirrel.Or{
-		squirrel.Expr("pg_visible_in_snapshot(created_tx_id, ?) = true", squirrel.Expr(snapshotQuery, value)),
-		squirrel.Expr("created_tx_id = ?::xid8", value), // Include current transaction
-	}
+	createdWhere := squirrel.Or{
+		squirrel.Expr("pg_visible_in_snapshot(created_tx_id, "+snapshotQuery+") = true", value),
+		squirrel.Expr("created_tx_id = ?::xid8", value),
+	}

-	expiredWhere := squirrel.And{
-		squirrel.Or{
-			squirrel.Expr("pg_visible_in_snapshot(expired_tx_id, ?) = false", squirrel.Expr(snapshotQuery, value)),
-			squirrel.Expr("expired_tx_id = ?::xid8", ActiveRecordTxnID), // Never expired
-		},
-		squirrel.Expr("expired_tx_id <> ?::xid8", value), // Not expired by current transaction
-	}
+	expiredWhere := squirrel.And{
+		squirrel.Or{
+			squirrel.Expr("pg_visible_in_snapshot(expired_tx_id, "+snapshotQuery+") = false", value),
+			squirrel.Expr("expired_tx_id = ?::xid8", ActiveRecordTxnID),
+		},
+		squirrel.Expr("expired_tx_id <> ?::xid8", value),
+	}

internal/storage/postgres/utils/common.go (1)

174-183: Use 53-bit technique for uniform [0,1) without FP precision pitfalls

Derive a float via the upper 53 bits; this matches math/rand’s approach and avoids chained divisions.

Apply:

-	// Use bit shifting instead of division for better performance
-	return float64(binary.BigEndian.Uint64(b[:])) / (1 << 63) / 2.0
+	// Use upper 53 bits for uniform [0,1)
+	u := binary.BigEndian.Uint64(b[:]) >> 11
+	return float64(u) * (1.0 / (1 << 53))

Configuration used: CodeRabbit UI

Review profile: CHILL

Plan: Pro