Emissive covalent organic frameworks (COFs) have recently emerged as next-generation porous materials with attractive properties such as tunable topology, porosity, and inherent photoluminescence. Among the different types of COFs, substoichiometric frameworks (so-called Type III COFs) are especially attractive due to the possibility of not only generating unusual topology and complex pore architectures but also facilitating the introduction of well-defined functional groups at precise locations for desired functions. Herein, the first example of a highly emissive (PLQY 6.8%) substoichiometric 2D-COF (COF-SMU-1) featuring free uncondensed aldehyde groups is reported. In particular, COF-SMU-1 features a dual-pore architecture with an overall bex net topology, tunable emission in various organic solvents, and distinct colorimetric changes in the presence of water. To gain further insights into its photoluminescence properties, the charge transfer, excimer emission, and excited state exciton dynamics of COF-SMU-1 are investigated using femtosecond transient absorption spectroscopy in different organic solvents. Additionally, highly enhanced atmospheric water-harvesting properties of COF-SMU-1 are revealed using FT-IR and water sorption studies.The findings will not only lead to in-depth understanding of structure-property relationships in emissive COFs but also open new opportunities for designing COFs for potential applications in solid-state lighting and water harvesting.