Products

Data license: CC-BY-4.0 · Data source: bankproducts.info

0 rows where source_document_id = 198 sorted by bank_id descending

✎ View and edit SQL

This data as json

0 records

CREATE TABLE product (
    id                  TEXT PRIMARY KEY,  -- zkb:privatkonto, ubs:key4_banking
    bank_id             TEXT NOT NULL REFERENCES bank(id),
    category            TEXT NOT NULL,     -- current_account, savings_account, pillar_3a, vested_benefits,
                                           -- debit_card, credit_card, prepaid_card, package, ebanking,
                                           -- mortgage, wealth_management, term_deposit, securities_trading,
                                           -- fiduciary_deposit, safe_deposit, guarantee, leasing,
                                           -- financial_planning, personal_advisory, loyalty_program,
                                           -- digital_vault, fx_subscription, robo_advisory, self_directed_trading,
                                           -- metal_account, deposit_account, check
    name                TEXT NOT NULL,     -- bank's own product name
    currency            TEXT DEFAULT 'CHF',
    client_type         TEXT DEFAULT 'private',  -- private, business, institutional
    target_group        TEXT,              -- standard, young, children, students, seniors, eco, gift, rental_deposit, member
    description         TEXT,
    valid_from          TEXT,
    source_document_id  INTEGER REFERENCES source_document(id),
    bpg                 CHAR(12)        -- opaque global id, BPG003xxxxxx; populated post-load by bpg_assignments.sql
        CHECK (bpg IS NULL OR bpg GLOB 'BPG003[0-9A-HJKMNP-TV-Z][0-9A-HJKMNP-TV-Z][0-9A-HJKMNP-TV-Z][0-9A-HJKMNP-TV-Z][0-9A-HJKMNP-TV-Z][0-9A-HJKMNP-TV-Z]'),
    access_tier         TEXT NOT NULL DEFAULT 'free'  -- freemium gating: free public, pro/business paid
        CHECK (access_tier IN ('free', 'pro', 'business')),
    is_tailored         INTEGER             -- v2.6 (atom #278): mirrors CDS-AU (Australian Open Banking) isTailored.
                                            --   0 = pricing publicly published in a schedule (machine-readable, comparable);
                                            --   1 = negotiated per client (UHNW classical private banking, business loans);
                                            --   NULL = unknown / not yet classified (default for new loads).
                                            -- Distinguishes "we haven't crawled it" (NULL) from "bank deliberately
                                            -- doesn't publish because it's relationship-priced" (1) — three states
                                            -- that previously collapsed into "fee.amount IS NULL".
        CHECK (is_tailored IS NULL OR is_tailored IN (0, 1)),
    notice_period_days  INTEGER             -- v2.7 (atom #307): normalised withdrawal notice in days.
                                            --   0/NULL = on-demand; X = days notice. Conversion: months×30,
                                            --   years×365, days verbatim. Backfilled from
                                            --   product_feature.notice_period free-text by
                                            --   db/notice_period_backfill.sql.
        CHECK (notice_period_days IS NULL OR notice_period_days >= 0),
    iso_account_type    CHAR(4)             -- v2.8 (atom #308): ISO 20022 ExternalCashAccountType1Code.
                                            --   4-letter code from the official ISO enum: CACC (Current),
                                            --   SVGS (Savings), LOAN, SACC (Special), MOMA (Money Market),
                                            --   CHAR (Charges), CASH, etc.
                                            --   NULL for categories outside ISO scope: cards
                                            --   (debit/credit/prepaid), mandates (wealth_management,
                                            --   securities_trading, fiduciary_deposit), CH-specific
                                            --   (pillar_3a, vested_benefits, pension_account).
                                            --   Mapped from `category` by db/iso_account_type_backfill.sql.
                                            --   Backfill is conservative — when in doubt, leave NULL rather
                                            --   than force-fit a code that misrepresents the product.
        CHECK (iso_account_type IS NULL OR iso_account_type GLOB '[A-Z][A-Z][A-Z][A-Z]')
);
CREATE INDEX idx_product_bank ON product(bank_id);
CREATE INDEX idx_product_category ON product(category);
CREATE INDEX idx_product_lookup ON product(bank_id, category, client_type, target_group);
CREATE UNIQUE INDEX idx_product_bpg ON product(bpg) WHERE bpg IS NOT NULL;
CREATE INDEX idx_product_access_tier ON product(access_tier);
CREATE INDEX idx_product_is_tailored ON product(is_tailored) WHERE is_tailored IS NOT NULL;
CREATE INDEX idx_product_iso_account_type ON product(iso_account_type) WHERE iso_account_type IS NOT NULL;
CREATE INDEX idx_product_notice_period ON product(notice_period_days) WHERE notice_period_days IS NOT NULL;