-- File: migrations/00000000000000_diesel_initial_setup/up.sql -- This file was automatically created by Diesel to setup helper functions -- and other internal bookkeeping. This file is safe to edit, any future -- changes will be added to existing projects as new migrations. -- Sets up a trigger for the given table to automatically set a column called -- `updated_at` whenever the row is modified (unless `updated_at` was included -- in the modified columns) -- -- # Example -- -- ```sql -- CREATE TABLE users (id SERIAL PRIMARY KEY, updated_at TIMESTAMP NOT NULL DEFAULT NOW()); -- -- SELECT diesel_manage_updated_at('users'); -- ``` CREATE OR REPLACE FUNCTION diesel_manage_updated_at(_tbl regclass) RETURNS VOID AS $$ BEGIN EXECUTE format('CREATE TRIGGER set_updated_at BEFORE UPDATE ON %s FOR EACH ROW EXECUTE PROCEDURE diesel_set_updated_at()', _tbl); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION diesel_set_updated_at() RETURNS trigger AS $$ BEGIN IF ( NEW IS DISTINCT FROM OLD AND NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at ) THEN NEW.updated_at := current_timestamp; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; -- File: migrations/2022-09-29-084920_create_initial_tables/up.sql -- Types CREATE TYPE "AttemptStatus" AS ENUM ( 'started', 'authentication_failed', 'juspay_declined', 'pending_vbv', 'vbv_successful', 'authorized', 'authorization_failed', 'charged', 'authorizing', 'cod_initiated', 'voided', 'void_initiated', 'capture_initiated', 'capture_failed', 'void_failed', 'auto_refunded', 'partial_charged', 'pending', 'failure', 'payment_method_awaited', 'confirmation_awaited' ); CREATE TYPE "AuthenticationType" AS ENUM ('three_ds', 'no_three_ds'); CREATE TYPE "CaptureMethod" AS ENUM ('automatic', 'manual', 'scheduled'); CREATE TYPE "ConnectorType" AS ENUM ( 'payment_processor', 'payment_vas', 'fin_operations', 'fiz_operations', 'networks', 'banking_entities', 'non_banking_finance' ); CREATE TYPE "Currency" AS ENUM ( 'AED', 'ALL', 'AMD', 'ARS', 'AUD', 'AWG', 'AZN', 'BBD', 'BDT', 'BHD', 'BMD', 'BND', 'BOB', 'BRL', 'BSD', 'BWP', 'BZD', 'CAD', 'CHF', 'CNY', 'COP', 'CRC', 'CUP', 'CZK', 'DKK', 'DOP', 'DZD', 'EGP', 'ETB', 'EUR', 'FJD', 'GBP', 'GHS', 'GIP', 'GMD', 'GTQ', 'GYD', 'HKD', 'HNL', 'HRK', 'HTG', 'HUF', 'IDR', 'ILS', 'INR', 'JMD', 'JOD', 'JPY', 'KES', 'KGS', 'KHR', 'KRW', 'KWD', 'KYD', 'KZT', 'LAK', 'LBP', 'LKR', 'LRD', 'LSL', 'MAD', 'MDL', 'MKD', 'MMK', 'MNT', 'MOP', 'MUR', 'MVR', 'MWK', 'MXN', 'MYR', 'NAD', 'NGN', 'NIO', 'NOK', 'NPR', 'NZD', 'OMR', 'PEN', 'PGK', 'PHP', 'PKR', 'PLN', 'QAR', 'RUB', 'SAR', 'SCR', 'SEK', 'SGD', 'SLL', 'SOS', 'SSP', 'SVC', 'SZL', 'THB', 'TTD', 'TWD', 'TZS', 'USD', 'UYU', 'UZS', 'YER', 'ZAR' ); CREATE TYPE "EventClass" AS ENUM ('payments'); CREATE TYPE "EventObjectType" AS ENUM ('payment_details'); CREATE TYPE "EventType" AS ENUM ('payment_succeeded'); CREATE TYPE "FutureUsage" AS ENUM ('on_session', 'off_session'); CREATE TYPE "IntentStatus" AS ENUM ( 'succeeded', 'failed', 'processing', 'requires_customer_action', 'requires_payment_method', 'requires_confirmation' ); CREATE TYPE "MandateStatus" AS ENUM ( 'active', 'inactive', 'pending', 'revoked' ); CREATE TYPE "MandateType" AS ENUM ('single_use', 'multi_use'); CREATE TYPE "PaymentFlow" AS ENUM ( 'vsc', 'emi', 'otp', 'upi_intent', 'upi_collect', 'upi_scan_and_pay', 'sdk' ); CREATE TYPE "PaymentMethodIssuerCode" AS ENUM ( 'jp_hdfc', 'jp_icici', 'jp_googlepay', 'jp_applepay', 'jp_phonepe', 'jp_wechat', 'jp_sofort', 'jp_giropay', 'jp_sepa', 'jp_bacs' ); CREATE TYPE "PaymentMethodSubType" AS ENUM ( 'credit', 'debit', 'upi_intent', 'upi_collect', 'credit_card_installments', 'pay_later_installments' ); CREATE TYPE "PaymentMethodType" AS ENUM ( 'card', 'bank_transfer', 'netbanking', 'upi', 'open_banking', 'consumer_finance', 'wallet', 'payment_container', 'bank_debit', 'pay_later' ); CREATE TYPE "ProcessTrackerStatus" AS ENUM ( 'processing', 'new', 'pending', 'process_started', 'finish' ); CREATE TYPE "RefundStatus" AS ENUM ( 'failure', 'manual_review', 'pending', 'success', 'transaction_failure' ); CREATE TYPE "RefundType" AS ENUM ( 'instant_refund', 'regular_refund', 'retry_refund' ); CREATE TYPE "RoutingAlgorithm" AS ENUM ( 'round_robin', 'max_conversion', 'min_cost', 'custom' ); -- Tables CREATE TABLE address ( id SERIAL, address_id VARCHAR(255) PRIMARY KEY, city VARCHAR(255), country VARCHAR(255), line1 VARCHAR(255), line2 VARCHAR(255), line3 VARCHAR(255), state VARCHAR(255), zip VARCHAR(255), first_name VARCHAR(255), last_name VARCHAR(255), phone_number VARCHAR(255), country_code VARCHAR(255), created_at TIMESTAMP NOT NULL DEFAULT now()::TIMESTAMP, modified_at TIMESTAMP NOT NULL DEFAULT now()::TIMESTAMP ); CREATE TABLE configs ( id SERIAL, key VARCHAR(255) NOT NULL, config TEXT NOT NULL, PRIMARY KEY (key) ); CREATE TABLE customers ( id SERIAL, customer_id VARCHAR(255) NOT NULL, merchant_id VARCHAR(255) NOT NULL, NAME VARCHAR(255), email VARCHAR(255), phone VARCHAR(255), phone_country_code VARCHAR(255), description VARCHAR(255), address JSON, created_at TIMESTAMP NOT NULL DEFAULT now()::TIMESTAMP, metadata JSON, PRIMARY KEY (customer_id, merchant_id) ); CREATE TABLE events ( id SERIAL PRIMARY KEY, event_id VARCHAR(255) NOT NULL, event_type "EventType" NOT NULL, event_class "EventClass" NOT NULL, is_webhook_notified BOOLEAN NOT NULL DEFAULT FALSE, intent_reference_id VARCHAR(255), primary_object_id VARCHAR(255) NOT NULL, primary_object_type "EventObjectType" NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT now()::TIMESTAMP ); CREATE TABLE locker_mock_up ( id SERIAL PRIMARY KEY, card_id VARCHAR(255) NOT NULL, external_id VARCHAR(255) NOT NULL, card_fingerprint VARCHAR(255) NOT NULL, card_global_fingerprint VARCHAR(255) NOT NULL, merchant_id VARCHAR(255) NOT NULL, card_number VARCHAR(255) NOT NULL, card_exp_year VARCHAR(255) NOT NULL, card_exp_month VARCHAR(255) NOT NULL, name_on_card VARCHAR(255), nickname VARCHAR(255), customer_id VARCHAR(255), duplicate BOOLEAN ); CREATE TABLE mandate ( id SERIAL PRIMARY KEY, mandate_id VARCHAR(255) NOT NULL, customer_id VARCHAR(255) NOT NULL, merchant_id VARCHAR(255) NOT NULL, payment_method_id VARCHAR(255) NOT NULL, mandate_status "MandateStatus" NOT NULL, mandate_type "MandateType" NOT NULL, customer_accepted_at TIMESTAMP, customer_ip_address VARCHAR(255), customer_user_agent VARCHAR(255), network_transaction_id VARCHAR(255), previous_transaction_id VARCHAR(255), created_at TIMESTAMP NOT NULL DEFAULT now()::TIMESTAMP ); CREATE TABLE merchant_account ( id SERIAL PRIMARY KEY, merchant_id VARCHAR(255) NOT NULL, api_key VARCHAR(255), return_url VARCHAR(255), enable_payment_response_hash BOOLEAN NOT NULL DEFAULT FALSE, payment_response_hash_key VARCHAR(255) DEFAULT NULL, redirect_to_merchant_with_http_post BOOLEAN NOT NULL DEFAULT FALSE, merchant_name VARCHAR(255), merchant_details JSON, webhook_details JSON, routing_algorithm "RoutingAlgorithm", custom_routing_rules JSON, sub_merchants_enabled BOOLEAN DEFAULT FALSE, parent_merchant_id VARCHAR(255), publishable_key VARCHAR(255) ); CREATE TABLE merchant_connector_account ( id SERIAL PRIMARY KEY, merchant_id VARCHAR(255) NOT NULL, connector_name VARCHAR(255) NOT NULL, connector_account_details JSON NOT NULL, test_mode BOOLEAN, disabled BOOLEAN, merchant_connector_id SERIAL NOT NULL, payment_methods_enabled JSON [ ], connector_type "ConnectorType" NOT NULL DEFAULT 'payment_processor'::"ConnectorType" ); CREATE TABLE payment_attempt ( id SERIAL PRIMARY KEY, payment_id VARCHAR(255) NOT NULL, merchant_id VARCHAR(255) NOT NULL, txn_id VARCHAR(255) NOT NULL, status "AttemptStatus" NOT NULL, amount INTEGER NOT NULL, currency "Currency", save_to_locker BOOLEAN, connector VARCHAR(255) NOT NULL, error_message TEXT, offer_amount INTEGER, surcharge_amount INTEGER, tax_amount INTEGER, payment_method_id VARCHAR(255), payment_method "PaymentMethodType", payment_flow "PaymentFlow", redirect BOOLEAN, connector_transaction_id VARCHAR(255), capture_method "CaptureMethod", capture_on TIMESTAMP, confirm BOOLEAN NOT NULL, authentication_type "AuthenticationType", created_at TIMESTAMP NOT NULL, modified_at TIMESTAMP NOT NULL, last_synced TIMESTAMP ); CREATE TABLE payment_intent ( id SERIAL PRIMARY KEY, payment_id VARCHAR(255) NOT NULL, merchant_id VARCHAR(255) NOT NULL, status "IntentStatus" NOT NULL, amount INTEGER NOT NULL, currency "Currency", amount_captured INTEGER, customer_id VARCHAR(255), description VARCHAR(255), return_url VARCHAR(255), metadata JSONB DEFAULT '{}'::JSONB, connector_id VARCHAR(255), shipping_address_id VARCHAR(255), billing_address_id VARCHAR(255), statement_descriptor_name VARCHAR(255), statement_descriptor_suffix VARCHAR(255), created_at TIMESTAMP NOT NULL, modified_at TIMESTAMP NOT NULL, last_synced TIMESTAMP, setup_future_usage "FutureUsage", off_session BOOLEAN, client_secret VARCHAR(255) ); CREATE TABLE payment_methods ( id SERIAL PRIMARY KEY, customer_id VARCHAR(255) NOT NULL, merchant_id VARCHAR(255) NOT NULL, payment_method_id VARCHAR(255) NOT NULL, accepted_currency "Currency" [ ], scheme VARCHAR(255), token VARCHAR(255), cardholder_name VARCHAR(255), issuer_name VARCHAR(255), issuer_country VARCHAR(255), payer_country TEXT [ ], is_stored BOOLEAN, swift_code VARCHAR(255), direct_debit_token VARCHAR(255), network_transaction_id VARCHAR(255), created_at TIMESTAMP NOT NULL, last_modified TIMESTAMP NOT NULL, payment_method "PaymentMethodType" NOT NULL, payment_method_type "PaymentMethodSubType", payment_method_issuer VARCHAR(255), payment_method_issuer_code "PaymentMethodIssuerCode" ); CREATE TABLE process_tracker ( id VARCHAR(127) PRIMARY KEY, NAME VARCHAR(255), tag TEXT [ ] NOT NULL DEFAULT '{}'::TEXT [ ], runner VARCHAR(255), retry_count INTEGER NOT NULL, schedule_time TIMESTAMP, rule VARCHAR(255) NOT NULL, tracking_data JSON NOT NULL, business_status VARCHAR(255) NOT NULL, status "ProcessTrackerStatus" NOT NULL, event TEXT [ ] NOT NULL DEFAULT '{}'::TEXT [ ], created_at TIMESTAMP NOT NULL DEFAULT now(), updated_at TIMESTAMP NOT NULL DEFAULT now() ); CREATE TABLE refund ( id SERIAL PRIMARY KEY, internal_reference_id VARCHAR(255) NOT NULL, refund_id VARCHAR(255) NOT NULL, payment_id VARCHAR(255) NOT NULL, merchant_id VARCHAR(255) NOT NULL, transaction_id VARCHAR(255) NOT NULL, connector VARCHAR(255) NOT NULL, pg_refund_id VARCHAR(255), external_reference_id VARCHAR(255), refund_type "RefundType" NOT NULL, total_amount INTEGER NOT NULL, currency "Currency" NOT NULL, refund_amount INTEGER NOT NULL, refund_status "RefundStatus" NOT NULL, sent_to_gateway BOOLEAN NOT NULL DEFAULT FALSE, refund_error_message TEXT, metadata JSON, refund_arn VARCHAR(255), created_at TIMESTAMP NOT NULL, modified_at TIMESTAMP NOT NULL, description VARCHAR(255) ); CREATE TABLE temp_card ( id SERIAL PRIMARY KEY, date_created TIMESTAMP NOT NULL, txn_id VARCHAR(255), card_info JSON ); -- Indices CREATE INDEX customers_created_at_index ON customers (created_at); CREATE UNIQUE INDEX merchant_account_api_key_index ON merchant_account (api_key); CREATE UNIQUE INDEX merchant_account_merchant_id_index ON merchant_account (merchant_id); CREATE UNIQUE INDEX merchant_account_publishable_key_index ON merchant_account (publishable_key); CREATE INDEX merchant_connector_account_connector_type_index ON merchant_connector_account (connector_type); CREATE INDEX merchant_connector_account_merchant_id_index ON merchant_connector_account (merchant_id); CREATE UNIQUE INDEX payment_attempt_payment_id_merchant_id_index ON payment_attempt (payment_id, merchant_id); CREATE UNIQUE INDEX payment_intent_payment_id_merchant_id_index ON payment_intent (payment_id, merchant_id); CREATE INDEX payment_methods_created_at_index ON payment_methods (created_at); CREATE INDEX payment_methods_customer_id_index ON payment_methods (customer_id); CREATE INDEX payment_methods_last_modified_index ON payment_methods (last_modified); CREATE INDEX payment_methods_payment_method_id_index ON payment_methods (payment_method_id); CREATE INDEX refund_internal_reference_id_index ON refund (internal_reference_id); CREATE INDEX refund_payment_id_merchant_id_index ON refund (payment_id, merchant_id); CREATE INDEX refund_refund_id_index ON refund (refund_id); CREATE UNIQUE INDEX refund_refund_id_merchant_id_index ON refund (refund_id, merchant_id); CREATE INDEX temp_card_txn_id_index ON temp_card (txn_id); -- File: migrations/2022-09-29-093314_create_seed_data/up.sql INSERT INTO merchant_account ( merchant_id, api_key, merchant_name, merchant_details, custom_routing_rules, publishable_key ) VALUES ( 'juspay_merchant', 'MySecretApiKey', 'Juspay Merchant', '{ "primary_email": "merchant@juspay.in" }', '[ { "connectors_pecking_order": [ "stripe" ] } ]', 'pk_MyPublicApiKey' ); INSERT INTO merchant_connector_account ( merchant_id, connector_name, connector_account_details ) VALUES ( 'juspay_merchant', 'stripe', '{ "auth_type": "HeaderKey", "api_key": "Basic MyStripeApiKey" }' ); -- File: migrations/2022-10-20-100628_add_cancellation_reason/up.sql ALTER TABLE payment_attempt ADD COLUMN cancellation_reason VARCHAR(255); -- File: migrations/2022-10-26-101016_update_payment_attempt_status_intent_status/up.sql -- Your SQL goes here ALTER TABLE payment_attempt ADD IF NOT EXISTS amount_to_capture INTEGER; ALTER TYPE "CaptureMethod" ADD VALUE 'manual_multiple' AFTER 'manual'; ALTER TYPE "IntentStatus" ADD VALUE 'requires_capture'; -- File: migrations/2022-11-03-130214_create_connector_response_table/up.sql -- Your SQL goes here CREATE TABLE connector_response ( id SERIAL PRIMARY KEY, payment_id VARCHAR(255) NOT NULL, merchant_id VARCHAR(255) NOT NULL, txn_id VARCHAR(255) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT now()::TIMESTAMP, modified_at TIMESTAMP NOT NULL DEFAULT now()::TIMESTAMP, connector_name VARCHAR(32) NOT NULL, connector_transaction_id VARCHAR(255), authentication_data JSON, encoded_data TEXT ); CREATE UNIQUE INDEX connector_response_id_index ON connector_response (payment_id, merchant_id, txn_id); -- File: migrations/2022-11-08-101705_add_cancel_to_payment_intent_status/up.sql -- Your SQL goes here ALTER TYPE "IntentStatus" ADD VALUE 'cancelled' after 'failed'; -- File: migrations/2022-11-21-133803_add_mandate_id_in_payment_attempt/up.sql -- Your SQL goes here ALTER TABLE payment_attempt ADD IF NOT EXISTS mandate_id VARCHAR(255); -- File: migrations/2022-11-24-095709_add_browser_info_to_payment_attempt/up.sql ALTER TABLE payment_attempt ADD COLUMN browser_info JSONB DEFAULT NULL; -- File: migrations/2022-11-25-121143_add_paypal_pmt/up.sql -- Your SQL goes here ALTER TYPE "PaymentMethodType" ADD VALUE 'paypal' after 'pay_later'; -- File: migrations/2022-11-30-084736_update-index-in-mca/up.sql CREATE UNIQUE INDEX merchant_connector_account_merchant_id_connector_name_index ON merchant_connector_account (merchant_id, connector_name); -- File: migrations/2022-12-05-090521_single_use_mandate_fields/up.sql -- Your SQL goes here ALTER TABLE mandate ADD IF NOT EXISTS single_use_amount INTEGER DEFAULT NULL, ADD IF NOT EXISTS single_use_currency "Currency" DEFAULT NULL; -- File: migrations/2022-12-07-055441_add_use_kv_to_merchant_account/up.sql -- Your SQL goes here CREATE TYPE "MerchantStorageScheme" AS ENUM ( 'postgres_only', 'redis_kv' ); ALTER TABLE merchant_account ADD COLUMN storage_scheme "MerchantStorageScheme" NOT NULL DEFAULT 'postgres_only'; -- File: migrations/2022-12-07-133736_make_connector_field_optional/up.sql ALTER TABLE payment_attempt ALTER COLUMN connector DROP NOT NULL; ALTER TABLE connector_response ALTER COLUMN connector_name DROP NOT NULL; -- File: migrations/2022-12-09-102635_mandate-connector-and-amount/up.sql -- Your SQL goes here ALTER TABLE mandate RENAME COLUMN single_use_amount TO mandate_amount; ALTER TABLE mandate RENAME COLUMN single_use_currency TO mandate_currency; ALTER TABLE mandate ADD IF NOT EXISTS amount_captured INTEGER DEFAULT NULL, ADD IF NOT EXISTS connector VARCHAR(255) NOT NULL DEFAULT 'dummy', ADD IF NOT EXISTS connector_mandate_id VARCHAR(255) DEFAULT NULL; -- File: migrations/2022-12-10-123613_update_address_and_customer/up.sql -- Your SQL goes here ALTER TABLE address ADD COLUMN customer_id VARCHAR(255) NOT NULL, ADD COLUMN merchant_id VARCHAR(255) NOT NULL; CREATE INDEX address_customer_id_merchant_id_index ON address (customer_id, merchant_id); ALTER TABLE customers DROP COLUMN address; -- File: migrations/2022-12-11-190755_update_mock_up/up.sql -- Your SQL goes here ALTER TABLE locker_mock_up ADD COLUMN card_cvc VARCHAR(8); -- File: migrations/2022-12-12-132936_reverse_lookup/up.sql CREATE TABLE reverse_lookup ( lookup_id VARCHAR(255) NOT NULL PRIMARY KEY, sk_id VARCHAR(50) NOT NULL, pk_id VARCHAR(255) NOT NULL, source VARCHAR(30) NOT NULL ); CREATE INDEX lookup_id_index ON reverse_lookup (lookup_id); -- File: migrations/2022-12-13-170152_add_connector_metadata/up.sql ALTER TABLE merchant_connector_account ADD COLUMN metadata JSONB DEFAULT NULL; -- File: migrations/2022-12-14-074547_error-code-in-payment_attempt/up.sql -- Your SQL goes here ALTER TABLE payment_attempt ADD IF NOT EXISTS error_code VARCHAR(255) DEFAULT NULL; -- File: migrations/2022-12-14-090419_add_payment_token_in_payment_attempt/up.sql ALTER TABLE payment_attempt ADD COLUMN payment_token VARCHAR(255); -- File: migrations/2022-12-14-092540_i32_to_i64/up.sql -- Your SQL goes here ALTER TABLE mandate ALTER COLUMN mandate_amount TYPE bigint, ALTER COLUMN amount_captured TYPE bigint; ALTER TABLE payment_attempt ALTER COLUMN amount TYPE bigint, ALTER COLUMN offer_amount TYPE bigint, ALTER COLUMN surcharge_amount TYPE bigint, ALTER COLUMN tax_amount TYPE bigint, ALTER COLUMN amount_to_capture TYPE bigint; ALTER TABLE payment_intent ALTER COLUMN amount TYPE bigint, ALTER COLUMN amount_captured TYPE bigint; ALTER TABLE refund ALTER COLUMN total_amount TYPE bigint, ALTER COLUMN refund_amount TYPE bigint; -- File: migrations/2022-12-14-162701_update_payment_method/up.sql -- Your SQL goes here ALTER TABLE payment_methods ADD COLUMN metadata JSON; -- File: migrations/2022-12-19-085322_rename_txn_id_to_attempt_id/up.sql ALTER TABLE payment_attempt RENAME COLUMN txn_id to attempt_id; -- File: migrations/2022-12-19-085739_add_attempt_id_to_refund/up.sql ALTER TABLE refund ADD COLUMN attempt_id VARCHAR(64) NOT NULL; -- File: migrations/2022-12-20-065945_reduce_size_of_varchar_columns/up.sql ALTER TABLE address ALTER COLUMN address_id TYPE VARCHAR(64), ALTER COLUMN city TYPE VARCHAR(128), ALTER COLUMN country TYPE VARCHAR(64), ALTER COLUMN state TYPE VARCHAR(128), ALTER COLUMN zip TYPE VARCHAR(16), ALTER COLUMN phone_number TYPE VARCHAR(32), ALTER COLUMN country_code TYPE VARCHAR(8), ALTER COLUMN customer_id TYPE VARCHAR(64), ALTER COLUMN merchant_id TYPE VARCHAR(64); ALTER TABLE connector_response RENAME COLUMN txn_id TO attempt_id; ALTER TABLE connector_response ALTER COLUMN payment_id TYPE VARCHAR(64), ALTER COLUMN merchant_id TYPE VARCHAR(64), ALTER COLUMN attempt_id TYPE VARCHAR(64), ALTER COLUMN connector_name TYPE VARCHAR(64), ALTER COLUMN connector_transaction_id TYPE VARCHAR(128); ALTER TABLE customers ALTER COLUMN customer_id TYPE VARCHAR(64), ALTER COLUMN merchant_id TYPE VARCHAR(64), ALTER COLUMN phone TYPE VARCHAR(32), ALTER COLUMN phone_country_code TYPE VARCHAR(8); ALTER TABLE events ALTER COLUMN event_id TYPE VARCHAR(64), ALTER COLUMN intent_reference_id TYPE VARCHAR(64), ALTER COLUMN primary_object_id TYPE VARCHAR(64); ALTER TABLE mandate RENAME COLUMN previous_transaction_id to previous_attempt_id; ALTER TABLE mandate ALTER COLUMN mandate_id TYPE VARCHAR(64), ALTER COLUMN customer_id TYPE VARCHAR(64), ALTER COLUMN merchant_id TYPE VARCHAR(64), ALTER COLUMN payment_method_id TYPE VARCHAR(64), ALTER COLUMN customer_ip_address TYPE VARCHAR(64), ALTER COLUMN network_transaction_id TYPE VARCHAR(128), ALTER COLUMN previous_attempt_id TYPE VARCHAR(64), ALTER COLUMN connector TYPE VARCHAR(64), ALTER COLUMN connector_mandate_id TYPE VARCHAR(128); ALTER TABLE merchant_account ALTER COLUMN merchant_id TYPE VARCHAR(64), ALTER COLUMN api_key TYPE VARCHAR(128), ALTER COLUMN merchant_name TYPE VARCHAR(128), ALTER COLUMN parent_merchant_id TYPE VARCHAR(64), ALTER COLUMN publishable_key TYPE VARCHAR(128); ALTER TABLE merchant_connector_account ALTER COLUMN merchant_id TYPE VARCHAR(64), ALTER COLUMN connector_name TYPE VARCHAR(64); ALTER TABLE payment_attempt ALTER COLUMN payment_id TYPE VARCHAR(64), ALTER COLUMN merchant_id TYPE VARCHAR(64), ALTER COLUMN attempt_id TYPE VARCHAR(64), ALTER COLUMN connector TYPE VARCHAR(64), ALTER COLUMN payment_method_id TYPE VARCHAR(64), ALTER COLUMN connector_transaction_id TYPE VARCHAR(128), ALTER COLUMN mandate_id TYPE VARCHAR(64), ALTER COLUMN payment_token TYPE VARCHAR(128); ALTER TABLE payment_intent ALTER COLUMN payment_id TYPE VARCHAR(64), ALTER COLUMN merchant_id TYPE VARCHAR(64), ALTER COLUMN customer_id TYPE VARCHAR(64), ALTER COLUMN connector_id TYPE VARCHAR(64), ALTER COLUMN shipping_address_id TYPE VARCHAR(64), ALTER COLUMN billing_address_id TYPE VARCHAR(64), ALTER COLUMN client_secret TYPE VARCHAR(128); ALTER TABLE payment_methods DROP COLUMN network_transaction_id; ALTER TABLE payment_methods ALTER COLUMN customer_id TYPE VARCHAR(64), ALTER COLUMN merchant_id TYPE VARCHAR(64), ALTER COLUMN payment_method_id TYPE VARCHAR(64), ALTER COLUMN scheme TYPE VARCHAR(32), ALTER COLUMN token TYPE VARCHAR(128), ALTER COLUMN issuer_name TYPE VARCHAR(64), ALTER COLUMN issuer_country TYPE VARCHAR(64), ALTER COLUMN swift_code TYPE VARCHAR(32), ALTER COLUMN direct_debit_token TYPE VARCHAR(128), ALTER COLUMN payment_method_issuer TYPE VARCHAR(128); ALTER TABLE process_tracker ALTER COLUMN name TYPE VARCHAR(64), ALTER COLUMN runner TYPE VARCHAR(64); ALTER TABLE refund RENAME COLUMN transaction_id to connector_transaction_id; ALTER TABLE refund RENAME COLUMN pg_refund_id to connector_refund_id; ALTER TABLE refund ALTER COLUMN internal_reference_id TYPE VARCHAR(64), ALTER COLUMN refund_id TYPE VARCHAR(64), ALTER COLUMN payment_id TYPE VARCHAR(64), ALTER COLUMN merchant_id TYPE VARCHAR(64), ALTER COLUMN connector_transaction_id TYPE VARCHAR(128), ALTER COLUMN connector TYPE VARCHAR(64), ALTER COLUMN connector_refund_id TYPE VARCHAR(128), ALTER COLUMN external_reference_id TYPE VARCHAR(64), ALTER COLUMN refund_arn TYPE VARCHAR(128); ALTER TABLE reverse_lookup ALTER COLUMN lookup_id TYPE VARCHAR(128), ALTER COLUMN sk_id TYPE VARCHAR(128), ALTER COLUMN pk_id TYPE VARCHAR(128), ALTER COLUMN source TYPE VARCHAR(128); -- File: migrations/2022-12-21-071825_add_refund_reason/up.sql ALTER TABLE REFUND ADD COLUMN refund_reason VARCHAR(255) DEFAULT NULL; -- File: migrations/2022-12-21-124904_remove_metadata_default_as_null/up.sql ALTER TABLE payment_intent ALTER COLUMN metadata DROP DEFAULT; -- File: migrations/2022-12-22-091431_attempt_status_rename/up.sql ALTER TYPE "AttemptStatus" RENAME VALUE 'juspay_declined' TO 'router_declined'; ALTER TYPE "AttemptStatus" RENAME VALUE 'pending_vbv' TO 'authentication_successful'; ALTER TYPE "AttemptStatus" RENAME VALUE 'vbv_successful' TO 'authentication_pending'; -- File: migrations/2022-12-27-172643_update_locker_mock_up/up.sql -- Your SQL goes here ALTER TABLE locker_mock_up ADD COLUMN payment_method_id VARCHAR(64); -- File: migrations/2023-01-03-122401_update_merchant_account/up.sql -- Your SQL goes here ALTER TABLE merchant_account ADD COLUMN locker_id VARCHAR(64); -- File: migrations/2023-01-10-035412_connector-metadata-payment-attempt/up.sql -- Your SQL goes here ALTER TABLE payment_attempt ADD COLUMN connector_metadata JSONB DEFAULT NULL; -- File: migrations/2023-01-11-134448_add_metadata_to_merchant_account/up.sql -- Your SQL goes here ALTER TABLE merchant_account ADD COLUMN metadata JSONB DEFAULT NULL; -- File: migrations/2023-01-12-084710_update_merchant_routing_algorithm/up.sql -- Your SQL goes here ALTER TABLE merchant_account DROP COLUMN routing_algorithm; ALTER TABLE merchant_account DROP COLUMN custom_routing_rules; ALTER TABLE merchant_account ADD COLUMN routing_algorithm JSON; DROP TYPE "RoutingAlgorithm"; -- File: migrations/2023-01-12-140107_drop_temp_card/up.sql DROP TABLE temp_card; -- File: migrations/2023-01-19-122511_add_refund_error_code/up.sql ALTER TABLE refund ADD IF NOT EXISTS refund_error_code TEXT DEFAULT NULL; -- File: migrations/2023-01-20-113235_add_attempt_id_to_payment_intent/up.sql -- Your SQL goes here ALTER TABLE payment_intent ADD COLUMN active_attempt_id VARCHAR(64) NOT NULL DEFAULT 'xxx'; UPDATE payment_intent SET active_attempt_id = payment_attempt.attempt_id from payment_attempt where payment_intent.active_attempt_id = payment_attempt.payment_id; CREATE UNIQUE INDEX payment_attempt_payment_id_merchant_id_attempt_id_index ON payment_attempt (payment_id, merchant_id, attempt_id); -- Because payment_attempt table can have rows with same payment_id and merchant_id, this index is dropped. DROP index payment_attempt_payment_id_merchant_id_index; CREATE INDEX payment_attempt_payment_id_merchant_id_index ON payment_attempt (payment_id, merchant_id); -- File: migrations/2023-02-01-135102_create_api_keys_table/up.sql CREATE TABLE api_keys ( key_id VARCHAR(64) NOT NULL PRIMARY KEY, merchant_id VARCHAR(64) NOT NULL, NAME VARCHAR(64) NOT NULL, description VARCHAR(256) DEFAULT NULL, hash_key VARCHAR(64) NOT NULL, hashed_api_key VARCHAR(128) NOT NULL, prefix VARCHAR(16) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT now()::TIMESTAMP, expires_at TIMESTAMP DEFAULT NULL, last_used TIMESTAMP DEFAULT NULL ); -- File: migrations/2023-02-02-055700_add_payment_issuer_and_experience_in_payment_attempt/up.sql -- Your SQL goes here ALTER TABLE payment_attempt ADD COLUMN IF NOT EXISTS payment_issuer VARCHAR(50); ALTER TABLE payment_attempt ADD COLUMN IF NOT EXISTS payment_experience VARCHAR(50); -- File: migrations/2023-02-02-062215_remove_redirect_and_payment_flow_from_payment_attempt/up.sql ALTER TABLE payment_attempt DROP COLUMN IF EXISTS redirect; ALTER TABLE payment_attempt DROP COLUMN IF EXISTS payment_flow; DROP TYPE IF EXISTS "PaymentFlow"; -- File: migrations/2023-02-07-070512_change_merchant_connector_id_data_type/up.sql ALTER TABLE merchant_connector_account ALTER COLUMN merchant_connector_id TYPE VARCHAR(128) USING merchant_connector_id::varchar; ALTER TABLE merchant_connector_account ALTER COLUMN merchant_connector_id DROP DEFAULT; -- File: migrations/2023-02-09-093400_add_bank_redirect/up.sql -- Your SQL goes here ALTER TYPE "PaymentMethodType" ADD VALUE 'bank_redirect' after 'paypal'; -- File: migrations/2023-02-10-083146_make_payment_method_type_as_text/up.sql -- Your SQL goes here ALTER TABLE payment_methods ALTER COLUMN payment_method_type TYPE VARCHAR(64); ALTER TABLE payment_attempt ADD COLUMN payment_method_type VARCHAR(64); DROP TYPE IF EXISTS "PaymentMethodSubType"; -- File: migrations/2023-02-20-101809_update_merchant_connector_account/up.sql ALTER TABLE merchant_connector_account ADD COLUMN connector_label VARCHAR(255), ADD COLUMN business_country VARCHAR(2) DEFAULT 'US' NOT NULL, ADD COLUMN business_label VARCHAR(255) DEFAULT 'default' NOT NULL; -- To backfill, use `US` as default country and `default` as the business_label UPDATE merchant_connector_account AS m SET connector_label = concat( m.connector_name, '_', 'US', '_', 'default' ); ALTER TABLE merchant_connector_account ALTER COLUMN connector_label SET NOT NULL, ALTER COLUMN business_country DROP DEFAULT, ALTER COLUMN business_label DROP DEFAULT; DROP INDEX merchant_connector_account_merchant_id_connector_name_index; CREATE UNIQUE INDEX merchant_connector_account_merchant_id_connector_label_index ON merchant_connector_account (merchant_id, connector_label); -- File: migrations/2023-02-21-065628_update_merchant_account/up.sql ALTER TABLE merchant_account ADD COLUMN IF NOT EXISTS primary_business_details JSON NOT NULL DEFAULT '{"country": ["US"], "business": ["default"]}'; -- File: migrations/2023-02-21-094019_api_keys_remove_hash_key/up.sql ALTER TABLE api_keys DROP COLUMN hash_key; /* Once we've dropped the `hash_key` column, we cannot use the existing API keys from the `api_keys` table anymore, as the `hash_key` is a random string that we no longer have. */ TRUNCATE TABLE api_keys; ALTER TABLE api_keys ADD CONSTRAINT api_keys_hashed_api_key_key UNIQUE (hashed_api_key); -- File: migrations/2023-02-22-100331_rename_pm_type_enum/up.sql -- Your SQL goes here ALTER TABLE payment_attempt ALTER COLUMN payment_method TYPE VARCHAR; ALTER TABLE payment_methods ALTER COLUMN payment_method TYPE VARCHAR; ALTER TABLE payment_methods ALTER COLUMN payment_method_type TYPE VARCHAR; ALTER TABLE payment_attempt DROP COLUMN payment_issuer; ALTER TABLE payment_attempt ADD COLUMN payment_method_data JSONB; DROP TYPE "PaymentMethodType"; -- File: migrations/2023-02-28-072631_ang-currency/up.sql -- Your SQL goes here ALTER TYPE "Currency" ADD VALUE 'ANG' after 'AMD'; -- File: migrations/2023-02-28-112730_add_refund_webhook_types/up.sql -- Your SQL goes here ALTER TYPE "EventClass" ADD VALUE 'refunds'; ALTER TYPE "EventObjectType" ADD VALUE 'refund_details'; ALTER TYPE "EventType" ADD VALUE 'refund_succeeded'; ALTER TYPE "EventType" ADD VALUE 'refund_failed'; -- File: migrations/2023-03-04-114058_remove_api_key_column_merchant_account_table/up.sql ALTER TABLE merchant_account DROP COLUMN api_key; -- File: migrations/2023-03-07-141638_make_payment_attempt_connector_json/up.sql -- Alter column type to json -- as well as the connector. ALTER TABLE payment_attempt ALTER COLUMN connector TYPE JSONB USING jsonb_build_object( 'routed_through', connector, 'algorithm', NULL ); -- File: migrations/2023-03-14-123541_add_cards_info_table/up.sql -- Your SQL goes here CREATE TABLE cards_info ( card_iin VARCHAR(16) PRIMARY KEY, card_issuer TEXT, card_network TEXT, card_type TEXT, card_subtype TEXT, card_issuing_country TEXT, bank_code_id VARCHAR(32), bank_code VARCHAR(32), country_code VARCHAR(32), date_created TIMESTAMP NOT NULL, last_updated TIMESTAMP, last_updated_provider TEXT ); -- File: migrations/2023-03-15-082312_add_connector_txn_id_merchant_id_index_in_payment_attempt/up.sql -- Your SQL goes here CREATE INDEX payment_attempt_connector_transaction_id_merchant_id_index ON payment_attempt (connector_transaction_id, merchant_id); -- File: migrations/2023-03-15-185959_add_dispute_table/up.sql CREATE TYPE "DisputeStage" AS ENUM ('pre_dispute', 'dispute', 'pre_arbitration'); CREATE TYPE "DisputeStatus" AS ENUM ('dispute_opened', 'dispute_expired', 'dispute_accepted', 'dispute_cancelled', 'dispute_challenged', 'dispute_won', 'dispute_lost'); CREATE TABLE dispute ( id SERIAL PRIMARY KEY, dispute_id VARCHAR(64) NOT NULL, amount VARCHAR(255) NOT NULL, currency VARCHAR(255) NOT NULL, dispute_stage "DisputeStage" NOT NULL, dispute_status "DisputeStatus" NOT NULL, payment_id VARCHAR(255) NOT NULL, attempt_id VARCHAR(64) NOT NULL, merchant_id VARCHAR(255) NOT NULL, connector_status VARCHAR(255) NOT NULL, connector_dispute_id VARCHAR(255) NOT NULL, connector_reason VARCHAR(255), connector_reason_code VARCHAR(255), challenge_required_by VARCHAR(255), dispute_created_at VARCHAR(255), updated_at VARCHAR(255), created_at TIMESTAMP NOT NULL DEFAULT now()::TIMESTAMP, modified_at TIMESTAMP NOT NULL DEFAULT now()::TIMESTAMP ); CREATE UNIQUE INDEX merchant_id_dispute_id_index ON dispute (merchant_id, dispute_id); CREATE UNIQUE INDEX merchant_id_payment_id_connector_dispute_id_index ON dispute (merchant_id, payment_id, connector_dispute_id); CREATE INDEX dispute_status_index ON dispute (dispute_status); CREATE INDEX dispute_stage_index ON dispute (dispute_stage); ALTER TYPE "EventClass" ADD VALUE 'disputes'; ALTER TYPE "EventObjectType" ADD VALUE 'dispute_details'; ALTER TYPE "EventType" ADD VALUE 'dispute_opened'; ALTER TYPE "EventType" ADD VALUE 'dispute_expired'; ALTER TYPE "EventType" ADD VALUE 'dispute_accepted'; ALTER TYPE "EventType" ADD VALUE 'dispute_cancelled'; ALTER TYPE "EventType" ADD VALUE 'dispute_challenged'; ALTER TYPE "EventType" ADD VALUE 'dispute_won'; ALTER TYPE "EventType" ADD VALUE 'dispute_lost'; -- File: migrations/2023-03-16-105114_add_data_collection_status/up.sql ALTER TYPE "AttemptStatus" ADD VALUE IF NOT EXISTS 'device_data_collection_pending'; -- File: migrations/2023-03-23-095309_add_business_sub_label_to_mca/up.sql ALTER TABLE merchant_connector_account ADD COLUMN IF NOT EXISTS business_sub_label VARCHAR(64) DEFAULT 'default'; -- File: migrations/2023-03-23-123920_add_business_label_and_country_to_pi/up.sql ALTER TABLE payment_intent ADD COLUMN IF NOT EXISTS business_country VARCHAR(2) NOT NULL DEFAULT 'US', ADD COLUMN IF NOT EXISTS business_label VARCHAR(64) NOT NULL DEFAULT 'default'; -- File: migrations/2023-03-26-163105_add_unresolved_status/up.sql ALTER TYPE "AttemptStatus" ADD VALUE IF NOT EXISTS 'unresolved'; ALTER TYPE "IntentStatus" ADD VALUE IF NOT EXISTS 'requires_merchant_action' after 'requires_customer_action'; ALTER TYPE "EventType" ADD VALUE IF NOT EXISTS 'action_required'; ALTER TYPE "EventType" ADD VALUE IF NOT EXISTS 'payment_processing'; -- File: migrations/2023-03-27-091611_change_country_to_enum/up.sql CREATE TYPE "CountryCode" AS ENUM ( 'AF', 'AX', 'AL', 'DZ', 'AS', 'AD', 'AO', 'AI', 'AQ', 'AG', 'AR', 'AM', 'AW', 'AU', 'AT', 'AZ', 'BS', 'BH', 'BD', 'BB', 'BY', 'BE', 'BZ', 'BJ', 'BM', 'BT', 'BO', 'BQ', 'BA', 'BW', 'BV', 'BR', 'IO', 'BN', 'BG', 'BF', 'BI', 'KH', 'CM', 'CA', 'CV', 'KY', 'CF', 'TD', 'CL', 'CN', 'CX', 'CC', 'CO', 'KM', 'CG', 'CD', 'CK', 'CR', 'CI', 'HR', 'CU', 'CW', 'CY', 'CZ', 'DK', 'DJ', 'DM', 'DO', 'EC', 'EG', 'SV', 'GQ', 'ER', 'EE', 'ET', 'FK', 'FO', 'FJ', 'FI', 'FR', 'GF', 'PF', 'TF', 'GA', 'GM', 'GE', 'DE', 'GH', 'GI', 'GR', 'GL', 'GD', 'GP', 'GU', 'GT', 'GG', 'GN', 'GW', 'GY', 'HT', 'HM', 'VA', 'HN', 'HK', 'HU', 'IS', 'IN', 'ID', 'IR', 'IQ', 'IE', 'IM', 'IL', 'IT', 'JM', 'JP', 'JE', 'JO', 'KZ', 'KE', 'KI', 'KP', 'KR', 'KW', 'KG', 'LA', 'LV', 'LB', 'LS', 'LR', 'LY', 'LI', 'LT', 'LU', 'MO', 'MK', 'MG', 'MW', 'MY', 'MV', 'ML', 'MT', 'MH', 'MQ', 'MR', 'MU', 'YT', 'MX', 'FM', 'MD', 'MC', 'MN', 'ME', 'MS', 'MA', 'MZ', 'MM', 'NA', 'NR', 'NP', 'NL', 'NC', 'NZ', 'NI', 'NE', 'NG', 'NU', 'NF', 'MP', 'NO', 'OM', 'PK', 'PW', 'PS', 'PA', 'PG', 'PY', 'PE', 'PH', 'PN', 'PL', 'PT', 'PR', 'QA', 'RE', 'RO', 'RU', 'RW', 'BL', 'SH', 'KN', 'LC', 'MF', 'PM', 'VC', 'WS', 'SM', 'ST', 'SA', 'SN', 'RS', 'SC', 'SL', 'SG', 'SX', 'SK', 'SI', 'SB', 'SO', 'ZA', 'GS', 'SS', 'ES', 'LK', 'SD', 'SR', 'SJ', 'SZ', 'SE', 'CH', 'SY', 'TW', 'TJ', 'TZ', 'TH', 'TL', 'TG', 'TK', 'TO', 'TT', 'TN', 'TR', 'TM', 'TC', 'TV', 'UG', 'UA', 'AE', 'GB', 'US', 'UM', 'UY', 'UZ', 'VU', 'VE', 'VN', 'VG', 'VI', 'WF', 'EH', 'YE', 'ZM', 'ZW' ); ALTER TABLE address ALTER COLUMN country TYPE "CountryCode" USING country::"CountryCode"; -- File: migrations/2023-03-30-132338_add_start_end_date_for_mandates/up.sql ALTER TABLE mandate ADD IF NOT EXISTS start_date TIMESTAMP NULL, ADD IF NOT EXISTS end_date TIMESTAMP NULL, ADD COLUMN metadata JSONB DEFAULT NULL; -- File: migrations/2023-04-03-082335_update_mca_frm_configs/up.sql ALTER TABLE "merchant_connector_account" ADD COLUMN frm_configs jsonb; -- File: migrations/2023-04-04-061926_add_dispute_api_schema/up.sql -- Your SQL goes here CREATE TABLE file_metadata ( file_id VARCHAR(64) NOT NULL, merchant_id VARCHAR(255) NOT NULL, file_name VARCHAR(255), file_size INTEGER NOT NULL, file_type VARCHAR(255) NOT NULL, provider_file_id VARCHAR(255), file_upload_provider VARCHAR(255), available BOOLEAN NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT now()::TIMESTAMP, PRIMARY KEY (file_id, merchant_id) ); -- File: migrations/2023-04-05-051523_add_business_sub_label_to_payment_attempt/up.sql ALTER TABLE payment_attempt ADD COLUMN IF NOT EXISTS business_sub_label VARCHAR(64); -- File: migrations/2023-04-05-121040_alter_mca_change_country_to_enum/up.sql ALTER TABLE merchant_connector_account ALTER COLUMN business_country TYPE "CountryCode" USING business_country::"CountryCode"; -- File: migrations/2023-04-05-121047_alter_pi_change_country_to_enum/up.sql ALTER TABLE payment_intent ALTER COLUMN business_country DROP DEFAULT, ALTER COLUMN business_country TYPE "CountryCode" USING business_country::"CountryCode"; -- File: migrations/2023-04-06-063047_add_connector_col_in_dispute/up.sql -- Your SQL goes here ALTER TABLE dispute ADD COLUMN connector VARCHAR(255) NOT NULL; -- File: migrations/2023-04-06-092008_create_merchant_ek/up.sql CREATE TABLE merchant_key_store( merchant_id VARCHAR(255) NOT NULL PRIMARY KEY, key bytea NOT NULL, created_at TIMESTAMP NOT NULL ); -- File: migrations/2023-04-11-084958_pii-migration/up.sql -- Your SQL goes here ALTER TABLE merchant_connector_account ALTER COLUMN connector_account_details TYPE bytea USING convert_to(connector_account_details::text, 'UTF8'); ALTER TABLE merchant_account ALTER COLUMN merchant_name TYPE bytea USING convert_to(merchant_name, 'UTF8'), ALTER merchant_details TYPE bytea USING convert_to(merchant_details::text, 'UTF8'); ALTER TABLE address ALTER COLUMN line1 TYPE bytea USING convert_to(line1, 'UTF8'), ALTER COLUMN line2 TYPE bytea USING convert_to(line2, 'UTF8'), ALTER COLUMN line3 TYPE bytea USING convert_to(line3, 'UTF8'), ALTER COLUMN state TYPE bytea USING convert_to(state, 'UTF8'), ALTER COLUMN zip TYPE bytea USING convert_to(zip, 'UTF8'), ALTER COLUMN first_name TYPE bytea USING convert_to(first_name, 'UTF8'), ALTER COLUMN last_name TYPE bytea USING convert_to(last_name, 'UTF8'), ALTER COLUMN phone_number TYPE bytea USING convert_to(phone_number, 'UTF8'); ALTER TABLE customers ALTER COLUMN name TYPE bytea USING convert_to(name, 'UTF8'), ALTER COLUMN email TYPE bytea USING convert_to(email, 'UTF8'), ALTER COLUMN phone TYPE bytea USING convert_to(phone, 'UTF8'); -- File: migrations/2023-04-12-075449_separate_payment_attempt_algorithm_col/up.sql -- Your SQL goes here ALTER TABLE payment_attempt ADD COLUMN straight_through_algorithm JSONB; UPDATE payment_attempt SET straight_through_algorithm = connector->'algorithm' WHERE connector->>'algorithm' IS NOT NULL; ALTER TABLE payment_attempt ALTER COLUMN connector TYPE VARCHAR(64) USING connector->>'routed_through'; -- File: migrations/2023-04-13-094917_change_primary_business_type/up.sql -- This change will allow older merchant accounts to be used with new changes UPDATE merchant_account SET primary_business_details = '[{"country": "US", "business": "default"}]'; -- Since this field is optional, default is not required ALTER TABLE merchant_connector_account ALTER COLUMN business_sub_label DROP DEFAULT; -- File: migrations/2023-04-19-072152_merchant_account_add_intent_fulfilment_time/up.sql ALTER TABLE merchant_account ADD COLUMN IF NOT EXISTS intent_fulfillment_time BIGINT; -- File: migrations/2023-04-19-120503_update_customer_connector_customer/up.sql -- Your SQL goes here ALTER TABLE customers ADD COLUMN connector_customer JSONB; -- File: migrations/2023-04-19-120735_add_time_for_tables/up.sql -- Your SQL goes here ALTER TABLE merchant_account ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT now(), ADD COLUMN IF NOT EXISTS modified_at TIMESTAMP NOT NULL DEFAULT now(); ALTER TABLE merchant_connector_account ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT now(), ADD COLUMN IF NOT EXISTS modified_at TIMESTAMP NOT NULL DEFAULT now(); ALTER TABLE customers ADD COLUMN IF NOT EXISTS modified_at TIMESTAMP NOT NULL DEFAULT now(); -- File: migrations/2023-04-20-073704_allow_multiple_mandate_ids/up.sql ALTER TABLE mandate ADD COLUMN connector_mandate_ids jsonb; UPDATE mandate SET connector_mandate_ids = jsonb_build_object( 'mandate_id', connector_mandate_id, 'payment_method_id', NULL ); -- File: migrations/2023-04-20-162755_add_preprocessing_step_id_payment_attempt/up.sql -- Your SQL goes here ALTER TABLE payment_attempt ADD COLUMN preprocessing_step_id VARCHAR DEFAULT NULL; CREATE INDEX preprocessing_step_id_index ON payment_attempt (preprocessing_step_id); -- File: migrations/2023-04-21-100150_create_index_for_api_keys/up.sql CREATE UNIQUE INDEX api_keys_merchant_id_key_id_index ON api_keys (merchant_id, key_id); -- File: migrations/2023-04-25-061159_rename_country_code_to_country_alpha2/up.sql -- Your SQL goes here ALTER TYPE "CountryCode" RENAME TO "CountryAlpha2"; -- File: migrations/2023-04-25-091017_merchant_account_add_frm_routing_algorithm.sql/up.sql ALTER TABLE merchant_account ADD COLUMN frm_routing_algorithm JSONB NULL; -- File: migrations/2023-04-25-141011_add_connector_label_col_in_file_metadata/up.sql -- Your SQL goes here ALTER TABLE file_metadata ADD COLUMN connector_label VARCHAR(255); -- File: migrations/2023-04-26-062424_alter_dispute_table/up.sql ALTER TABLE dispute ALTER COLUMN challenge_required_by TYPE TIMESTAMP USING dispute_created_at::TIMESTAMP, ALTER COLUMN dispute_created_at TYPE TIMESTAMP USING dispute_created_at::TIMESTAMP, ALTER COLUMN updated_at TYPE TIMESTAMP USING dispute_created_at::TIMESTAMP; -- File: migrations/2023-04-26-090005_remove_default_created_at_modified_at/up.sql -- Merchant Account ALTER TABLE merchant_account ALTER COLUMN modified_at DROP DEFAULT; ALTER TABLE merchant_account ALTER COLUMN created_at DROP DEFAULT; -- Merchant Connector Account ALTER TABLE merchant_connector_account ALTER COLUMN modified_at DROP DEFAULT; ALTER TABLE merchant_connector_account ALTER COLUMN created_at DROP DEFAULT; -- Customers ALTER TABLE customers ALTER COLUMN modified_at DROP DEFAULT; ALTER TABLE customers ALTER COLUMN created_at DROP DEFAULT; -- Address ALTER TABLE address ALTER COLUMN modified_at DROP DEFAULT; ALTER TABLE address ALTER COLUMN created_at DROP DEFAULT; -- Refunds ALTER TABLE refund ALTER COLUMN modified_at DROP DEFAULT; ALTER TABLE refund ALTER COLUMN created_at DROP DEFAULT; -- Connector Response ALTER TABLE connector_response ALTER COLUMN modified_at DROP DEFAULT; ALTER TABLE connector_response ALTER COLUMN created_at DROP DEFAULT; -- Payment methods ALTER TABLE payment_methods ALTER COLUMN created_at DROP DEFAULT; -- Payment Intent ALTER TABLE payment_intent ALTER COLUMN modified_at DROP DEFAULT; ALTER TABLE payment_intent ALTER COLUMN created_at DROP DEFAULT; --- Payment Attempt ALTER TABLE payment_attempt ALTER COLUMN modified_at DROP DEFAULT; ALTER TABLE payment_attempt ALTER COLUMN created_at DROP DEFAULT; -- File: migrations/2023-04-27-120010_add_payment_failed_event_type/up.sql ALTER TYPE "EventType" ADD VALUE IF NOT EXISTS 'payment_failed'; -- File: migrations/2023-05-02-102332_payout_create/up.sql CREATE type "PayoutStatus" AS ENUM ( 'success', 'failed', 'cancelled', 'pending', 'ineligible', 'requires_creation', 'requires_payout_method_data', 'requires_fulfillment' ); CREATE type "PayoutType" AS ENUM ('card', 'bank'); CREATE TABLE PAYOUT_ATTEMPT ( payout_attempt_id VARCHAR (64) NOT NULL PRIMARY KEY, payout_id VARCHAR (64) NOT NULL, customer_id VARCHAR (64) NOT NULL, merchant_id VARCHAR (64) NOT NULL, address_id VARCHAR (64) NOT NULL, connector VARCHAR (64) NOT NULL, connector_payout_id VARCHAR (128) NOT NULL, payout_token VARCHAR (64), status "PayoutStatus" NOT NULL, is_eligible BOOLEAN, error_message TEXT, error_code VARCHAR (64), business_country "CountryAlpha2", business_label VARCHAR(64), created_at timestamp NOT NULL DEFAULT NOW():: timestamp, last_modified_at timestamp NOT NULL DEFAULT NOW():: timestamp ); CREATE TABLE PAYOUTS ( payout_id VARCHAR (64) NOT NULL PRIMARY KEY, merchant_id VARCHAR (64) NOT NULL, customer_id VARCHAR (64) NOT NULL, address_id VARCHAR (64) NOT NULL, payout_type "PayoutType" NOT NULL, payout_method_id VARCHAR (64), amount BIGINT NOT NULL, destination_currency "Currency" NOT NULL, source_currency "Currency" NOT NULL, description VARCHAR (255), recurring BOOLEAN NOT NULL, auto_fulfill BOOLEAN NOT NULL, return_url VARCHAR (255), entity_type VARCHAR (64) NOT NULL, metadata JSONB DEFAULT '{}':: JSONB, created_at timestamp NOT NULL DEFAULT NOW():: timestamp, last_modified_at timestamp NOT NULL DEFAULT NOW():: timestamp ); CREATE UNIQUE INDEX payout_attempt_index ON PAYOUT_ATTEMPT ( merchant_id, payout_attempt_id, payout_id ); CREATE UNIQUE INDEX payouts_index ON PAYOUTS (merchant_id, payout_id); -- Alterations ALTER TABLE merchant_account ADD COLUMN payout_routing_algorithm JSONB; ALTER TABLE locker_mock_up ADD COLUMN enc_card_data TEXT; ALTER TYPE "ConnectorType" ADD VALUE 'payout_processor'; -- File: migrations/2023-05-03-121025_nest_straight_through_col_in_payment_attempt/up.sql -- Your SQL goes here UPDATE payment_attempt SET straight_through_algorithm = jsonb_build_object('algorithm', straight_through_algorithm); -- File: migrations/2023-05-05-112013_add_evidence_col_in_dispute/up.sql -- Your SQL goes here ALTER TABLE dispute ADD COLUMN evidence JSONB NOT NULL DEFAULT '{}'::JSONB; -- File: migrations/2023-05-08-141907_rename_dispute_cols/up.sql -- Your SQL goes here ALTER TABLE dispute RENAME COLUMN dispute_created_at TO connector_created_at; ALTER TABLE dispute RENAME COLUMN updated_at TO connector_updated_at; -- File: migrations/2023-05-16-145008_mandate_data_in_pa/up.sql -- Your SQL goes here ALTER TABLE payment_attempt ADD COLUMN mandate_details JSONB; -- File: migrations/2023-05-29-094747_order-details-as-a-separate-column.sql/up.sql ALTER TABLE payment_intent ADD COLUMN order_details jsonb[]; -- File: migrations/2023-05-31-152153_add_connector_webhook_details_to_mca/up.sql -- Your SQL goes here ALTER TABLE merchant_connector_account ADD COLUMN IF NOT EXISTS connector_webhook_details JSONB DEFAULT NULL; -- File: migrations/2023-06-14-105035_add_reason_in_payment_attempt/up.sql ALTER TABLE payment_attempt ADD COLUMN error_reason TEXT; -- File: migrations/2023-06-16-073615_add_ron_currency_to_db/up.sql -- Your SQL goes here ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'RON' AFTER 'QAR'; ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'TRY' AFTER 'TTD'; -- File: migrations/2023-06-18-042123_add_udf_column_in_payments/up.sql -- Your SQL goes here ALTER TABLE payment_intent ADD COLUMN udf JSONB; -- File: migrations/2023-06-19-071300_merchant_key_store_shrink_merchant_id/up.sql ALTER TABLE merchant_key_store ALTER COLUMN merchant_id TYPE VARCHAR(64); -- File: migrations/2023-06-22-161131_change-type-of-frm-configs.sql/up.sql UPDATE merchant_connector_account set frm_configs = null ; ALTER TABLE merchant_connector_account ALTER COLUMN frm_configs TYPE jsonb[] USING ARRAY[frm_configs]::jsonb[]; UPDATE merchant_connector_account set frm_configs = null ; -- File: migrations/2023-06-26-124254_add_vnd_to_currency_enum/up.sql -- Your SQL goes here ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'VND' AFTER 'UZS'; -- File: migrations/2023-06-29-094858_payment-intent-remove-udf-field/up.sql -- Your SQL goes here ALTER TABLE payment_intent DROP COLUMN udf; -- File: migrations/2023-07-01-184850_payment-intent-add-metadata-fields/up.sql -- Your SQL goes here ALTER TABLE payment_intent ADD COLUMN allowed_payment_method_types JSON, ADD COLUMN connector_metadata JSON, ADD COLUMN feature_metadata JSON; -- File: migrations/2023-07-03-093552_add_attempt_count_in_payment_intent/up.sql ALTER TABLE payment_intent ADD COLUMN attempt_count SMALLINT NOT NULL DEFAULT 1; UPDATE payment_intent SET attempt_count = payment_id_count.count FROM (SELECT payment_id, count(payment_id) FROM payment_attempt GROUP BY payment_id) as payment_id_count WHERE payment_intent.payment_id = payment_id_count.payment_id; -- File: migrations/2023-07-04-131721_add_org_id_and_org_name/up.sql -- Your SQL goes here ALTER TABLE merchant_account ADD COLUMN IF NOT EXISTS organization_id VARCHAR(32); -- File: migrations/2023-07-07-091223_create_captures_table/up.sql CREATE TYPE "CaptureStatus" AS ENUM ( 'started', 'charged', 'pending', 'failed' ); ALTER TYPE "IntentStatus" ADD VALUE If NOT EXISTS 'partially_captured' AFTER 'requires_capture'; CREATE TABLE captures( capture_id VARCHAR(64) NOT NULL PRIMARY KEY, payment_id VARCHAR(64) NOT NULL, merchant_id VARCHAR(64) NOT NULL, status "CaptureStatus" NOT NULL, amount BIGINT NOT NULL, currency "Currency", connector VARCHAR(255), error_message VARCHAR(255), error_code VARCHAR(255), error_reason VARCHAR(255), tax_amount BIGINT, created_at TIMESTAMP NOT NULL, modified_at TIMESTAMP NOT NULL, authorized_attempt_id VARCHAR(64) NOT NULL, connector_transaction_id VARCHAR(128), capture_sequence SMALLINT NOT NULL ); CREATE INDEX captures_merchant_id_payment_id_authorized_attempt_id_index ON captures ( merchant_id, payment_id, authorized_attempt_id ); CREATE INDEX captures_connector_transaction_id_index ON captures ( connector_transaction_id ); ALTER TABLE payment_attempt ADD COLUMN multiple_capture_count SMALLINT; --number of captures available for this payment attempt in captures table -- File: migrations/2023-07-08-134807_add_connector_response_reference_id_in_payment_intent/up.sql -- Your SQL goes here ALTER TABLE payment_attempt ADD COLUMN IF NOT EXISTS connector_response_reference_id VARCHAR(128); -- File: migrations/2023-07-11-140347_add_is_recon_enabled_field_in_merchant_account/up.sql -- Your SQL goes here ALTER TABLE merchant_account ADD COLUMN "is_recon_enabled" BOOLEAN NOT NULL DEFAULT FALSE; -- File: migrations/2023-07-17-111427_add-fraud-check-table.sql/up.sql -- Your SQL goes here-- Your SQL goes here CREATE TYPE "FraudCheckType" AS ENUM ( 'pre_frm', 'post_frm' ); CREATE TYPE "FraudCheckStatus" AS ENUM ( 'fraud', 'manual_review', 'pending', 'legit', 'transaction_failure' ); CREATE TABLE fraud_check ( frm_id VARCHAR(64) NOT NULL UNIQUE, payment_id VARCHAR(64) NOT NULL, merchant_id VARCHAR(64) NOT NULL, attempt_id VARCHAR(64) NOT NULL UNIQUE, created_at TIMESTAMP NOT NULL DEFAULT now(), frm_name VARCHAR(255) NOT NULL, frm_transaction_id VARCHAR(255) UNIQUE, frm_transaction_type "FraudCheckType" NOT NULL, frm_status "FraudCheckStatus" NOT NULL, frm_score INTEGER, frm_reason JSONB, frm_error VARCHAR(255), payment_details JSONB, metadata JSONB, modified_at TIMESTAMP NOT NULL DEFAULT now(), PRIMARY KEY (frm_id, attempt_id, payment_id, merchant_id) ); CREATE UNIQUE INDEX frm_id_index ON fraud_check (frm_id, attempt_id, payment_id, merchant_id); -- File: migrations/2023-07-19-081050_add_zero_decimal_currencies/up.sql -- Your SQL goes here ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'BIF' AFTER 'BHD'; ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'CLP' AFTER 'CHF'; ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'DJF' AFTER 'CZK'; ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'GNF' AFTER 'GMD'; ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'KMF' AFTER 'KHR'; ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'MGA' AFTER 'MDL'; ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'PYG' AFTER 'PLN'; ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'RWF' AFTER 'RUB'; ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'UGX' AFTER 'TZS'; ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'VUV' AFTER 'VND'; ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'XAF' AFTER 'VUV'; ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'XOF' AFTER 'XAF'; ALTER TYPE "Currency" ADD VALUE IF NOT EXISTS 'XPF' AFTER 'XOF'; -- File: migrations/2023-07-28-111829_update_columns_to_fix_db_diff/up.sql ALTER TABLE dispute ALTER COLUMN payment_id TYPE VARCHAR(64); ALTER TABLE payment_methods ALTER COLUMN payment_method_type TYPE VARCHAR(64); ALTER TABLE merchant_account ALTER COLUMN primary_business_details DROP DEFAULT; -- File: migrations/2023-08-01-165717_make_event_id_unique_for_events_table/up.sql -- Your SQL goes here ALTER TABLE events ADD CONSTRAINT event_id_unique UNIQUE (event_id); -- File: migrations/2023-08-08-144148_add_business_profile_table/up.sql -- Your SQL goes here CREATE TABLE IF NOT EXISTS business_profile ( profile_id VARCHAR(64) PRIMARY KEY, merchant_id VARCHAR(64) NOT NULL, profile_name VARCHAR(64) NOT NULL, created_at TIMESTAMP NOT NULL, modified_at TIMESTAMP NOT NULL, return_url TEXT, enable_payment_response_hash BOOLEAN NOT NULL DEFAULT TRUE, payment_response_hash_key VARCHAR(255) DEFAULT NULL, redirect_to_merchant_with_http_post BOOLEAN NOT NULL DEFAULT FALSE, webhook_details JSON, metadata JSON, routing_algorithm JSON, intent_fulfillment_time BIGINT, frm_routing_algorithm JSONB, payout_routing_algorithm JSONB, is_recon_enabled BOOLEAN NOT NULL DEFAULT FALSE ); -- File: migrations/2023-08-11-073905_add_frm_config_in_mca/up.sql ALTER TABLE "merchant_connector_account" ADD COLUMN frm_config jsonb[]; -- Do not run below migration in PROD as this only makes sandbox compatible to PROD version ALTER TABLE merchant_connector_account ALTER COLUMN frm_configs TYPE jsonb USING frm_configs[1]::jsonb; -- File: migrations/2023-08-16-080721_make_connector_field_mandatory_capture_table/up.sql -- Your SQL goes here ALTER TABLE captures ALTER COLUMN connector SET NOT NULL; ALTER TABLE captures RENAME COLUMN connector_transaction_id TO connector_capture_id; ALTER TABLE captures add COLUMN IF NOT EXISTS connector_response_reference_id VARCHAR(128); -- File: migrations/2023-08-16-103806_add_last_executed_frm_step/up.sql alter table fraud_check add column last_step VARCHAR(64) NOT NULL DEFAULT 'processing'; -- File: migrations/2023-08-16-112847_add_profile_id_in_affected_tables/up.sql -- Your SQL goes here ALTER TABLE payment_intent ADD COLUMN IF NOT EXISTS profile_id VARCHAR(64); ALTER TABLE merchant_connector_account ADD COLUMN IF NOT EXISTS profile_id VARCHAR(64); ALTER TABLE merchant_account ADD COLUMN IF NOT EXISTS default_profile VARCHAR(64); -- Profile id is needed in refunds for listing refunds by business profile ALTER TABLE refund ADD COLUMN IF NOT EXISTS profile_id VARCHAR(64); -- For listing disputes by business profile ALTER TABLE dispute ADD COLUMN IF NOT EXISTS profile_id VARCHAR(64); -- For a similar use case as to payments ALTER TABLE payout_attempt ADD COLUMN IF NOT EXISTS profile_id VARCHAR(64); -- File: migrations/2023-08-23-090712_payment_attempt_perf_idx/up.sql -- Your SQL goes here CREATE INDEX payment_attempt_attempt_id_merchant_id_index ON payment_attempt (attempt_id, merchant_id); -- File: migrations/2023-08-24-095037_add_profile_id_in_file_metadata/up.sql -- Your SQL goes here ALTER TABLE file_metadata ADD COLUMN IF NOT EXISTS profile_id VARCHAR(64); -- File: migrations/2023-08-25-094551_add_recon_status_in_merchant_account/up.sql -- Your SQL goes here CREATE TYPE "ReconStatus" AS ENUM ('requested','active', 'disabled','not_requested'); ALTER TABLE merchant_account ADD recon_status "ReconStatus" NOT NULL DEFAULT "ReconStatus"('not_requested'); -- File: migrations/2023-08-28-131238_make_business_details_optional/up.sql -- Your SQL goes here ALTER TABLE payment_intent ALTER COLUMN business_country DROP NOT NULL; ALTER TABLE payment_intent ALTER COLUMN business_label DROP NOT NULL; ALTER TABLE merchant_connector_account ALTER COLUMN business_country DROP NOT NULL; ALTER TABLE merchant_connector_account ALTER COLUMN business_label DROP NOT NULL; ALTER TABLE merchant_connector_account ALTER COLUMN connector_label DROP NOT NULL; DROP INDEX IF EXISTS merchant_connector_account_merchant_id_connector_label_index; CREATE UNIQUE INDEX IF NOT EXISTS merchant_connector_account_profile_id_connector_id_index ON merchant_connector_account(profile_id, connector_name); CREATE UNIQUE INDEX IF NOT EXISTS business_profile_merchant_id_profile_name_index ON business_profile(merchant_id, profile_name); -- File: migrations/2023-08-31-093852_add_merchant_decision/up.sql alter table payment_intent add column merchant_decision VARCHAR(64); -- File: migrations/2023-09-06-101704_payment_method_data_in_payment_methods/up.sql -- Your SQL goes here ALTER TABLE payment_methods ADD COLUMN IF NOT EXISTS payment_method_data BYTEA DEFAULT NULL; -- File: migrations/2023-09-07-113914_add_amount_capturable_field_payment_attempt/up.sql -- Your SQL goes here ALTER TABLE payment_attempt ADD COLUMN IF NOT EXISTS amount_capturable BIGINT NOT NULL DEFAULT 0; -- File: migrations/2023-09-08-112817_applepay_verified_domains_in_business_profile/up.sql ALTER TABLE business_profile ADD COLUMN IF NOT EXISTS applepay_verified_domains text[]; -- File: migrations/2023-09-08-134514_add_payment_confirm_source_in_payment_intent/up.sql -- Your SQL goes here CREATE TYPE "PaymentSource" AS ENUM ( 'merchant_server', 'postman', 'dashboard', 'sdk' ); ALTER TABLE payment_intent ADD COLUMN IF NOT EXISTS payment_confirm_source "PaymentSource"; -- File: migrations/2023-09-13-075226_applepay_verified_domains_in_mca/up.sql ALTER TABLE merchant_connector_account ADD COLUMN IF NOT EXISTS applepay_verified_domains text[]; -- File: migrations/2023-09-14-032447_add_payment_id_in_address/up.sql -- Your SQL goes here ALTER TABLE address ADD COLUMN payment_id VARCHAR(64); ALTER TABLE customers ADD COLUMN address_id VARCHAR(64); -- File: migrations/2023-09-17-152010_make_id_not_null_address/up.sql -- Your SQL goes here ALTER TABLE address ALTER COLUMN id DROP NOT NULL; -- File: migrations/2023-09-18-104900_add_pm_auth_config_mca/up.sql -- Your SQL goes here ALTER TABLE merchant_connector_account ADD COLUMN IF NOT EXISTS pm_auth_config JSONB DEFAULT NULL; ALTER TYPE "ConnectorType" ADD VALUE 'payment_method_auth'; -- File: migrations/2023-09-25-125007_add_surcharge_metadata_payment_attempt/up.sql -- Your SQL goes here ALTER TABLE payment_attempt ADD COLUMN IF NOT EXISTS surcharge_metadata JSONB DEFAULT NULL; -- File: migrations/2023-10-05-085859_make_org_id_mandatory_in_ma/up.sql -- Your SQL goes here UPDATE merchant_account SET organization_id = 'org_abcdefghijklmn' WHERE organization_id IS NULL; ALTER TABLE merchant_account ALTER COLUMN organization_id SET NOT NULL; -- File: migrations/2023-10-05-114138_add_payment_id_in_mandate/up.sql -- Your SQL goes here ALTER TABLE mandate ADD COLUMN original_payment_id VARCHAR(64); -- File: migrations/2023-10-05-130917_add_mandate_webhook_types/up.sql -- Your SQL goes here ALTER TYPE "EventClass" ADD VALUE 'mandates'; ALTER TYPE "EventObjectType" ADD VALUE 'mandate_details'; ALTER TYPE "EventType" ADD VALUE 'mandate_active'; ALTER TYPE "EventType" ADD VALUE 'mandate_revoked';