Joseph Doherty
677263af57
The per-IP unlock throttle's counters were a process-local ConcurrentDictionary on each central node, so the effective fleet cap was N x MaxUnlockAttemptsPerIpPerHour per client (failing over to the other node handed the client a fresh budget) and a node restart cleared the window outright. The window now lives in the central MS SQL BundleUnlockAttempt table — the HA store both central nodes already share, so no new infrastructure, no new port, no consensus protocol: - Commons: BundleUnlockAttempt entity + IBundleUnlockAttemptRepository. - ConfigurationDatabase: entity config (one index, IX_BundleUnlockAttempt_Client_Attempted, serving both the windowed count and the prune), repository (AsNoTracking count, ExecuteDeleteAsync prune), DbSet, DI registration, migration AddBundleUnlockAttemptTable + the hand-written idempotent production script under docs/plans/sql/. - Transport: IBundleUnlockAttemptWindow seam + SqlBundleUnlockAttemptWindow (singleton; resolves the scoped repository from its own scope per call, like the central purge singletons). BundleUnlockRateLimiter keeps the policy and gains TryRegisterAttemptAsync; BundleImporter.LoadAsync calls it. Behaviour preserved from row 44: locked bundles spend no hourly budget (the lockout check still runs first), throttle denials burn no per-bundle strike and are NOT recorded so they cannot roll the window forward, unattributable clients share the stricter <unattributed> bucket, and the budget counts attempts rather than failures. Config keys and their semantics are unchanged, so the rig's 500 override stays valid. Three deliberate trades, documented in both Transport docs: - FAILS OPEN. A store error logs a Warning naming the client and cap, and permits the attempt. This is a best-effort rate cap over an already authenticated, Administrator-gated surface — the availability of legitimate imports outranks the cap, and a DB blip must not make the import wizard unusable. Falling back to the process-local window was considered and rejected: it would still block a legitimate operator's 11th import. - Approximate. Count-then-insert is not atomic across nodes, so simultaneous attempts can overshoot by the number in flight — bounded, self-correcting within the window, and not worth provider-specific raw SQL. - Pruned opportunistically on the unlock path (CAS-claimed at most every 15 min per process, bounded to 1000 rows, retaining 2x the window, best-effort try/catch) rather than by a new background service — Transport owns no central scheduler. Same shape as DeploymentService's terminal-record sweep. Tests: Transport.IntegrationTests 116 passed (+10 — two limiter instances over one store enforcing a single combined window, restart durability, denials burning no budget, sliding release, bucket isolation, store-failure fail-open, caller-cancellation not swallowed, no-shared-window fallback, prune and its negative control); ConfigurationDatabase 395 passed (+5).
46 lines
1.8 KiB
Transact-SQL
46 lines
1.8 KiB
Transact-SQL
-- Register row 52: the fleet-wide bundle-unlock throttle window.
|
|
--
|
|
-- Creates the central [BundleUnlockAttempt] table that both central nodes share, so
|
|
-- ScadaBridge:Transport:MaxUnlockAttemptsPerIpPerHour is a fleet cap rather than a
|
|
-- per-node, per-process one. Operational history, not audit: non-partitioned, standard
|
|
-- [PRIMARY] filegroup, no DB-role restriction, and rows are pruned opportunistically by
|
|
-- the application once they age past twice the trailing window.
|
|
--
|
|
-- Run with QUOTED_IDENTIFIER ON. Several tables in this database carry filtered indexes
|
|
-- and persisted computed columns, and SQL Server refuses DDL/DML against them with error
|
|
-- 1934 when the session has QUOTED_IDENTIFIER OFF -- which is exactly what `sqlcmd` gives
|
|
-- you by default (pass -I, or keep this header). The SETs sit in their own batch so they
|
|
-- are in force when the statements after the GO parse.
|
|
SET QUOTED_IDENTIFIER ON;
|
|
SET ANSI_NULLS ON;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260815154803_AddBundleUnlockAttemptTable'
|
|
)
|
|
BEGIN
|
|
EXEC(N'CREATE TABLE [BundleUnlockAttempt] (
|
|
[Id] bigint NOT NULL IDENTITY,
|
|
[ClientKey] varchar(64) NOT NULL,
|
|
[AttemptedAtUtc] datetime2 NOT NULL,
|
|
CONSTRAINT [PK_BundleUnlockAttempt] PRIMARY KEY ([Id])
|
|
)');
|
|
|
|
EXEC(N'CREATE INDEX [IX_BundleUnlockAttempt_Client_Attempted]
|
|
ON [BundleUnlockAttempt] ([ClientKey], [AttemptedAtUtc])');
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260815154803_AddBundleUnlockAttemptTable'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260815154803_AddBundleUnlockAttemptTable', N'10.0.7');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|