85 lines
6.5 KiB
SQL
85 lines
6.5 KiB
SQL
-- Square onboarding — database schema.
|
|
--
|
|
-- ALL Square state now lives in the database (no JSON files), persisted through
|
|
-- the host application's procedural db_* layer (../include/database.php) — the
|
|
-- same data layer the saving/ API uses. This folder's db_config.php is NO LONGER
|
|
-- used; the real credentials live in the host app.
|
|
--
|
|
-- Run once against the hotlunch database. Safe to re-run: ADD COLUMN IF NOT
|
|
-- EXISTS / CREATE TABLE IF NOT EXISTS are guarded (MySQL 8 / MariaDB 10.5+).
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 1) Per-school Square connection + metadata, all on the existing `schools` row.
|
|
-- Previously the tokens were here but the metadata (merchant_key, location,
|
|
-- currency, readiness, …) lived only in data/square_merchants.json. It is now
|
|
-- fully columnar so nothing is stored outside the database.
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
ALTER TABLE schools
|
|
ADD COLUMN IF NOT EXISTS square_merchant_id VARCHAR(64) NULL,
|
|
ADD COLUMN IF NOT EXISTS square_access_token TEXT NULL, -- AES-256-GCM ciphertext (enc:v1:...)
|
|
ADD COLUMN IF NOT EXISTS square_refresh_token TEXT NULL, -- AES-256-GCM ciphertext (enc:v1:...)
|
|
ADD COLUMN IF NOT EXISTS square_token_expires_at DATETIME NULL, -- access token expiry (ISO 8601 from Square)
|
|
ADD COLUMN IF NOT EXISTS square_connected_at DATETIME NULL,
|
|
ADD COLUMN IF NOT EXISTS square_merchant_key VARCHAR(64) NULL, -- public embed key (sqpk_…)
|
|
ADD COLUMN IF NOT EXISTS square_location_id VARCHAR(64) NULL, -- active location for Web Payments SDK
|
|
ADD COLUMN IF NOT EXISTS square_currency VARCHAR(3) NULL,
|
|
ADD COLUMN IF NOT EXISTS square_country VARCHAR(2) NULL,
|
|
ADD COLUMN IF NOT EXISTS square_merchant_name VARCHAR(255) NULL,
|
|
ADD COLUMN IF NOT EXISTS square_ready TINYINT(1) NOT NULL DEFAULT 0,
|
|
ADD COLUMN IF NOT EXISTS square_status_issues TEXT NULL, -- readiness issues, one per line
|
|
ADD COLUMN IF NOT EXISTS square_allowed_origins TEXT NULL, -- embed origin allowlist, one per line
|
|
ADD COLUMN IF NOT EXISTS square_status_checked_at BIGINT NULL, -- unix time of last readiness check
|
|
ADD COLUMN IF NOT EXISTS square_refreshed_at BIGINT NULL, -- unix time of last token refresh
|
|
ADD COLUMN IF NOT EXISTS square_refresh_failed_at BIGINT NULL,
|
|
ADD COLUMN IF NOT EXISTS square_refresh_error TEXT NULL,
|
|
ADD COLUMN IF NOT EXISTS square_authorization_revoked TINYINT(1) NOT NULL DEFAULT 0,
|
|
ADD COLUMN IF NOT EXISTS square_revoked_at BIGINT NULL,
|
|
ADD COLUMN IF NOT EXISTS square_seeded_for_testing TINYINT(1) NOT NULL DEFAULT 0;
|
|
|
|
-- Speed up lookups by merchant id (webhooks) and by public embed key (checkout).
|
|
CREATE INDEX idx_schools_square_merchant ON schools (square_merchant_id);
|
|
CREATE INDEX idx_schools_square_key ON schools (square_merchant_key);
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 2) Payment records (replaces data/square_payments.json). Keyed by the Square
|
|
-- payment id so a retried idempotent charge updates its row instead of
|
|
-- duplicating it. The school is referenced by its numeric id schools.record_num,
|
|
-- NOT by name — indexed for fast per-school lookups. (The column is named
|
|
-- `school_num` for historical reasons but stores schools.record_num.)
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS square_payments (
|
|
record_num INT NOT NULL AUTO_INCREMENT,
|
|
payment_id VARCHAR(64) NOT NULL,
|
|
school_num BIGINT NULL, -- stores schools.record_num
|
|
merchant_id VARCHAR(64) NULL,
|
|
amount_cents INT NOT NULL DEFAULT 0,
|
|
currency VARCHAR(3) NOT NULL DEFAULT 'CAD',
|
|
app_fee_cents INT NOT NULL DEFAULT 0,
|
|
status VARCHAR(32) NULL,
|
|
source_id_suffix VARCHAR(8) NULL,
|
|
recorded_at BIGINT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (record_num),
|
|
UNIQUE KEY uq_square_payment_id (payment_id),
|
|
KEY idx_square_payments_school_num (school_num)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 3) Pending OAuth onboarding attempts (replaces data/pending_onboarding.json).
|
|
-- Keyed by the anti-CSRF `state` token echoed back by Square on the callback.
|
|
-- The school is referenced by its UNIQUE numeric id schools.record_num (the
|
|
-- schools table's primary key — there is no `school_num` column on it), NOT by
|
|
-- name: school names are not unique across clients, so the callback must reattach
|
|
-- the connection to the exact school that began onboarding. The column below is
|
|
-- named `school_num` for historical reasons but stores schools.record_num.
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS square_pending_onboarding (
|
|
state VARCHAR(64) NOT NULL,
|
|
school_num BIGINT NOT NULL, -- stores schools.record_num
|
|
status VARCHAR(32) NOT NULL DEFAULT 'pending', -- pending|connected|declined|token_exchange_failed
|
|
merchant_id VARCHAR(64) NULL,
|
|
created_at BIGINT NOT NULL DEFAULT 0,
|
|
connected_at BIGINT NULL,
|
|
last_callback BIGINT NULL,
|
|
PRIMARY KEY (state)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|