🏦 bank-postgresql-vault

💸 A Curated Vault of PostgreSQL Data Modeling Patterns for Banking and Finance 🚀

---

✨ Welcome to the Vault!

Explore 65+ real-world, production-ready SQL patterns for PostgreSQL, tailored to modern banking, fintech, and data engineering. Each pattern is a self-contained example, ready for learning or drop-in use.
From basic schema design to advanced indexing, temporal tracking, partitioning, security, and more...

---

🚀 Getting Started

1. Clone the repo:

   git clone https://github.com/AishwaryaaP/bank-postgresql-vault.git
   cd bank-postgresql-vault

2. Browse the patterns folder and pick your favorite pattern.

3. Run in psql or your favorite SQL IDE!

---

🎯 What’s Inside?

• Schema Designs: Surrogate keys, UUIDs, natural/composite PKs
• Constraints: Unique, foreign, check, deferrable, business rules
• Indexing: Partial, expression, multi-column, concurrent, GIN/trigram
• Partitioning: Range, list, maintenance templates
• Data Modeling: Soft deletes, audit trails, history, arrays, hstore, JSONB
• Performance: Window functions, CTEs, lateral joins, anti-joins, upserts
• Security: RLS, least privilege, security definer functions
• Automation: pg_cron, ETL, CDC, migrations, backup/restore
• Procedural SQL: PL/pgSQL functions, triggers, stored procedures
• CQRS/Event Sourcing: Materialized views, append-only patterns

---

📂 Patterns Index

Below are direct links to every pattern for quick access, with descriptions:

#	Pattern File	Description	🌐 Quick Link
1	01_create_banking_schema.sql	Creates the base banking schema	🔗 View
2	02_surrogate_key_identity.sql	Defines tables with surrogate primary keys using IDENTITY columns.	🔗 View
3	03_uuid_keys_pgcrypto.sql	Uses UUIDs as primary keys, generated with the pgcrypto extension.	🔗 View
4	04_composite_pk_ledger_lines.sql	Introduces composite primary keys for ledger line consistency.	🔗 View
5	05_natural_key_bic_swift.sql	Uses natural keys (BIC/SWIFT codes) for bank identification.	🔗 View
6	06_unique_constraints_customers.sql	Applies unique constraints to ensure no duplicate customer records.	🔗 View
7	07_foreign_keys_on_delete.sql	Demonstrates foreign key constraints with cascading deletes.	🔗 View
8	08_soft_delete_flag.sql	Shows soft deletion using a boolean flag instead of hard deletes.	🔗 View
9	09_audit_trail_customers.sql	Adds audit trail functionality to track customer changes.	🔗 View
10	10_temporal_history_accounts.sql	Implements temporal tables for account history tracking.	🔗 View
11	11_optimistic_locking_accounts.sql	Applies optimistic locking with version numbers for concurrent updates.	🔗 View
12	12_pessimistic_locking_transfer.sql	Uses pessimistic locking to safely handle money transfers.	🔗 View
13	13_upsert_customer_contact.sql	Provides UPSERT logic for customer contact info.	🔗 View
14	14_merge_daily_rates.sql	Demonstrates merging daily FX rates using MERGE.	🔗 View
15	15_idempotent_insert_transactions.sql	Ensures idempotent inserts of transaction data.	🔗 View
16	16_bulk_insert_copy_payments.sql	Loads bulk payment data using COPY.	🔗 View
17	17_batch_upsert_cards.sql	Performs batch upserts for card details.	🔗 View
18	18_partitioning_range_tx_date.sql	Implements range partitioning by transaction date.	🔗 View
19	19_partitioning_list_region.sql	Partitions data by region using list partitioning.	🔗 View
20	20_partition_maintenance_template.sql	Provides a template for partition maintenance tasks.	🔗 View
21	21_materialized_view_balances.sql	Creates materialized views for customer balances.	🔗 View
22	22_security_definer_read_model.sql	Defines security definer functions for read models.	🔗 View
23	23_jsonb_kyc_profile.sql	Stores KYC customer profile data in JSONB format.	🔗 View
24	24_jsonb_indexing_gin.sql	Creates GIN indexes on JSONB fields for faster queries.	🔗 View
25	25_full_text_search_support.sql	Enables full-text search support on text fields.	🔗 View
26	26_trigram_search_names.sql	Uses trigram search for fuzzy matching on customer names.	🔗 View
27	27_array_account_flags.sql	Stores multiple account flags using PostgreSQL arrays.	🔗 View
28	28_hstore_misc_attrs.sql	Uses hstore for flexible key-value storage.	🔗 View
29	29_window_functions_ranking.sql	Applies window functions for ranking transactions.	🔗 View
30	30_recursive_cte_org_hierarchy.sql	Models organizational hierarchy using recursive CTEs.	🔗 View
31	31_lateral_join_top_tx_per_acct.sql	Finds top transactions per account using lateral joins.	🔗 View
32	32_anti_join_inactive_customers.sql	Identifies inactive customers using anti-joins.	🔗 View
33	33_exists_vs_in_cards.sql	Compares EXISTS vs IN queries for card lookups.	🔗 View
34	34_partial_index_active_cards.sql	Creates partial indexes for active cards only.	🔗 View
35	35_expression_index_lower_email.sql	Indexes on lowercase emails for case-insensitive lookups.	🔗 View
36	36_multicolumn_index_hot_path.sql	Builds multicolumn indexes for performance hot paths.	🔗 View
37	37_concurrent_index_large_tx.sql	Demonstrates concurrent index creation on large transaction tables.	🔗 View
38	38_postgres_fdw_corebank.sql	Uses Postgres FDW to connect to external core banking DBs.	🔗 View
39	39_event_sourcing_ledger.sql	Implements event sourcing pattern for transaction ledger.	🔗 View
40	40_cqrs_read_model_mv.sql	Creates CQRS read models using materialized views.	🔗 View
41	41_triggers_examples_audit.sql	Shows triggers for auditing changes in key tables.	🔗 View
42	42_plpgsql_fn_interest_calc.sql	PL/pgSQL function to calculate account interest.	🔗 View
43	43_stored_procedure_transfer.sql	Stored procedure for executing money transfers.	🔗 View
44	44_roles_privileges_least.sql	Implements least privilege role-based security.	🔗 View
45	45_row_level_security_per_tenant.sql	Sets row-level security policies per tenant.	🔗 View
46	46_monthly_partition_do_block.sql	Automates monthly partitions with DO blocks.	🔗 View
47	47_backup_restore_notes.sql	Provides SQL notes for backup and restore best practices.	🔗 View
48	48_maintenance_vacuum_analyze.sql	Demonstrates table maintenance with VACUUM and ANALYZE.	🔗 View
49	49_sequences_and_setval.sql	Manages sequences and resets them with setval.	🔗 View
50	50_gapless_ticket_numbers.sql	Ensures gapless ticket numbers using sequences.	🔗 View
51	51_advisory_locks_dedup.sql	Uses advisory locks to prevent duplicate inserts.	🔗 View
52	52_explain_analyze_examples.sql	Shows query plans with EXPLAIN and ANALYZE.	🔗 View
53	53_query_rewrite_tips.sql	Provides query rewrite strategies for performance.	🔗 View
54	54_refresh_mv_concurrently.sql	Refreshes materialized views concurrently.	🔗 View
55	55_temp_tables_etl.sql	Uses temporary tables in ETL workflows.	🔗 View
56	56_pg_cron_example.sql	Schedules tasks with pg_cron extension.	🔗 View
57	57_copy_to_from_csv.sql	Imports and exports data using COPY with CSV.	🔗 View
58	58_cdc_wal2json_overview.sql	Demonstrates Change Data Capture with wal2json.	🔗 View
59	59_generic_search_function.sql	Defines a generic SQL search function.	🔗 View
60	60_schema_migrations_table.sql	Creates schema migrations table for versioning.	🔗 View
61	61_deferrable_constraints.sql	Shows deferrable constraints for transaction control.	🔗 View
62	62_citext_case_insensitive.sql	Uses citext type for case-insensitive text fields.	🔗 View
63	63_isolation_levels_examples.sql	Illustrates different transaction isolation levels.	🔗 View
64	64_generated_columns_demo.sql	Demonstrates generated columns in PostgreSQL.	🔗 View
65	65_check_constraints_business_rules.sql	Implements check constraints for business rules.	🔗 View

---

🌈 Why this Vault?

• Production-inspired: All patterns are inspired by real-world systems.
• Ready to use: Copy, paste, and adapt.
• Learn by doing: Each file is a mini-tutorial.
• Mix & match: Compose your own data platform.
• Open source, open hearts! ❤️

---

⭐️ If you find this helpful, leave a star and share your thoughts!

---

👤 Author

Aishwarya Yuvaraj Phirke
📂 GitHub: @AishwaryaaP
👋 Connect on LinkedIn

Made with ❤️, ☕ and PostgreSQL — Happy Coding!