109 lines
5.4 KiB
PL/PgSQL
109 lines
5.4 KiB
PL/PgSQL
-- ============================================================================
|
|
-- 0007_surface_bump_policy.sql
|
|
-- ----------------------------------------------------------------------------
|
|
-- Per-(person|org), per-surface availability-bump automation policy (brief H §H1).
|
|
--
|
|
-- One row per (user_id, [org_id], surface) holds the knobs the `bookings-*`
|
|
-- specialists read before issuing an availability bump:
|
|
-- - cadence_minutes : how often the policy wants a bump. The specialist clamps
|
|
-- this UP to the surface's tier floor at runtime (Tryst:
|
|
-- 180 for Basic/Standard, 120 for Premium/Premium+ per
|
|
-- surface-tryst.brief.md §canonical-facts). The column is
|
|
-- the *desired* cadence; the effective cadence is
|
|
-- max(cadence_minutes, tier_floor).
|
|
-- - active_hours : the local-time window bumps are allowed in. Stored as a
|
|
-- (start, end) pair of TIME values. The window MAY wrap
|
|
-- midnight (e.g. 10:00 → 02:00), so there is deliberately
|
|
-- NO start<end CHECK — wrap-around is resolved in the
|
|
-- policy service, not the schema.
|
|
-- - active_days : ISO weekdays (1=Mon … 7=Sun) bumps are allowed on.
|
|
-- - paused_until : vacation mode / kill-switch. While now() < paused_until,
|
|
-- the policy is paused and the specialist's precheck
|
|
-- rejects the bump. NULL = not paused.
|
|
--
|
|
-- Insert/update is owned by platform.api (policy-card.screen.md edits route
|
|
-- through it). Adapter specialists only READ this table via platform.api.
|
|
--
|
|
-- RLS mirrors 0005_surface_metrics.sql (tenant_isolation on user_id/org_id).
|
|
--
|
|
-- Spec source: H-recurring-chores.brief.md §H1; surface-tryst.brief.md §4 +
|
|
-- §canonical-facts; specialist-bookings-tryst.contract.md (Auto).
|
|
-- ============================================================================
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE surface_bump_policy (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
org_id UUID NULL REFERENCES orgs(id) ON DELETE CASCADE,
|
|
surface TEXT NOT NULL, -- surface_kind value (e.g. 'tryst')
|
|
|
|
enabled BOOLEAN NOT NULL DEFAULT true, -- ON/OFF master toggle (H1)
|
|
cadence_minutes INTEGER NOT NULL, -- desired cadence; effective = max(this, tier floor)
|
|
|
|
-- Active window. May wrap midnight; no ordering constraint by design.
|
|
active_start TIME NOT NULL DEFAULT '00:00',
|
|
active_end TIME NOT NULL DEFAULT '00:00', -- == active_start ⇒ 24h window
|
|
|
|
-- ISO weekday numbers (1=Mon … 7=Sun) the policy runs on. Default: every day.
|
|
active_days SMALLINT[] NOT NULL DEFAULT ARRAY[1,2,3,4,5,6,7]::SMALLINT[],
|
|
|
|
paused_until TIMESTAMPTZ NULL, -- vacation / kill-switch; NULL = live
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT surface_bump_policy_cadence_chk
|
|
CHECK (cadence_minutes >= 1),
|
|
CONSTRAINT surface_bump_policy_active_days_chk
|
|
CHECK (
|
|
active_days <@ ARRAY[1,2,3,4,5,6,7]::SMALLINT[]
|
|
AND array_length(active_days, 1) >= 1
|
|
),
|
|
-- One policy row per tenant+surface. user_id is always present; org_id may be
|
|
-- NULL (person-owned) or set (org overlay) — both partial-unique indexes below.
|
|
CONSTRAINT surface_bump_policy_user_not_null CHECK (user_id IS NOT NULL)
|
|
);
|
|
|
|
-- Exactly one policy per (user, surface) for person-owned rows …
|
|
CREATE UNIQUE INDEX uq_surface_bump_policy_user_surface
|
|
ON surface_bump_policy (user_id, surface)
|
|
WHERE org_id IS NULL;
|
|
|
|
-- … and one per (user, org, surface) for org-overlay rows.
|
|
CREATE UNIQUE INDEX uq_surface_bump_policy_user_org_surface
|
|
ON surface_bump_policy (user_id, org_id, surface)
|
|
WHERE org_id IS NOT NULL;
|
|
|
|
CREATE INDEX idx_surface_bump_policy_user_surface
|
|
ON surface_bump_policy (user_id, surface);
|
|
|
|
CREATE INDEX idx_surface_bump_policy_org_surface
|
|
ON surface_bump_policy (org_id, surface)
|
|
WHERE org_id IS NOT NULL;
|
|
|
|
ALTER TABLE surface_bump_policy ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Tenant isolation — follows the canonical 0001 convention: the app sets the
|
|
-- `app.current_user_id` GUC per connection (read via current_user_uuid(), defined
|
|
-- in 0001), and org access is resolved through org_members membership. There is
|
|
-- NO `app.org_id` GUC — the runtime never sets one, so keying off it (as the
|
|
-- earlier 0004/0005 policies did) silently matched no rows. Mirrors 0001/0006.
|
|
CREATE POLICY tenant_isolation_surface_bump_policy ON surface_bump_policy
|
|
USING (
|
|
user_id = current_user_uuid()
|
|
OR (org_id IS NOT NULL
|
|
AND org_id IN (SELECT org_id FROM org_members
|
|
WHERE user_id = current_user_uuid()))
|
|
)
|
|
WITH CHECK (
|
|
user_id = current_user_uuid()
|
|
OR (org_id IS NOT NULL
|
|
AND org_id IN (SELECT org_id FROM org_members
|
|
WHERE user_id = current_user_uuid()))
|
|
);
|
|
|
|
COMMENT ON TABLE surface_bump_policy IS
|
|
'Per-(person|org), per-surface availability-bump automation policy (brief H §H1). cadence_minutes is the DESIRED cadence; the specialist clamps it up to the surface tier floor. active_start/active_end may wrap midnight. paused_until is vacation/kill-switch.';
|
|
|
|
COMMIT;
|